Zeoslib Portal

Hi.

Im trying to optimize my code somewhat. Usually when i only do an update from a SqlServer-query to an MySql-table it goes very fast. Altough this seems to differ from table to table.

if not (Locate('ACIP_ACPI_ID;ACIP_ACIPT_ID',VarArrayOf([ADOQGeneral.FieldByName('Va_Ref_Id').AsString,'1']),[])) then

Those are both integer values. When found it updates 2 fields. 3000 records takes about 4-5 seconds.

if not (Locate('ACPI_ID',ADOQGeneral.FieldByName('Va_Ref_Id').AsString,[])) then

This is string. When found it updates 6 fields. Altough with this, 1500 records takes about 40 seconds.

Anyone know what the reason could be, and what i can do to increase the preformance of the latter?

I use TZTable for the operations.

All the best
Jostein

Hello DialAfc,

the way ZeosLib implements Locate is fetching record after record from the db until a match is found. Integer comparison is a lot faster than AnsiStrLComp which is used to compare the strings. So the behaviour you described is expected.

A definitely faster way should be using an update statement with parameters in a query component and issuing ExecuteSQL.
Best Regards,
Markus Dütting

Hi Markus,

I thought, when doing a (Z)Table.Open, all records are fetched at once. That's why I always prefer Queries. Am I wrong here?

Best regards,
Rüdiger

Hi.

Thnx for the reply. That was actually what i tought might be the reason. But, when i looked closer at my code, the Locate on the MySql-table on (ACPI_ID) isnt a string, it is actually an integer, wich makes it pretty strange...

Hi pol,

doesn't look like that to me. But i must admit that i'm quite new to Zeos. I thought it will be cached if a row is read and all rows are read e.g. if you call RecordCount, use Filter ( MasterFields) and as i've just seen Locate ( it calls RecordCount) regardless of Table or Query.

To DialAfc,
are you sure that you search for values that are relative near the same position in the sort order of your dataset and have the same layout or better TZTable object in both circumstances? It searches in the order it gets the rows from the db so this would make a significant difference as well as the average size of a record. Using a filter also has an impact. You can only compare the results if you try to search for the same record in the same table with different key values. It would be strange if a one column compare with integers would be slower than a two column one. Btw it uses the type of the dataset field to compare the values, regardless what data type you use as compare values in the locate call.

Best Regards,
Markus Dütting