slow combobox

Question

slow combobox

lior gal

SSC Journeyman

Points: 94
More actions
September 8, 2004 at 3:24 pm

#76185

hi
i have an access xp application wich is connected to sql sever 2000. on one of my forms i have a combobox that have a rowsource of over 50,000 records (each record is around 50K). when oppening the combo for the first time it take atlist 7 sec to open.
does anybody have any idea how to reduce that time ?
i tried store procedure,function and view as the combo rowsource but there was mo change.

thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

Hi,

Even though MS Access is known to be very fast when combo boxes are used, I would not display 50,000 records in a combo box. From the problem description in the post I would first check the application code: Is the database connection done implicitly (automatically when the first rowset is populated) or explicitelly (for example, when the main form is created). Second, those 50K per record are a HUGE amount of data to transfer, and I would try to reduce that traffic. As a third option, I would check the execution plan of the SQL.

I hope this helps.

Regards,

Goce Smilevski.

kbrown@pccsinc.org Old Hand Points: 366 More actions · Answer 2

I agree with Mr. Smilevski that 50K records is a lot for a combo box. Could you use another combo box to limit the combo box recordset to something more manageable.

For instance, if this combo box contains a list of 50,000 contacts. Could you use another combo box that has different contact categories, or different states, or cities, area codes? Anything that may group the items in the large combo box? Perhaps this combo box is filled order records or something. You could create another combo box with months, or years.

So, if the user selected a month from one combo box, the second combo box would contain only the records for that month. Or if the user selected a state from one box, the second box would contain a listing of contacts that live in that state.

Almost anything that could be used to limit the number of records would be useful. I have a similar setup on several of the forms in my Access apps. It's pretty easy to implement. If you think of a grouping and need help in implementing the two (or 3 or 4 or 5 ...) combo boxes. Let me know.

Best Regards,

Kyle Brown.