Tips for finding bottlenecks please

[revver]

I have a form containing 30+ controls, several of which are buttons.

One particular button launches a new form called "View Reports" which then has its listbox populated from a table of reports. A choice is made from the 7 possible reports and an "Open Report" button is clicked to preview the selected report. All this works fine on the development machine.

When the first button is clicked, "Calculating..." shows in the status bar for a time before the View Reports form is available. Then after a selection is made and Open Report is clicked, "Formatting page, press Ctrl+Break to stop ..." appears there (for minutes compared to seconds on the development box).

There are other points where things go slowly as well. I am not so much looking for a solution to this particular slowdown as I am for a debugging technique (Although all specific hints gratefully received).

The development box is only a P4 with 2.75 GB RAM and it performs reasonably. The target machines are Terminal Servers which perform fine in Word, Excel etc and other Access apps I've written. Multiple users tend to slow the Access apps down but even with just one user in this app the slowdowns frustrate the user.

Any hints appreciated.

 

[boblarson]

1. Is the database split? If not, it needs to be.

2. If split, is a copy of the frontend copied into each user's Terminal Server profile? If not, it should. Running from one single file will cause things to slow down as well.

3. Try using a persistent connection also so it doesn't keep opening and closing connections which will add time to it.

4. For each table, to into design view and in the table properties dialog find the SubDatasheet and change it from Auto to NONE.

See here for more performance tips:
http://www.granite.ab.ca/access/performancefaq.htmhttp://www.google.com/url?sa=t&sour...NaB330W8Wp-lCqp4g&sig2=iLWxUV0N9l-3lFyz7IKItw

 

[revver]

Thanks Bob.

The database is split.
Both FE and BE live in the same folder since at most 2 users at the same time.
No connection strings, just linked tables. I'll look at persistence.
Cannot find subdatasheet in table properties. (2003) I'll look more.

I'll head to the performance faq now.

Thanks again.

 

[SydneyGeek]

Len, once you have done what you can with the config (tackle that first) you may still want to know where you are being slowed down (if you are...)

I wrote this for myself to document multi-step processes, and it has the advantage of letting you analyse the log table for bottlenecks. Maybe it will help you.

Denis

 

[boblarson]

The database is split.
Both FE and BE live in the same folder since at most 2 users at the same time.
No connection strings, just linked tables. I'll look at persistence.
Cannot find subdatasheet in table properties. (2003) I'll look .

Two users or more it makes no difference, a copy for each is essential. And even though you have linked tables, they do not have a persistent connection open. You need to open a connection explicitly by opening a form based on a table, for example.

 

[revver]

Thanks Bob, thanks Denis.

It would be a rare occasion that a second user would be active, nevertheless I'll give them individual copies as you suggest.
When you said before

the frontend copied into each user's Terminal Server profile

did you mean a copy somewhere in each user's "Documents and Settings" structure or just a copy somewhere for that user's exclusive use? For a different app running on the same Terminal Servers I created a folder structure - a "Shared Database" folder with folders under that for each user. Their Desktop shortcuts each launch their individual copies. That did improve speed for that app.

I thought I understood what you are saying about connections. However, I have managed to confuse myself by looking into it in more detail. I thought you created a connection and used that as a parameter in the Open statement. Now I don't know what I think. Can you point me at a description How To or some example code please? Also, I even wondered why link the tables if you are going to connect them. That is until I reread your post and understood that linking does not create a connection.

BTW, I did eventually find subdatasheet. All tables except one said auto. Now it is unanimous.

 

[boblarson]

1. A copy normally is on each user's desktop machine but in Terminal Services it just matters that it is a distinct copy somewhere in their profile which they can get to. It is important that the same file isn't opened at the same time.

2. When I'm talking about connections - when you link a table, Access will open a connection automatically when needed to transfer data back and forth but it also closes the connection when done. So, it can slow things down while it opens and closes, opens and closes. So, you can open a persistent connection which can be where you bind a table to a form and then open that form as a hidden form so there is a connection held open. That helps speed things up.

3. For Subdatasheets you need to change them ALL to NONE. They should NOT be set to AUTO. They should be set to NONE.

 

[revver]

Sorry Bob,
I could have been clearer - All unanimous = all set to none.

I'll use the same Shared Database\Username structure that's already in place for the other app.

Am I right then in assuming that you are suggesting, for example, a do-nothing form bound to the ReportsnPermissions table is kept open but hidden in order to keep the connection alive? I guess in this example I've just chosen, I could keep the real form open but hidden until required. But for other connections like the one needed for the slow report (bound to a select query) I could hide a form bound to the same query thus keeping all necessary tables connected. Have I understood you?

 

[boblarson]

I believe you have it.