Delphi ADO Query

Question

Hi. Is there any faster way to iterate through an ADO Dataset than

while (not ADOQuery1.Eof) do
    begin
      /* Do something */
      ADOQuery1.Next;
    end;

I need to scan a dataset of around 9000 items and only extract records matching a pre-defined set of branch numbers.

Thanks, Pieter.

Answer 1

@Pieter, two options

1) you can modify your sql sentence before to execute, adding the where condition wich match with the pre-defined set of branch numbers.

2) using the Filter property of TAdoQuery.

AdoQuery1.close;
AdoQuery1.filter := 'your condition goes here';
AdoQuery1.filtered := true;
AdoQuery1.Open;

Answer 2

It is much faster to use ADORecordset for such tasks:

 while not ADOQuery1.Recordset.EOF do
  begin
    ADOQuery1.Recordset.MoveNext;
    // get value
    SomeVar := ADOQuery1.Recordset.Fields['FieldName'].Value;  
  end;

Answer 3

Be sure that you use DisableControls/EnableControls if it's not necesary for not spend time updating visible controls associated at DataSet.

try
  ADOQuery1.DisableControls;
  while (not ADOQuery1.Eof) do
    begin
      /* Do something */
      ADOQuery1.Next;
    end;
finally
  ADOQuery1.EnableControls;
end;

Regards.

Answer 4

Additional performance gains can be made by avoiding any string comparisons until as late as possible (when everything else matches). If you have a large number of duplicate strings in your database, then consider placing your strings in a separate table, linked back to the first table by an integer.

Answer 5

You may want to change the query to include a SQL where clause, something like

Select whatever fields From whatevertable
where branchnumber in (
    select branchnumber from whatevertable where branchid=xxz
)

I would also highly suggest looking at forward-only, read-only cursors to give the biggest speed increase.