Why does ADO Next record processing slow down in Delphi?

Question

I had a Delphi 4 program that I developed many years ago that used Opus DirectAccess to sequentially search through a Microsoft Access database and retrieve desired records. Delphi 4 did not have ADO, so that's why I was using DirectAccess.

But I've now upgraded to Delphi 2009 and converted the program to use ADO. What I found was that the loop through the table (of some 100,000 records) starts off as fast as it did in DirectAccess, but then it starts slowing down and gets slower and slower as it goes through the table. The basic loop is:

ArticlesTable.First;
while not Cancel and not ArticlesTable.Eof do begin

  ( See if the current record has criteria desired )
  ( If so, process the record )

  ArticlesTable.Next;
end;

So basically, it is just processing the records sequentially using the .Next method.

So why is it slowing down, and how can I recode this so that it won't slow down?

Answer 1

You should call DisableControls on all ADO datasets if you aren't using DB aware controls on the dataset.

Otherwise the speed sucks.

refer to this article for details.

Alternatively, use the internal ado recordset property

while Not ADOQuery1.Recordset.EOF do
begin
  ADOQuery1.Recordset.Movenext;
end;

Answer 2

Additionally, you can change the CursorType property of your Access Component (TADOTable/TADOQuery/...).

Try ctOpenForwardOnly to imporve performance; It's possible that you need to disconnect de DBGrid (if you have conected one) and reconnect on exit the loop.

Regards.