[DUG]: Cached Updates Again

[Stacey Verner]

I sent this yesterday but I didn't get any replies so I thought that I would try again.

 

I though I knew how Cached Updates worked, and I though I know how to use it, but it seems not. One of our users is experiencing a slowdown when editing a large number of records, so I developed a quick test to see what was going on. The following code simply runs through a dataset changing a field and posting the update using cached updates.

 

procedure TfTest.bTestClick(Sender: TObject);
var LTime, LDuration: TDateTime;
  LTotal, LAverage: Extended;
  LCount: Integer;
begin
  LCount := 0;
  LTotal := 0;
  qTest.Close;
  qTest.Open;
  while not qTest.EOF do begin
    LTime := Now;

 

    qTest.Edit;
    qTest.FieldByName('lanes').AsInteger := 11;
    qTest.Post;
    qTest.ApplyUpdates;
    qTest.CommitUpdates;

 

    LDuration := (Now - LTime) * 24 * 60 * 60;

 

    Inc(LCount);
    LTotal := LTotal + LDuration;
    LAverage := LTotal / LCount;
    eCount.Text := Format('%d', [LCount]);
    eTotal.Text := Format('%17.16f', [LTotal]);
    eAverage.Text := Format('%17.16f', [LAverage]);

    qTest.Next;
  end;
end;

 

When this runs I get a fairly dramatic slowdown. ie. The first update takes about 1/100th of a second but, by the time I reach the 200th update it takes nearly 1 second.

 

I have tried the two following variations and got interesting results.

 

...

qTest.ApplyUpdates;

//qTest.CommitUpdates;

...

 

This runs much faster but still has a noticeable slowdown. Previously I believed that this would run very slowly because it would post all changes in the cache each time (ie. The first time it posts 1 change. The second time it posts 2 changes. etc... etc...) because we aren't clearing the cache, but this doesn't appear to be the case.

 

...

//qTest.ApplyUpdates;

qTest.CommitUpdates;

...

 

This runs about the same speed as the original, and it does apply the changes to the database, even though ApplyUpdates is never called.

 

Any thoughts/suggestions?

 

Thanks

 

Stacey

 

Stacey Verner             Ph:   +64-9-4154790
Software Developer        Fax:  +64-9-4154791
                          DDI:  +64-9-4154797
CJN Technologies Ltd.     Email: [EMAIL PROTECTED]
PO Box 302-278, North Harbour, Auckland, New Zealand
12 Piermark Drive, North Harbour Estate, Auckland, NZ
Visit our website at http://www.cjntech.co.nz/