How I dramatically sped up DB file updating

A few weeks ago I was bemoaning the length of time it took to update a mere
500 records from an Access MDB file...about 28 seconds.

It turned up that the "potential" for updating all 500 records existed, but
in practice most records did not need to be updated. After the calculations
had been completed, the record ended up in the same "Class", with the same
costs, etc. Once that light bulb went off in my head, it was a simple
process to test all the fields that potentially could have changed, and then
only update the record if one or more changed.

I replaced this type of line:

rs.Fields.Item['Assigned'].Value := ClassIDs[k];

with

DBUp(rs, 'Assigned',ClassIDs[k]);

which called:

procedure DBUp(rs : RecordSet; Fld : string; New : OleVariant);
var old : OleVariant;
begin
old := rs.Fields.Item[Fld].Value;
if old <> new then begin
rs.Fields.Item[Fld].Value := New;
Dirty := true;
end;
end;

Of course Dirty was set to false when each record was read, and

If Dirty then
rs.Update

was called.

By substantially reducing the number of times I actually updated the DB, the
time was cut substantially.

Before hitting on this solution, I was surprised to find that
adLockPessimistic seemed to run a bit faster than adLockOptimistic. Also,
there seemed to be very little time difference between Update and
UpdateBatch in my application.


.