Delphi performance: reading all values ​​under a field in a dataset

Question

We are trying to figure out some performance fixes read from TADOQuery. We are currently looking at entries using the "while not Q.eof do begin ... Q.next method. For each of them, we read the identifier and value of each entry and add them to the list with the list.

Is there a way to convert all the values ​​of the specified field into a list in one shot? Instead of iterating over a dataset? It would be very convenient if I could do something like ...

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

I know that this is not a real team, but the concept I'm looking for. Looking for a quick answer and solution (as always, but it is necessary to solve a really relevant performance problem).

Answer 1

There are some great performance suggestions made by other people that you should implement in Delphi. You must consider them. I will focus on ADO.

You did not specify what a back-end database server is, so I cannot be too specific, but there are some things you should know about ADO.

ADO RecordSet

ADO has a RecordSet object. In this case, this RecordSet is basically your ResultSet. The interesting thing about iterating through RecordSet is that it is still connected to the provider.

Cursor type

If your cursor type is Dynamic or Delphi by default, Keyset, then each time RecSet requests a new line from the provider, the provider checks to see if there have been any changes before they return the record.

So, for TADOQuery, where everything you do reads the result set to populate the combobox, and this is hardly changed, you should use the Static type of the cursor to avoid checking for updated records.

If you do not know what a cursor is, when you call a function of type Next, you move the cursor that represents the current record.

Not every provider supports all types of cursors.

CacheSize

The default cache size for Delphi and ADO for RecordSet is 1. This is 1 record. This works in combination with a cursor type. Cachesize tells RecordSet how many records to select and save at a time.

When you issue a command like Next (actually MoveNext in ADO) with a cache size of 1, the RecordSet has only the current record in memory, so when it retrieves this next record, it should again request it from the provider. If the cursor is not Static, this gives the provider the opportunity to retrieve the latest data before returning the next record. Thus, size 1 makes sense for Keyset or Dynamic, because you want the provider to receive updated data.

Obviously, with a value of 1, the connection between the provider and the RecordSet moves the cursor each time. Well, this is overhead that we don't need if the cursor type is static. Thus, increasing the size of the cache will reduce the number of round trips between RecordSet and the provider. It also increases memory requirements, but it should be faster.

Also note that with a cache size greater than 1 for Keyset cursors, if the required entry is in the cache, it will no longer request it from the provider, which means that you will not see the update.

Answer 2

Looking at your comment, here are some suggestions:

There are a few things that are likely to be the bottleneck in this situation. The first is to search for fields repeatedly. If you call FieldByName or FindField inside your loop, you lose processor time by recounting a value that will not change. Call FieldByName once for each field you read, and instead assign them to local variables.

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

If you add a bunch of elements to the Delphi combo box, you are probably dealing with a string list in the form of the Items property. Set the list Capacity property and remember to call BeginUpdate before starting the upgrade and call EndUpdate at the end. This may allow some internal optimizations that speed up the loading of large amounts of data.

Depending on the combo box you are using, there may be a problem with a large number of items in the internal list. See if it has a “virtual” mode, where instead of loading everything forward, you just tell how many elements he needs, and when he crashes, he calls the event handler for each element that should be displayed on the screen, and you give him the correct text to display. This can really speed up some user interface elements.

In addition, you must ensure that the database query itself is fast, but SQL optimization is beyond the scope of this question.

And finally, Michael Ericsson's commentary definitely deserves attention!

Answer 3

You can use getrows . You specify the column of interest to you, and it returns an array with values. The time taken to add 22,000 lines to the combo box is 7 seconds, and the while not ADOQuery1.Eof ... is 1.3 seconds in my tests.

Code example:

 var V: Variant; I: Integer; begin V := ADOQuery1.Recordset.GetRows(adGetRowsRest, EmptyParam, 'ColumnName'); for I:= VarArrayLowBound(V, 2) to VarArrayHighBound(V, 2) do ComboBox1.Items.Add(V[0, I])); end; 

If you need more than one column in an array, you should use a variant array as the third parameter.

 V := ADOQuery1.Recordset.GetRows(adGetRowsRest, EmptyParam, VarArrayOf(['ColumnName1', 'ColumnName2']); 

Answer 4

You can try dragging and dropping all the data into the ClientDataSet and repeating this, but I think that copying the data to CDS does exactly what you are doing right now - the loop and the assignment.

What I once did was combine the values ​​on the server, transfer it with one click and split it again. This actually accelerated the system because it reduced the connection between the client and server.

You should carefully monitor how bottleneck performance is. It could also be combobox if you don't block GUI updates when adding values ​​(especially when we talk about 20K values ​​- this is a lot to scroll through).

Edit: If you cannot change the connection, perhaps you can make it asynchronous. Request new data in the stream, maintain a graphical interface, fill in the combo box if data is available. This means that the user sees an empty combo box for 5 seconds, but at least he can do something else. Does not change the time required.

Answer 5

You cannot escape cycles. “Very long” is relative, but if extracting 20,000 records takes too much time, something is wrong.

• Check your request; maybe SQL could be improved (missing index?)
• Show the code of the loop in which you add items to the combo box. Perhaps it can be optimized. (calling FieldByName multiple times in a loop? using options to get field values?)
• Be sure to call ComboBox.Items.BeginUpdate; before the loop and ComboBox.Items.EndUpdate after.
• Use the profiler to find the bottleneck.

Answer 6

Is your query also bound to some data controls or TDataSource? If so, do your loop inside the DisableControls and EnableControls block so that your visual controls are not updated every time you jump to a new record.

Is the list of items pretty static? If so, consider creating a non-visual combobox instance when the application starts, possibly in a separate thread, and then assign your invisible combobox to the visual combobox when creating your form.

Answer 7

try using DisableControls and EnableControls to increase the performance of the linear process in the dataset.

  var SL: TStringList; Fld: TField; begin SL := TStringList.Create; AdoQuery1.DisableControls; Fld := AdoQuery1.FieldByName('ListFieldName'); try SL.Sorted := False; // Sort in the query itself first SL.Capacity := 25000; // Some amount estimate + fudge factor SL.BeginUpdate; try while not AdoQuery1.Eof do begin SL.Append(Fld.AsString); AdoQuery1.Next; end; finally SL.EndUpdate; end; YourComboBox.Items.AddStrings(SL); finally SL.Free; AdoQuery1.EnableControls; end; end; 

Answer 8

Not sure if this will help, but my suggestion would not be to add directly to ComboBox . Instead, load into a local TStringList , do it as quickly as possible, and then use TComboBox.Items.AddStrings to add them all at once:

 var SL: TStringList; Fld: TField; begin SL := TStringList.Create; Fld := AdoQuery1.FieldByName('ListFieldName'); try SL.Sorted := False; // Sort in the query itself first SL.Capacity := 25000; // Some amount estimate + fudge factor SL.BeginUpdate; try while not AdoQuery1.Eof do begin SL.Append(Fld.AsString); AdoQuery1.Next; end; finally SL.EndUpdate; end; YourComboBox.Items.BeginUpdate; try YourComboBox.Items.AddStrings(SL); finally YourComboBox.Items.EndUpdate; end; finally SL.Free; end; end;