1

I have a database *.mdb. The task is to get all the values from a certain field of this database and write them to a file. Now the code looks something like this

while not ADOQuery1.Eof do begin
   resultstringlist.add(ADOQuery1.FieldByName('_fieldname_').asString);
   ADOQuery1.next;
end;

It works, but I think that there is a more optimal and faster option. With a database size of about 40,000 records, this process takes quite a long time. Database which I need to work with contains dozens of times more records and dozens of tables. I think this is a pretty stupid question, but I could not find a solution in a few days myself. Databases for me is an unexplored territory. Not for long, I hope.

Anton
  • 21
  • 1
  • What does your SQL query look like? Do you have an index on the field you are querying? – Remy Lebeau Apr 06 '18 at 18:33
  • Why are you retrieving 40K (or more) records in the first place? There's no way you can be usefully displaying that to the user in a listbox or combobox. Sounds like you should be rethinking both your application logic and SQL statements to me. In addition, you're spending a lot of time reallocating memory for the stringlist, because it has to keep growing and growing and growing, which also consumes a lot of time (and can fragment memory pretty badly). – Ken White Apr 07 '18 at 00:43
  • I can create a new database with a few fields I need to work with. It will have about 20-50 fields, but all the same 40,000 records It's not usual exchange of client-server or client-client. This is a database with accumulated experimental data for few days, which I need to work with. Construct graphs, interpolate, evaluate the correlation between different fields, etc. The first field in each record is a point along the X axis (datetime format). The remaining fields are points along the Y axis (real or integer format). I can't take just a hundred points and work only with them. – Anton Apr 07 '18 at 07:39

1 Answers1

5

Don't use FieldByName in a tight loop, it does a linear search through the dataset's Fields on each call. Instead, look up the field before the loop and assign the result to a TField local variable and access the field value in the loop using that variable. So, use something like

var AField : TField;
...
AField := ADOQuery1.FieldByName('_fieldname_');

ADOQuery1.DisableControls;
try
  while not ADOQuery1.Eof do begin
    resultstringlist.add(AField.asString);
   ADOQuery1.next;
  end;
finally
  ADOQuery1.EnableControls;
end;

The calls to DisableControls and EnableControls avoid the loop being slowed down by updating any db-aware gui controls connected to the AdoQuery.

You might also get into the habit of calling BeginUpdate and EndUpdate on any TStrings-descended objects while updating them in a tight loop.

MartynA
  • 30,454
  • 4
  • 32
  • 73