Delphi ADO Query

Question

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.

score 10 · Answer 1 · answered Feb 22 '10 at 17:23

10

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.

answered Feb 22 '10 at 17:23

Germán Estévez -Neftalí-

6,442
21
33

Thanks for the link, Gerry. – Germán Estévez -Neftalí- Nov 27 '12 at 09:43

score 8 · Answer 2 · edited Feb 22 '10 at 17:20

8

@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;

edited Feb 22 '10 at 17:20

Mason Wheeler

82,511
50
270
477

answered Feb 22 '10 at 16:47

RRUZ

134,889
20
356
483

4

He's right. The fastest way to accomplish this is to use a WHERE clause in the SQL so it only returns the records you need. SQL is specifically optimized for doing this and it's likely to do a better job than anything you can do client-side. – Mason Wheeler Feb 22 '10 at 17:21
This answer assumes that one *can* filter the data at the db level. I have run into many situations where the filtering involves calls to an application method and simply cannot be done with a where clause. – Josh Doebbert Dec 09 '16 at 20:01

Linas · Accepted Answer · 2010-02-22T17:10:42.590

7

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;

edited Feb 22 '10 at 17:10

answered Feb 22 '10 at 16:59

Linas

5,485
1
25
35

1

Is that true? I figured that the .next .eof and .fields calls would just go to the recordset object anyway. – MarkF Feb 22 '10 at 18:12
You are right, but controlling recordset directly is much faster, because dataset does lots of other stuff which slows down iteration. – Linas Feb 22 '10 at 18:23
1

This will provide a bit of a speed boost, but probably not as much as not having to iterate over as many records in the first place. – Mason Wheeler Feb 22 '10 at 18:53
Well, 9000 - not so many records, but I can bet that recordset would outperform dataset at least 4 times. – Linas Feb 22 '10 at 19:11
4

This is also dependant on DisableControls as noted by Neftali. ADO datasets are very slow if it isn't called. It is only needed if you have data bound controls. – Gerry Coll Feb 22 '10 at 20:11
1

DisableControls and using RecordSet has definately increased the speed in which we now process the records! Thank you for all the tips. Regards, Pieter. – Pieter van Wyk Feb 23 '10 at 08:35

score 0 · Answer 4 · answered May 05 '13 at 12:35

Delphi ADO stuff (TADOQuery or TADOTable) is not bad, it is awful (checked with Delphi XE2 / 2007). Was exporting data from Transbase tables (ODBC driver) to MySQL through sql files and Navicat. For table with near million records it takes many hours through ADO (8 million records table was at 10% done after 2 days), several minutes by using TQuery (but can crash due to BDE bugs with big tables, BDE was not updated last 15 years), several minutes through pure ODBC or Navicat. My advise: use anything instead ADO (at least until seriously reworked by developers).

score 0 · Answer 5 · answered Feb 22 '10 at 18:50

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.

score 0 · Answer 6 · edited Feb 23 '10 at 04:58

0

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.

edited Feb 23 '10 at 04:58

mghie

32,028
6
87
129

answered Feb 23 '10 at 01:46

Darian Miller

7,808
3
43
62

Delphi ADO Query

6 Answers6

Linked