Performance: Reading all values under a field in a dataset

[djjd47130]

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


JD Solutions

 

[djjd47130]

To be more specific, we have to loop through every record in the dataset, read 2 fields (ID: Integer and Val: String) and add it to a combobox. Well when there's over 20,000 records, this takes a very long time, and I'm looking for a faster way.

JD Solutions

 

[Glenn9999]

Are you using BeginUpdate/EndUpdate pairs?

Combobox1.Items.BeginUpdate;
<put stuff in ComboBox1>
ComboBox1.Items.EndUpdate;

It is not possible for anyone to acknowledge truth when their salary depends on them not doing it.

 

[djjd47130]

Yup, actually much more about it here:

http://stackoverflow.com/questions/...reading-all-values-under-a-field-in-a-dataset

JD Solutions

 

[TonHu]

Adding 20.000 items to a combobox is insane, IMHO this should be called a design error.

If you are filtering the contents of the dataset and occasionally put an item into the combobox that complies to some filter, then don't filter that from the application but do that in the database by using a where clause in your query.

 

[djjd47130]

In this scenario our customer insists on keeping the 22,000 values, otherwise we did suggest to them to do some cleanup and merging. Our software keeps inventory, each item has 3 colors assigned to it, and it's the table holding the colors which it loading into 3 combo boxes actually. So when the user clicks the "Edit" button, it then loads the colors into all 3 boxes. Except, our software was never designed for holding even 5k values in these lists, but over 12 years of using our software, they've used this field for something else, storing other unique information rather than colors. This is how they wound up with over 22k values.

The whole idea is to figure out if there's something in the query component to quickly export all values under a field without looping through using TADOQuery.Next method. Actually, I did speed it up by about 1.5 seconds total, but could still use a little more help.

JD Solutions

 

[TonHu]

I'm sorry, but in that case I can only repeat my earlier statement:

Adding 20.000 items to a combobox is insane, IMHO this should be called a design error.

You should do something to the customer or to the design to solve it properly. I'd go for the second option, so the first will follow automatically, that's friendlier

 

[DjangMan]

I tried to post a comment over on the other forum but I wouldn't let me.

Only one person mentioned sorting in the combo and only in passing. Make sure that your combo is not sorted while you're adding records to the list. TComboBox is not sorted by default so you might be okay.

You might look deeper into the VCL and see if any messages are being broadcast when you add an item to a combobox. That might slow things down, too. But the begin update/end update might stop that from happening.

Try having the database do the field concatenation for you instead of you doing it in code. Then you can avoid two calls to .fieldbyname (which makes the code look through all of the field names in the result set each time) and you can make one call to .Field[0].AsString (which will be faster since you aren't iterating through the fields).