ComboBox Speed

[Steve]

I have a combobox that grabs a list of distinct items in a table where the
item begins with "10" (10 is a unique designation for us). The table has
about 50,000 records (roughly 1000 of those begin with 10) and the field for
the combobox is indexed. The combobox takes 25-30 seconds to populate when
the form is first opened. Is there anyway to speed that up or pre-load the
combobox?

 

[Jesper Fjølner]

I have a combobox that grabs a list of distinct items in a table where the

item begins with "10" (10 is a unique designation for us). The table has
about 50,000 records (roughly 1000 of those begin with 10) and the field
for
the combobox is indexed. The combobox takes 25-30 seconds to populate when
the form is first opened. Is there anyway to speed that up or pre-load the
combobox?

Is the database on a network drive?

Have you tried initially having the recordsource ="" when the forms opens
and then in the combos OnEnter event setting the rowsource to what it needs
to be?
That would eliminate the wait when opening the form, but may just give you
the wait when you open the combo, but it's worth a try.

Jesper Fjølner

 

[JohnGriffiths]

Hi

Taking the phrase " grabs a list of distinct items in " to mean you are
using a "SELECT DISTINCT ItemID FROM Table1" query.

DISTINCT implies that extra steps will be taken after table scan via index.

Please list your WHERE clause - John

 

[Steve]

Hi

Thanks, the statement is simply:

Select distinct(table1.item) from table1 where left(table1.item,2) = '10';

 

[Steve]

Thanks for the info. Yes, its on a network drive, but the form does not have
any delay even with the recordsource being the query. Its once the user hits
the drop down arrow that it delays.

 

[JohnGriffiths]

Hi, been offline,

You have a lot of work going on you might want to streamline.

If item field is numeric a conversion to string.
The calling overhead of VBA Left() function.

You are using a composite key as your primary key,
consider adding a field for this missing attribute
and adding it as part of your primary key.

So if the first 2 characters represent department then
creating a field to hold the department id would make
a lot of sense.
SELECT DISTINCT table1.item
FROM table1
WHERE table1.Department = 10 ;

This would still take more processing than a staight
forward select so by experiment you could try other
formulations for example :-
SELECT table1.item, COUNT( table1.item )
FROM table1
WHERE table1.Department = 10
GROUP BY table1.item ;

This *might* improve things and give more
information to the user when choosing an item.

Likewise to reduce the overhead (computationally and for the user)
of returning too many rows you might be able to restrict the
selection futher for example using status fields,
created by fields.

Any ideas - John

 

[Steve]

Thanks for the info, my problem is that the table is from our manufacturing
system and I get what it gives me. Unfortunately, I can't add/change fields,
indexes, keys, etc.

The speed issue I seem to have appears tied to the combobox usage. This is a
table with 50,000 records and even with the overhead of the Left() function,
a straight query on the table returns results in seconds as I would expect.
Yet, using it to populate the combobox is a 30 second event.

 

[JohnGriffiths]

A different Tack.

There was a post in the newsgroup with a combobox problem similar to this,
the op created a new combobox (drag and drop) and recreated the query so it
was as uncluttered with changes and got an improvement.

Further investigation between the old and new comboboxes revealed an effect
with one/some of the NotInList properties that brought processing t a halt.

Regards John