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
Speed of update
Moderators: gto, cipto_kh, EgonHugeist
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
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.
Code: Select all
update TableName set ColumnName1=:ParamName1,ColumnName2=:ParamName2 where ACIP_ACPI_ID=:ACIP_ACPI_ID and ACIP_ACIPT_ID=:ACIP_ACIPT_IDMarkus Dütting
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
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