Delphi ADO Query

Question

Is there any faster way to iterate through ADO Dataset than

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

I need to scan a dataset of about 9000 items and retrieve only records matching a predefined set of branch numbers.

Answer 1

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 2

Make sure you use DisableControls / EnableControls, if not necessary, so as not to waste time updating the visible controls associated with the DataSet.

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

Sincerely.

Answer 3

@Pieter, two options

1) you can change your sql clause before execution by adding a condition that matches the given set of branch numbers.

2) using the Filter TAdoQuery property.

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

Answer 4

An additional performance boost can be made by avoiding any string matching until it is possible (when everything else matches). If your database has a large number of duplicate rows, consider placing rows in a separate table, bound to the first table as an integer.

Answer 5

Delphi ADO stuff ( TADOQuery or TADOTable ) is not bad, it is horrible (tested with Delphi XE2 / 2007). Exported data from Transbase tables (ODBC driver) to MySQL via sql files and Navicat. A table with nearly one million records requires many hours via ADO (a table with 8 million records was 10% complete after 2 days), several minutes using TQuery (but it may fail due to BDE errors with large tables, BDE has not been updated last 15 years), in a few minutes through pure ODBC or Navicat. My advice: use something instead of ADO (at least until the developers seriously rework).

Answer 6

You can modify the query to include the SQL where clause, for example

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

I would also really like to look at read-only cursors, read-only cursors, to give the greatest speed increase.