Increase Your Multi-Record Update Performance Significantly
Friday, June 14, 2002Increase Your Multi-Record Update Performance Significantly (Up to 100 times faster) When updating a large set of records with a WHILE NOT EOF loop, the update time can be significantly longer and more complex than with an SQL UPDATE statement. With the following Delphi code as an example: ----------begin snippet---------- With AdsTable1 do begin While not EOF do begin Edit; if (FieldByName('Amt. Due').Value > 0) then FieldByName('Amt. Paid').Value := (Trunc(StrToFloat(Edit1.Text)* 100) * 0.01); Next; end; end; ----------end snippet---------- The records are having to be read one at a time from the server to the client to perform the logic to determine if the update is necessary, and also to perform the update itself. This involves much overhead and network traffic and basically defeats the purpose of Client/Server. A better approach would be to use an SQL UPDATE query. With the following Delphi code as an example: ----------begin snippet---------- With AdsQuery2 do Begin Close; SQL.Clear; SQL.Add('UPDATE CurDouble SET Amt. Paid = TRUNCATE(' + Edit1.Text + ', 2) WHERE Amt. Due > 0'; ExecSQL; end; ----------end snippet---------- You'll notice that the logic used to determine which records are updated is included in the SQL UPDATE statement. This enables the server to perform the logic and updates without having to read records back to the client. Also, the Advantage Database Server has logic built in which will automatically retry updates to records which may have been previously locked, which increases the percentage of mass updates in a multi-user environment. A side note to point out is the use of the TRUNCATE( ) function in the SQL statement. This is to insure that if users enter an extra decimal value when entering a dollar amount, that value will not be stored, so it will prevent math operations (like the SUM( ) function), from returning incorrect results. A true currency field is planned for a new future release. A ROUND( ) function is also available.
|