Access 2010 Performance

[EddieN1]

I am having a bizarre performance situations and welcome any suggestions. I'm running Access 2010 front end and SQL Server Express backend. I have about 20 operators on the system at any one time. All the forms and reports will be doing just fine and then, for no apparent reason, some of the operators forms will go really slow... like taking 1-2 seconds to go from field to field on a form. This might last 15-20 minutes, then it will start working fast again. I have not been able to find any common reason. If we open Task Manager at this time, it shows almost zero CPU time and maybe 2Gb of the 8Gb of memory being used. It happens to different operators who are using different parts of the system. Sometimes if the operator closes the database or Access itself and come back in, it clears up... other times it's still slow. Since it is slow on some workstations with different applications, I think it must be related to the workstation. However, we can't find anything. I thought maybe a virus scanner might kick off and slow things down, but if that were true, it should show up on Task Manager, right? I have run sp_whoisactive on SQL Server, but everything appears to be working fine. Any thoughts would be appreciated.

[ItsMe]

If it were a virus, I would expect to see the Idle process high and also CPU usage high. The fact that it is only RAM would cause me to first look at memory leaks from code not closing and instantiating objects/classes to "Set Object = Nothing".

However, it may also be the client trying to play well on the network. Maybe these are newer machines. Other things to look at may be how the client was joined to the network, check that the DNS has the appropriate Host file for client, consider ODBC drivers to SQL connection, and consider SQL connection.

Shot in the dark....

[orange]

Is the BE on a server? Could it be some high, sporadic network traffic? Each user has their own copy of the FE on their PC right?

[EddieN1]

Yes, BE is on stand-alone server. Yes, each operator has his/her own FE on the workstation. Would network traffic be involved when the operator is tabbing from one field to the next?

[orange]

I wouldn't expect it to be network traffic when tabbing, but unless there's some code doing something strange I can't see why tabbing should be the cause of "slow".

[Xipooo]

Are you strictly using linked tables?
Are your forms bound to linked tables?
Do your queries join Access tables and SQL Server tables in the same query?
Are you seeing any other network traffic issues such as slow internet speeds?
Perform a ping to your server with /t and let it ping for a while. Tell us if you get anything nearing 100ms response time.

[EddieN1]

Are you strictly using linked tables? Yes...
Are your forms bound to linked tables? Yes...
Do your queries join Access tables and SQL Server tables in the same query? No...
Are you seeing any other network traffic issues such as slow internet speeds? Sometimes, but not always...
Perform a ping to your server with /t and let it ping for a while. Tell us if you get anything nearing 100ms response time. Nothing more than 1ms... however, note that this happens only sporadically... not consistently...

[EddieN1]

Just an update... had an operator report very slow process... closed down Internet Explorer, Outlook, and everything else. Task Manager showed very little activity. Access was still slow. Closed and re-opened Access and everything ran fast again. Emphasize Access 2010. Does anyone know of any patches from Microsoft that addresses this?

[Xipooo]

If your forms are bound to queries and tables that go against your SQL server then I'd say the network is probably the primary source of your woes.

The other part of this is that the ACE engine can be held up while it waits for a response from the SQL server, so Access will appear to lock up while it waits for a response. This is typical for non-asynchronous applications.

When the form is bound to a data source, switching between fields causes an update to the recordset which is propagated to your backend. If there is any network lag or the SQL server is simply too busy to handle your request then Access will just sit and wait until the connection either times out or it gets a response back from the server.

You could switch over to an "Async" strategy which would require some VBA to bind your forms instead of just setting the record source.

[EddieN1]

Here's more information on this... yesterday an operator reported slow responses. When I pinged the SQL Server 10 times, all responses were less than 1 ms. Closed and re-opened Access and database, still slow. Closed Outlook and everything was fast again. Re-opened Outlook and everything stayed fast. Could this be an Office 2010 issue??? say, memory leak? All Office apps are up to date. Other thoughts?

[rpeare]

I don't think it's related to office products, I restart my office machine maybe once every 2 - 3 weeks and don't have any appreciable slowdown over time regardless of process (outlook, excel, access primarily) on either my 2013 or 2007 machine. Have you got any antivirus or backup utilities working on the machines in question, or on the server side for that matter. I know you said your task manager isn't showing a spike in usage? Have you tried using unbound forms? It would be more coding for you but the transactions would be limited to specific events like an on click to force a save/update of a record rather than data having to be updated every time you exit a field/record.

[EddieN1]

This is one of those things that is quite inconsistent. It moves from operator to operator and starts and stops in a seemingly random manner.

[ItsMe]

Look for a common denominator. If the slowness on your network is only related to forms in Access or a few forms in a specific Access file, adjust the form's demand for RAM.

[EddieN1]

I'm still struggling with this and am still looking for some help. Here is an update... I haven't been able to find a common denominator yet, however, here's some more details. When it slows down, there are two messages near the bottom that toggle back and forth... on the bottom right, it sometimes says "Running Query" (although no query is running on SQL Server or anywhere else I have found), on the bottom left, it sometimes says "Calculating". Near the top it usually says "Not Responding". Note that the database is not is a "hard loop" because it always eventually comes back.

Is there an option, setting, or "hook" in Access that I can get to that will log internal events (not Form Events) that Access is taking that I could examine?

Thanks for any ideas anyone might have.

[ItsMe]

Perhaps how you connect to SQL Server and how you build and use your query objects within Access are the issue. This thread may help.
https://www.accessforums.net/sql-ser...low-44024.html