CODEWITHSUNDEEP
delphidatasetado
Pieter van Wyk
Pieter van Wyk

Reputation: 2378

Delphi ADO Query

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.

Upvotes: 8

Views: 26404

Answers (6)

user2091150
user2091150

Reputation: 998

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).

Upvotes: 0

Darian Miller
Darian Miller

Reputation: 8088

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.

Upvotes: 0

skamradt
skamradt

Reputation: 15538

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.

Upvotes: 0

Germán Estévez -Neftalí-
Germán Estévez -Neftalí-

Reputation: 6848

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.

Upvotes: 10

RRUZ
RRUZ

Reputation: 136391

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

Upvotes: 8

Linas
Linas

Reputation: 5545

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;

Upvotes: 7

Related Questions