Populating Combobox slow

[SANCHIT SHARMA]

Dear jmcilhinney and kulrom,

I am having multiple comboboxes on Vb.Net windows form. In these comboboxes we are using both Displaymember and Valuemember. my query is : What the way i should adopt to make the things faster.

On load event of the form we have written

Dim Dread_LS_PR As OleDbDataReader = Nothing
Dread_LS_PR = OBJ_QRY_LS_MINEPR.ExecuteReader_Cmd("SELECT CD_CLIE.CLIEDescription,CD_CLIE.CLIEID FROM CD_CLIE WHERE (CD_CLIE.LOGICALDELETE = 0 OR ISNULL(CD_CLIE.LOGICALDELETE)) and (CD_CLIE.MERGED = 0 OR ISNULL(CD_CLIE.MERGED)) and not CD_CLIE.CLIEDescription Like '00%' ORDER BY CD_CLIE.CLIEDescription ;")
DT_CD_CLIE.Load(Dread_LS_PR)

This table pertains approx 10000 records.



Here C_Clieid is a combobox

C_CLIEID.DataSource = DT_CD_CLIE 'DS_LS_ACTI.Tables("Union_CD_CLIE")
C_CLIEID.DisplayMember = "CLIEDescription"
C_CLIEID.ValueMember = "CLIEID"
C_CLIEID.MaxDropDownItems = 10
C_CLIEID.SelectedIndex = -1
'finally implemented column headers
C_CLIEID.ShowColumnHeader = False
C_CLIEID.ShowColumns = True
C_CLIEID.MaxDropDownItems = 9
C_CLIEID.ForeColor = Color.DarkBlue
C_CLIEID.ColumnHeaderBorderStyle = Border3DStyle.RaisedInner Or Border3DStyle.SunkenOuter Or Border3DStyle.Adjust
C_CLIEID.AutoCompleteMode = System.Windows.Forms.AutoCompleteMode.SuggestAppend
C_CLIEID.AutoCompleteSource = System.Windows.Forms.AutoCompleteSource.ListItems


This is taking a good amount while Extracting data and population the Combobox.

Is there any faster way to do the same.

 

[zekeman]

Try running just the .exe of your project found in the bin directory.

I have found that things like this run alot faster this way than they

do using the IDE if in fact you are now using the IDE.

I'm new to this 2005 stuff so not really much help probably.

 

[vis781]

How many records is this particular query returning? If there is a lot then i would expect it to take a while. You maybe need to think about displaying a progess bar. Or possibly do the database query on a separate thread leaving the form to load without having to wait for the query to finish. Whilst what zekeman has said is true, it's not a way to optimize your app. Whether your app is compiled in release/debug mode, if it is run from the IDE it is always run in full debug mode so yes all the debug information will bog down your app, but dont' use it as a way to say.. 'hey, all of a sudden my program runs faster' Think about it in terms of, if i can get my app to run nice and fast in debug mode think of how fast it will be in release mode.

 

[cjard]

Please.. tell me that there's no way all 10000 records (or even, any more than about 20) can make it into your combobox..

It's the database query that is taking the time ; i cant see how zekeman's advice will help a lot with that.. Ask on a forum relevant to the DB youre using, for help in optimising the query

 

[SANCHIT SHARMA]

We are trying to rebuild a current Access 2002 (XP) mdb program to VB.NET
with a SQL database - we are having problems with a suitable combobox.
There are many threads discussing multiple column comboboxes in .NET. We
are having success with the multiple columns similar to the combobox from
Access 2002 (XP).

Our biggest problem is speed. In a form in Access 2002, our combobox is
able to load data in a table in separate Access database file in a fraction
of a second. This table is already sorted in the proper order, and has
65,000 records (almost the limit of items in the Access combobox). For this
speed test, the table consists of one field. The records are just integers
counting from 1 to 65,000. The Access combobox is filled with data by
setting the combobox.RowSource property to equal a SQL Select Statement:

SELECT field1
FROM table1
IN 'c:\pathto\database.mde"

We tried to do a similar test using the VB.NET combobox with Microsoft SQL
Desktop Edition 2000. Same table as above. We tried several approaches
including binding to a DataSet, binding to an array (filled the array with
DataReader), and using the combobox.Items.AddRange(array). The best result
we could get with the 65,000 records was to use a DataReader to fill an
Array, then use the combobox.item.addrange - but this was approx. 7 seconds -
still slow compared to Access, and too slow for our purpose. This 7 second
was during the AddRange step, not during the looping through the DataReader
to fill the Array. Filling the Array was very quick. All of our tests
indicate the the slow down is getting the information into the combobox, not
get the data from the database.

Is there a Microsoft suggestion get a large list of data into the combobox
quickly the way Access could do it?

Any suggestions?

Thank you for all that took the time to read this long message.

 

[vis781]

While i would question why you would ever want to put 65'000 items in a combobox? The AddRange Method Simply calls the Add method of the collection anyway. When you 'AddRange' there is a for - next / for - each, or some kind of loop in the ISyncList class that iterates through the array passed in, in the argument so that is probably what is taking the time. Have you tried adding the data straight into the combobox from the datareader rather than populating an array first.

 

[cjard]

Our biggest problem is speed.

I actually feel your biggest problem is that noone on your development team appears to have heard of a little concept called HCI...

In a form in Access 2002, our combobox is
able to load data in a table in separate Access database file in a fraction
of a second.

It doesnt actually load it - in Access youre in a front end sitting on top of a database back end. Assigning a combobox property so that it knows where to source its data will indeed only take a fraction of a second.. In a similar way that this only takes a fraction of a second:

Dim x as String = "hello world"

This table is already sorted in the proper order, and has
65,000 records (almost the limit of items in the Access combobox). For this
speed test, the table consists of one field. The records are just integers
counting from 1 to 65,000.

Ignoring the speed issue for a second, I'd like you to time how long it takes for an average person (just ask them in off the street) to select ten random integers from the list. Aside from guaranteeing that this will be longer than the time required to bulk thousands of entries into a combo, it will perhaps give you a glimpse of the nightmare youre creating for your end users

Is there a Microsoft suggestion get a large list of data into the combobox
quickly the way Access could do it?

I'll turn this question around a little:
Have you ever seen microsoft put more than about 40 items in a Combo? The worst offender I can think of is in the Display control panel, where you set up the windows colour scheme. In windows 95 this was a long list. In XP, it's down to about 20 items.

Any suggestions?

Assess what youre actually trying to achieve, and whether youre using the right control for it. There's a good reason why Excel lays out 65 thousand cells in a grid pattern, not a combobox.

In my app, I use combos that have a datasource of a table.. It takes microseconds to populate them, and not just because there's a low number of entries.. The data is preloaded into memory during application startup because it doesnt change. It's stored in a DataTable so i can simply say Combobox1.DataSource = DataTable1.

Thank you for all that took the time to read this long message.

Youre welcome.. I hope my comments help you reach a good solution

 

[SANCHIT SHARMA]

Cjard,
Thanks for the suggestion.
In the example defined earlier we are following the same as suggested by you. But still we were trying to find out a faster way to do the same.

 

[cjard]

Faster way of loading the combo? well.. the other point i thought of asking was - how much variance can you truly experience in such a massive data set that means you have to worry about the performance consideration of reloading the items?

i.e. How often do you think you will be reloading all 65000 entries into your combo?

 

[Marlene]

I am having a problem with my comboboxes and performance so I searched and found this thread which was pretty much exactly my problem.

Okay, so comboboxes aren't meant for long lists. My user has this dialog called QuickSearch which she wants to display every record that's in her database table, four of the columns from it. For each column there is to be a combobox listing the values in the database. She can select one and her main form will display that record. This is how it is currently in Access and I am converting to vb.net and SQL Server. It means I have 9000 items in my combo boxes. Performance problems didn't rear their ugly heads until I added AutoComplete capability. I'll eliminate that if I have to, but what do I do about the user wanting this search mechanism? Give it to her and tell her that peformance will stink because her new architecture isn't like Access? Supposedly customers of her company will be calling on the phone and want instant information so even a 10 second wait is not acceptable.

Thanks.

 

[SANCHIT SHARMA]

It means I have 9000 items in my combo boxes. Performance problems didn't rear their ugly heads until I added AutoComplete capability. I'll eliminate that if I have to, but what do I do about the user wanting this search mechanism?

Marlin.. in such case you can suggest user to use wild card '%'. But this action will return multiple information fulfilling the search criterian from the database . In case of exact search wild card will not work..This problem can be resolved with a slight change in the form design.But before putting any suggestion I will be keen to understand your search design.

 

[cjard]

Supposedly customers of her company will be calling on the phone and want instant information so even a 10 second wait is not acceptable.

How long do you think it would take a human being, to find and successfully select an item in a combo, from a list of 9000? I'm pretty good at 'puters and i reckon it would take me longer than your maximum acceptable time

I have to say that the way it is implemented right now is just plain silly. There is NO sense at all in loading all of a database into a client app, then performing some search/filtering there, not even access does something this silly (even though it might give the impression that it does)

Rethink your search form logic, have the database do the searching (that is what they are designed for), use textboxes not combos, have the textbox fire off a db query after 3+ characters have been typed. Initially the db query should be to merely show the matching item count. Once you get less than e.g. 50 items, show all 50 in a datagridview. As the user types, progressively keep searching until the result set is very small. Send the result the user picks, back to the main screen. If the user is typing quickly, dont run a search until they have paused for a fraction of a second.

For an example of this in action, use foobar2000 music player, load a LOT of music into it (e.g. 20 thousand items) and press F3. A search window appears, and you type text to find in the list. Unlike winamp, which begins searching right away upon first keypress and drags forever, foobar waits until it thinks you have stopped typing (i think it takes the simplistic route of waiting for 0.5 second after the last keypress. If no other keypress occur in this time, a search is run)
--> This is easily implemented using a timer that ticks every 100 ms, upon every tick, an integer is decremented. upon every keypress the integer is reset to 5. This means if the person types 10 characters at 0.4 second intervals, the integer never hits 0. Then half a second passes, so our int is decremented all the way to 0. When the integer hits 0 and a tick occurs, stop the timer and run the search. A search string of 10 characters is passed to the database, which will look up much faster than a search string of length 1.


For the love of god, HCI and user friendliness, do NOT load more than 40 items into a combo. Ever..