System slows down when more users log on

[TG]

I have an ADP solution that has been running for some years. The users have
recently moved to Access 2007 frontend and the backend is SQL server 2000.
The main tables contain millions of lines. Queries into these tables are
based on stored procedures. Maximum number of users (logins) are 15.
Normally there is one or two concurrent users logged on.

Users complain that there seems to be an increase of response times when
more users log on. Max response time for complex queries into the biggest
tables seem to increase from 15 seconds to more than 90 seconds as more
users log on. This happens even if they are not running any queries, just
logged on.

The ADP frontend has some bound forms and a lot of unbound forms.

Is there a way I can disconnect the connection between the ADP client and
SQL server when the user becomes inactive? The purpose of the disconnect
would be to improve response times for the remaining active users. I would
like to reconnect automatically when the passive user becomes active again.
What would the code for disconnect and reconnect of ADP client look like?

We hope to avoid rewriting adp code to mdb.

Regards

Tore

 

[Pete D.]

Have you looked at the default settings in Access 2007 such as name auto
correct. This has serious performance issues. Might gander at
http://allenbrowne.com/Access2007.html
for performance improvements and links to other issus.

 

[Sylvain Lafontaine]

There have been many reports of speed issues with ADP 2007. An hotfix is
now available, see http://support.microsoft.com/kb/936519/en-us/ but I think
that I've saw some peoples still having some speed issues even after the
installation of this hoftixe.

If you have already tried this hotfixe or if it doesn't work, then the best
solution that I would see would be to go back to Access 2003 or to switch to
something else.

 

[Tore]

Your link says that the hotfix is included in Office 2007 SP1. I will check
if it is installed on the next visit to the customer. From your answer I take
it that there is no convenient way to disconnect an ADP client and reconnect
to SQL Server when needed?

Regards

Tore

 

[Tore]

I will look into optional settings. Thanks for the link.

Regards

Tore

 

[Sylvain Lafontaine]

For closing and reopening a connection, you can use
CurrentProject.CloseConnection and CurrentProject.OpenConnection methods.
Use an empty string "" with .OpenConnection if you want to permanently close
the connection before shipping your ADP project to the client.

In your case, probably that you can use the connection string from the
CurrentProject.Connection object if you simply want to close and reopen the
connection but I never tried it.

Finally, updating the statistics by using the sp_updatestats stored
procedure, clearing the caches and make sure that you are not hit by a
compilation problem or with the parameters sniffing process won't hurt:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

For the compilation problem and the parameters sniffing process, look at the
WITH RECOMPILE option and search Google on these terms.

 

[Tore]

Thanks for your advice. I will look into it.

Regards

Tore