Solved

how is load speed up combobox content loading ?

Posted on 2002-05-14
13
412 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
  • 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now