Delphi Performance: Reading all values under a field in a dataset

Question

We're trying to find out some performance fixes reading from a TADOQuery. Currently, we loop through the records using 'while not Q.eof do begin ... Q.next method. For each, we read ID and Value of each record, and add each to a combobox list.

Is there a way to convert all values of a specified field into a list in one shot? Rather than looping through the dataset? It would be really handy if I can do something like...

TStrings(MyList).Assign(Q.ValuesOfField['Val']);

I know that's not a real command, but that's the concept I'm looking for. Looking for a fast response and solution (as always but this is to fix a really urgent performance issue).

Answer 1

Looking at your comment, here are a few suggestions:

There are a few things that are likely to be a bottleneck in this situation. The first is looking up the fields repeatedly. If you're calling FieldByName or FindField inside your loop, you're wasting CPU time recomputing a value that's not going to change. Call FieldByName once for each field you're reading from and assign them to local variables instead.

When retrieving values from the fields, call AsString or AsInteger, or other methods that return the data type you're looking for. If you're reading from the TField.Value property, you're wasting time on variant conversions.

If you're adding a bunch of items to a Delphi combo box, you're probably dealing with a string list in the form of the Items property. Set the list's Capacity property and make sure to call BeginUpdate before you start updating, and call EndUpdate at the end. That can enable some internal optimizations that makes loading large amounts of data faster.

Depending on the combo box you're using, it could have some trouble dealing with large numbers of items in its internal list. See if it has a "virtual" mode, where instead of you loading everything up-front, you simply tell it how many items it needs, and when it gets dropped down, it calls an event handler for each item that's supposed to be shown on screen, and you give it the right text to display. This can really speed up certain UI controls.

Also, you should make sure your database query itself is fast, of course, but SQL optimization is beyond the scope of this question.

And finally, Mikael Eriksson's comment is definitely worthy of attention!