Increase Your Multi-Record Update Performance Significantly

Friday, June 14, 2002

Increase 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.