• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

how is load speed up combobox content loading ?

I've a db table which contains 2 fields only, but has 45,000 of records, and now I have to load one of the field data into a combobox. if using the normal do-while loop and AddItem function, it takes quite some time to finish loading 45,000 of items. anyone know anyway to speed up the process ?

The typical code is :
sub LoadData()
   while (not rs.EOF)
       combo1.AddItem rs.Fields(0)
end sub

I have a Delphi program which loads this same amount of records in a very short time, how does it do this ?
  • 6
  • 2
  • 2
  • +3
1 Solution
Under NO CIRCUMSTANCES whatsoever should you EVER even consider loading 45000 lines into a Combobox.  That is ABSOLUTELY insane.  What user would EVER be willing to search through than many entries to find something.  I hate to be dogmatic, but there MUST be a more INTELLIGENT aproackh than simply stuffing that many entries in a combobox.

In the Delphi program, you are probably using a BOUND control, so that the data is loaded as needed, rather that by you "manually" loading the items as your code shows.

But I repeat, UNDER NO CIRCUMSTANCES should you EVER load that much data into a combobox.
1. Delphi combobox is backed by a TStringList component and is very fast, efficient, and powerful.

2. I'm surprised you can add more than 32K records to your combobox without causing an error.

3a. I recommend using a bound combobox if you expose all the records to your user.

3b. Alternatively, use an unbound third-party combobox that is a virtual window to the data.

You might be able to reduce the elapsed time needed to fill the combobox.  Explore the following:
a. Compare performance using a Do...Loop against your While...Wend code.

b. Hide and disable the combobox during loading.

c. Load the combobox in a Timer control's Timer event, if the user doesn't need to access the data immediately.

d. If you are using the combobox as a type-ahead control, you might build the contents of the combobox based on what the user types.  You might quickly clear and load enough items to display what the users want to see.  You might also use other controls to provide the UI you desire.
I have to agree with Arthur on this one. Find some way to reduce the amount of data being put in the combo box. If I was the user, my finger would be tired from holding down the pgdn key to get to the bottom of the list.

Perhaps if I knew what the data was it would make sense to me.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

I must agree that if the user can only scroll to an item in the combobox, then you want to keep the number of combobox items reasonably low.

However, if you are required to implement a certain UI, certain design choices may be out of your reach.  So, other performance improvements you might want to explore are:
1. http://www.devx.com/upload/registered/features/vbpj/1999/08aug99/fb0899/fb0899.asp

2. using Windows (combobox) API calls

3. use a Rich text control instead of a combobox.
eugenengAuthor Commented:
so..what GUI should I use present these data to the end user then? the end user can't not remember those code, somehow I have to present these data for him to choose, if not using combobox, any good suggestion ?
You CAN use a databound combobox.

You can fill a combobox/listbox after the user has typed a couple (or three) characters in a text area.  

idea1: In the change event for the text area, determine the number of items you would put into a "choice list".  If the number of items is below some good-performance threshhold, then clear/rebuild the "choice list".

idea2: give the user a button that will build the complete list.

idea3: keep track of the user's choices and create a Most-Recently-Used list from which they can choose.  A combobox or listbox is ok to use for this purpose.
surely you can group the "codes" in some logical fashion, and then allow the user to pick from sub-lists.  What are the 45000 codes for?

Perhaps there are several layers of structure in teh codes, and you can use that as a way to "drill down".

For instance, Countries are divided into States, which are further divided into Counties, which are further divided into Cities, which are further divided into postal districts.  If ou need access to the postal district, start with choosing the country, then the state, and so on, to pare the final list of entries to be chosen from to a manageable number.
You can also use an API to load an item into a combobox
Call SendMessage(combo1.hwnd, CB_ADDSTRING, vbNullChar, rs.Fields(0))
It also matters how many characters are in your 45000 strings.  

Elapsed for 45000 items:     0.9511719  (abc )
Elapsed for 45000 items:     1.783203   (abc def )
Elapsed for 45000 items:     2.884766   (abc def ghi )
Elapsed for 45000 items:     4.398438   (abc def ghi jkl )
Elapsed for 45000 items:     6.158203   (abc def ghi jkl mno )

You are going to get a 33% reduction in load time by making the combobox invisible.  SendMessage API times are comparable.

Elapsed for 45000 items:     9.763672   (abc def ghi jkl mno ) visible
Elapsed for 45000 items:     6.029297   (abc def ghi jkl mno ) invisible
Elapsed for 45000 items:     5.990234   (abc def ghi jkl mno ) invisible & DoEvents
Elapsed for 45000 items:     6.039063   (abc def ghi jkl mno ) invisible & height=0 & DoEvents
eugenengAuthor Commented:
  yeah it works!! altho it still takes some noticeable time, but this is tolerable. I'm going to load the data when the program start up (because user don't see this at first). how can I make this loading as backgroup process ? if it is using "DoEvents" can you show me some snippet coz I'm not quite sure how to use this.

Define a loop-tracking variable:
Dim lngLoop as Long

Somewhere inside your loop add:
If lngLoop = 50 Then
    lngLoop = 0
    lngLoop = lngLoop + 1
End If

There are two standard practices:
1. Create a "splash" form that disappears when you've finished the Form_Load process.

2. Add a picturebox control that overlays all other controls on your first form.  You can either display some progress indicator or informational messages (news, tips, etc.) for the user.
Hi eugeneng,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept aikimark's comment(s) as an answer.

eugeneng, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
DanRollins -- EE database cleanup volunteer
per recommendation

Community Support Moderator @Experts Exchange

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 6
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now