Increase Speed of Opening Form (Access 2000)

Topic

New Reply

I have a database on a network drive with a form that contains 2 listboxes. Each listbox’s row source is a saved query. The form doesn’t open very fast. Would it be help the performance of the form if I generated the listbox’s queries in code upon open of the form?

Sarah

Reply | Quote

Replies

I wouldn’t think so – Access kind of compiles saved queries to optimize their performance. Obviously, this can’t be done if you create an SQL string to act as row source in code.

Reply | Quote

I don’t know if that would help. What makes you think it is the listboxes that are slowing down the opening of the form? Do you have a lot of code in the form’s module? I’ve read that heavy forms can be slow to open. Have you tried removing the listboxes temporarily to see if there’s a difference? If your form is code heavy you can create custom functions in standard modules and call them from the form passing whatever values you need to.

Reply | Quote

I’m attaching a JPG of the 2 queries in design view.

I guess I’m assuming the performance prob is due to the listboxes(lstAvailable and lstInstalled) because qryAvailable is using qryInstalled. And qryInstalled has a criteria of a text box on the main form. I’m thinking it’s the circular effect that’s causing the slowdown. There isn’t a lot of code on the form at all. A couple of buttons with code in the OnClick event and code to requery the listboxes in the OnCurrent event of the form. That’s it.

Sarah

Reply | Quote

FYI – The main form’s record source was an SQL statement, so I saved this as a query and now the form opens up in half the time. It still takes about 15 seconds to open, but it’ s better than waiting 35 sec.

If anyone has more ways to improve the speed of opening this form let me know.
Thanks.
Sarah

Reply | Quote

Is there a specific reason for using a query on a query? It may be significantly faster if the two queries are combined into a single query. Also, how many records are involved, and what indexes exist on the underlying tables – those two issues can have major impacts on performance. Another thing you could do is make the control unbound, so it opens up promptly, and then set the control data source in the OnCurrent event. The delay would still be there, but the user would know things were going on – not a perfect solution, but something to consider.

Reply | Quote

I took your advice on setting listbox row source in the OnCurrent event of the main form. This decreased open up time by 10 seconds. Now opens in about 5. Getting better!

As for your other comments/questions…

Yes there is a specific reason for using a query in another query. I’ll try and explain it. I have a listbox that contains all the company’s software(lstAvailable). In another listbox I have a single employee’s software they have installed on their computer (lstInstalled). When I click on an entry in lstAvailable and click a button, that entry appears in lstInstalled and disappears from lstAvailable. The queries allow this “disappearing” to happen. So it’s really not possible to combine the queries (as far as I can tell).

As for # of records, it’s under 1000. As for indexes, if there is a lot of indexes (meaning over 5) doesn’t that decrease performance just as much as not having any indexes? I have 4 indexs set on one table and 12 indexes on the other. If 12 is too many, I’ll have to remove some.

Sarah

Reply | Quote

I assume your two listboxes are refreshed so that each time the there is a button click to change the selection for one listbox, both are requeried? That is, if I want to delete a currently installed software package, I click on the package in the installed listbox and then click the update button and both listboxes are updated. In that case, the listboxes would have different data sources, but I don’t understand why either would require a query on a query. It seems you should be able to do it based on joins where one would do the unmatched software and the other the matched software. I also assumed that you have some DAO or ADO code that adds or removes records from tbl_Installed each time the update button is clicked.

As far as indexes, the only important ones would be the primary keys for each table, and a corresponding index on any foreign keys in other tables. Performance in select queries isn’t really affected, or is improved by, the number of indexes – that is not true however of action queries that edit, append or delete data.

Reply | Quote

Answer’s to your questions/assumptions, Wendell…

W: I assume your two listboxes are refreshed so that each time the there is a button click to change the selection for one listbox, both are requeried? That is, if I want to delete a currently installed software package, I click on the package in the installed listbox and then click the update button and both listboxes are updated

Me: YES.

W: In that case, the listboxes would have different data sources, but I don’t understand why either would require a query on a query. It seems you should be able to do it based on joins where one would do the unmatched software and the other the matched software.

Me: I don’t know about this, but I’ll look at it again.

W: I also assumed that you have some DAO or ADO code that adds or removes records from tbl_Installed each time the update button is clicked.

Me: YES, ADO.

W: As far as indexes, the only important ones would be the primary keys for each table, and a corresponding index on any foreign keys in other tables. Performance in select queries isn’t really affected, or is improved by, the number of indexes – that is not true however of action queries that edit, append or delete data.

Me: Thanks for the info.

Sarah

Reply | Quote

Another thing that speeds up form loading in A2k and above is turning off subdatasheets on the tables involved. Subdatasheets act like additional queries and can seriously slow the loading of tables themselves, let alone queries based on them.

Reply | Quote

I find that if the query is linked to other queries that contain grouping functions or many calculations, the form may be slow to open while the queries compile that generate the data for the listbox. Using code to do this doesn’t really help as the queries would still need to be run to generate the data for the listbox. (You could try using indexes in the queries to speed them.) However, In these cases, if the time to open is too long, I have written the data to a table and used the table data to open the listbox (so the queries aren’t run). Of course, you would then need to take into consideration updates to the table.

You might want to post what it is you are doing to see if there are better ways of doing the process. I have done this in the past to much success. Not only was the code shorter, but much more efficient.

HTH

Reply | Quote

I would just like to add that I found out turning off the Name AutoCorrect on the database speeded up the opening of my forms. By quite a bit actually on the one I asked about in this post. I’ll just turn it back on when I change names of objects and then turn it off again.

Sarah

Reply | Quote