Form Loads Slow/Combo Box Row Source Performance

[Michael]

Hi Folks - I have an Access 2000 database in FE and BE configuration. Up
until recently, performance has been fine. Lately, the opening form is
taking a long time to load. It turns out the the record source for the form
is a query that returns 30,000 records. Also, on this form is a combo box
that looks up a casenumber. The Row Source for the Combo Box could contain
30,000 records. So, my first fix was to change the query's criteria so it
references the combo box. This allows the form to load quicker, but it seems
that the combo box's performance has slowed. So, I guess I have some general
questions:

* Is 30000 records significant? The performance issues arise during high
usage of the db, server and network. If db is used first thing in morning or
off-hours, there is no problem.
* Is the combination of combo box row source and form's record source
causing the performance issues?
* When a user selects from a combo box, are all available records loaded, or
does Access fetch only so many?
* What is the best way to handle this situation?

Thanks,

Michael

 

[Jeff Boyce]

Michael

If your form has to load 30000 records before it can focus on a single one,
that's seems to be a lot of unnecessary overhead.

What about if your form loaded no records until a single one was selected,
then only loaded that one? That sounds like what you've done by using the
combobox first.

On the combobox, you might want to look into Allen Browne's routine for
handling 'thousands of rows' in a combobox (search on-line with those
keywords). Basically, by not loading the combobox until after 3 (or 2 or 4
or 7 or ...) characters have been entered, you effectively limit the number
of rows the combobox needs to return.

Since you didn't provide the code you're using to load the combobox, I'll
also point out that you only need as many columns as it takes for the user
to make a positive ID -- save returning all the other columns for when you
load the form itself.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Michael,

Although 30,000 records is not many for an Access database, it is probably a
little large for a single combo box. This would also depend on how many
extra fields you are pulling into the combobox RowSource (the fewer the
better). One technique to get around this would be to have a series of
labels displayed over the combo box ('A-E", "F-J", ...). In the click event
of each of these labels, you could change the RowSource of the combo,
something like:

Private Sub lbl_AF_Click

Dim strSQL as string

strSQL = "SELECT * FROM yourTable " _
& "WHERE Left([CaseNumber],1) IN ("A", "B", "C", "D", "F")
me.cboCaseNumber.RowSource = strSQL

End if

Obviously, if the CaseNumber is numeric, you would use numbers instead of
text.

Another option you might want to consider is to change your backend from
Access to SQL Server.

 

[Michael]

Do you think loading 30000 form records over a network would slow the db
dramatically? As I mentioned, db performance is slow during peak network
demand. Once the initial form is loaded, navigating from case to case is
also very slow. I'm leaning towards network/server congestion for 90% of the
issues, but I'm trying all options within my control.

Thanks for your suggestion.

Michael

Michael,

Although 30,000 records is not many for an Access database, it is probably
a
little large for a single combo box. This would also depend on how many
extra fields you are pulling into the combobox RowSource (the fewer the
better). One technique to get around this would be to have a series of
labels displayed over the combo box ('A-E", "F-J", ...). In the click
event
of each of these labels, you could change the RowSource of the combo,
something like:

Private Sub lbl_AF_Click

Dim strSQL as string

strSQL = "SELECT * FROM yourTable " _
& "WHERE Left([CaseNumber],1) IN ("A", "B", "C", "D", "F")
me.cboCaseNumber.RowSource = strSQL

End if

Obviously, if the CaseNumber is numeric, you would use numbers instead of
text.

Another option you might want to consider is to change your backend from
Access to SQL Server.


----
HTH
Dale

Hi Folks - I have an Access 2000 database in FE and BE configuration. Up
until recently, performance has been fine. Lately, the opening form is
taking a long time to load. It turns out the the record source for the
form
is a query that returns 30,000 records. Also, on this form is a combo box
that looks up a casenumber. The Row Source for the Combo Box could
contain
30,000 records. So, my first fix was to change the query's criteria so it
references the combo box. This allows the form to load quicker, but it
seems
that the combo box's performance has slowed. So, I guess I have some
general
questions:

* Is 30000 records significant? The performance issues arise during high
usage of the db, server and network. If db is used first thing in morning
or
off-hours, there is no problem.
* Is the combination of combo box row source and form's record source
causing the performance issues?
* When a user selects from a combo box, are all available records loaded,
or
does Access fetch only so many?
* What is the best way to handle this situation?

Thanks,

Michael

 

[Michael]

Jeff - I agree with the un-necessary overhead. I have made the relevant
change. The combo box uses only one field. I'm going to test the new change
in the field before I proceed with modifying the combo box.

I'm still trying to guage if 30000 records over the network would normally
degrade performance, everything else being equal. I know there are many
variables. Any thoughts?

Michael

 

[Jeff Boyce]

Michael

There's no need to push 30000 records over the network, when you/your user
is only going to work with one at a time.

If performance suddenly got slow, perhaps it's because, as happened to me,
the network folks installed an update to a network antivirus program that
failed to play nice with Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP