Go Premium for a chance to win a PS4. Enter to Win


how is load speed up combobox content loading ?

Posted on 2002-05-14
Medium Priority
Last Modified: 2008-03-04
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 ?
Question by:eugeneng
  • 6
  • 2
  • 2
  • +3
LVL 44

Expert Comment

ID: 7010049
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.
LVL 46

Expert Comment

ID: 7010137
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.

Expert Comment

ID: 7010242
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 46

Expert Comment

ID: 7010302
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.

Author Comment

ID: 7013022
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 ?
LVL 46

Expert Comment

ID: 7013471
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.
LVL 44

Expert Comment

ID: 7015407
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.
LVL 46

Expert Comment

ID: 7016046
You can also use an API to load an item into a combobox
Call SendMessage(combo1.hwnd, CB_ADDSTRING, vbNullChar, rs.Fields(0))
LVL 46

Expert Comment

ID: 7016153
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

Author Comment

ID: 7020587
  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.

LVL 46

Accepted Solution

aikimark earned 400 total points
ID: 7020682
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.
LVL 49

Expert Comment

ID: 7765505
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

Expert Comment

ID: 7838157
per recommendation

Community Support Moderator @Experts Exchange

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question