sudden access slowdown

[hmmcrunchy]

HI all I hope you can give me a few pointers this is our situation at the moment:



We have a 2 server 2008R2 servers each running an access back end mdb (2 different systems both in access 2010)

Both are accessed through a accdb front end with linked tables to the backends we have about 75 users using each program

Since last Thursday all of a sudden one of the systems has started to run very slow - taking up to 4 minutes to access a record or run a query, the other one is perfectly fine and no slowdown on any other network programs or shares

No programming changes were made recently prior to this issue, servers were updated (windows update) but nothing else has changed

Has any one come across anything similar.

thanks very much

[ItsMe]

Have you tried issuing new (unused) copies of the front end files to the users?

[hmmcrunchy]

hi Itsme thanks for reply

yes ive tried this and it doesn't seem to help either - we have a program that replaces the front end each night as they tend to bloat (and to make sure the users have the most up to date system)

[ItsMe]

The only other thing I can think of would be to make sure all of the users have a persistent connection as soon as they launch their FE file. Maybe the Main Menu form can be bound to a user profile table or something.

[hmmcrunchy]

thanks ItsMe,

the front screen is connected to a user table to authenticate the user straight away and give them access rights, this seems to work ok but a lot of the queries later on in the database seem to be taking an age

another difference in the two is the slow one has just tipped over the 1GB size mark, but I was under impression that max size was 2GB.

I have heard of issues of mdb files over 1GB having issues

[ItsMe]

...this seems to work ok but a lot of the queries later on in the database seem to be taking an age...

The point I was making is to ensure that the initial connection is persistent. Once the connection is created, using a very small recordset, maintain the connection. Do not close this connection to the BE file. The last thing the app should do before quitting is close this connection.

As for the size of the BE file... It should not cause a difference in the performance. What will affect performance is the client hardware's capability of processing and memory. Make sure your clients have a decent amount of horsepower. Then, make sure you do not do a table dump every time a user opens a form.

You can have a very powerful client machine and tax all of its resources by retrieving an entire table vs. retrieving a smaller dataset. Employ the WHERE clause in your SQL to ensure you are not unnecessarily taxing the client machine's resources.

[hmmcrunchy]

sorry got the wrong end of the stick there, ill try this out - thanks for you help itsme

[hmmcrunchy]

HI Just to update this (I hate threads left unfinished) the issue turned out to be corruption in the database itself - indexes damaged, we imported the entire database into a new mdb and that has resolved the issue