Solved

how is load speed up combobox content loading ?

Posted on 2002-05-14
13
434 Views
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)
       rs.MoveNext
   wend
end sub

I have a Delphi program which loads this same amount of records in a very short time, how does it do this ?
0
Comment
Question by:eugeneng
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +3
13 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
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.
0
 
LVL 45

Expert Comment

by:aikimark
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.
0
 
LVL 2

Expert Comment

by:ventond
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.
0
Technology Partners: 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!

 
LVL 45

Expert Comment

by:aikimark
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.
 
0
 

Author Comment

by:eugeneng
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 ?
0
 
LVL 45

Expert Comment

by:aikimark
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
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.
0
 
LVL 45

Expert Comment

by:aikimark
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))
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7016153
It also matters how many characters are in your 45000 strings.  

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

Form_Activate
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
0
 

Author Comment

by:eugeneng
ID: 7020587
aikimark,
  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.

 Thanx
0
 
LVL 45

Accepted Solution

by:
aikimark earned 100 total points
ID: 7020682
Define a loop-tracking variable:
Dim lngLoop as Long

Somewhere inside your loop add:
If lngLoop = 50 Then
    DoEvents
    lngLoop = 0
Else
    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.
0
 
LVL 49

Expert Comment

by:DanRollins
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
0
 

Expert Comment

by:SpideyMod
ID: 7838157
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

728 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