Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how is load speed up combobox content loading ?

Posted on 2002-05-14
13
Medium Priority
?
443 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 46

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 46

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 46

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 46

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 46

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 46

Accepted Solution

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Suggested Courses

730 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