Access 2010 Performance

[EddieN1]

I don't think this is related to SQL server because there's no consistency in this and it affects users independently. One user's system might be slow and everyone else is fine. Then two days later, a user in a different department has a slow system and everyone else is fine. Also, there is a 5 second or so delay when tabbing between two fields on a Form and there is no I/O between the two controls. 10-20 minutes later, there is no delay when tabbing between the same two fields.



I have noticed that the Running Query and Calculating messages appear at the bottom, but I can't find any Queries running? Is there any way to "trap" what is happening when these messages appear?

[rpeare]

the 'running query' or 'calculating' messages can be triggered any number of ways

ON EXIT
ON CHANGE
ON CLICK
ON DOUBLE CLICK
AFTER UPDATE
BEFORE UPDATE

you really have to notice when those messages appear and if you were swapping from one control to another (i.e. tabbing to a new field) you would have to check the events that have to do with switching the focus from one control to another (for instance ON EXIT of the control you WERE on, or ON ENTER for the control you are ARE on).

Are you using bound controls/forms (bound forms/controls are linked directly to a data source whether it be a query or a table)?

If so you might be able to alleviate some of the problem by reorganizing to unbound forms.

Are all of your users using the same front end, or does each person have their on copy of your front end?

If everyone's using the same front end you may get better performance if everyone has their own copy of the database.

[EddieN1]

Does anyone know if there is a "hook" I can get access to whenever Access displays and/or clears the messages "Running Query" or "Calculating"? Perhaps if could intercept the process with VBA code and examine the query, formulas, form/subform, etc. to get more clues. I would even consider publishing a "temporary" FE database with extra code to accomplish this even if it adds overhead to the process. After I get this solved, I could remove the temp code. Thoughts?

[rpeare]

use debug.print in all your code the messages displayed in your immediate window of your VBA window will tell you how far your code is getting or what is being called.

For instance before you run a query you can have

debug.print "Before Running Query X"
docmd.openquery ("Query_X")
debug.print "After Running Query X"

if you examine your immediate window (I think it's ctrl-G or shift-G in your vba window to bring it up)

[EddieN1]

Still working on this... here's a question... when I open the Access Application and just sit on the main form, the Task Manager shows about 2,000 I/O Read Bytes every couple of seconds. No forms have any Timer Events so nothing should be running. Is there anything that Access fires up automatically to do some sort of internal clean-up tasks such as "garbage collecting"?

[rpeare]

Is the problem computer using the same version of Access as everyone else? When I open an older version of an access database in anything 2007 or higher it can be clunky at times, using linked tables and an outdated SQL driver may also be causing some slowdown (this is the easy stuff I can think of to check)

Do you get the same I/O rate on every computer, or just the problem computer?

[EddieN1]

It's not limited to just one computer. It seems to move randomly from computer to computer. Seems to last about 20-25 minutes, then goes back to fine. Different departments, floors, etc. One common element is that "Calculating" or "Running Query" is usually at the bottom, but I can't trap it to see what's running.

[EddieN1]

Okay, Team, here's some more information... all the users are running Windows 7 Pro w/ SP1, 64bit, 8 Gb Ram. I have read about performance issues with Win 7, such as Search Index, but not anything specifically with Win 7 and Access. The problem ACTS like a virus scan or a Search Index type process except for two things... 1. Nothing shows up as running in Task Manager when database goes slow and 2. It doesn't seem to affect anything else like IE, Outlook, Excel, etc. There was some mention of changing some VBA References, but nothing seems to apply. Also, the problem occurs only intermittently... usually several days between occurrences. Does this ring a bell with anyone?

[EddieN1]

I still need help on this... to summarize... O/S: Windows 7 Pro, Access 2010 (Front Ends are on Users' Workstations), Database: SQL Server 2012. Drastic slow downs (3-8 seconds between keystrokes) occur apparently at random (although we know this isn't the case) about once every 3-5 days and lasts about 20 minutes. (I know it's not in an infinite loop, because it always eventually comes back to normal.) Affects only one user at a time. Task Manager shows 95% Idle. Other Office Applications respond normally, even Internet Explorer and Outlook. Status messages: Not Responding at the top. Bottom messages toggle between "Running Query" and "Calculating". I installed a routine that, every 60 seconds, records all SQL Server queries that are running and nothing stands out.

Does anyone know if there is a way I can trap and examine what's going on within Access whenever the status messages "Calculating" and "Running Query" appear?

Any thoughts and suggestions are welcome...

[rpeare]

if you see calculating or running query at the bottom of your access window that is purely an access function doesn't have anything to do with what's running on the server side. Are both the front end and back end of the database converted to the 2007 format (as .accdb as opposed to .mdb)? Are you sure there's no code in the database that's running on a timer? I don't think you ever said if you wrote this database or someone else did. If it's running something in the background on it's own (on a timer) it would account for some of this behavior.

You can trap everything as it runs with debug.print commands in all the code. then just open the immediate window (I think it's still ctrl g in the vba window) just debug.print "in module x" or any message that will help you find where the problem is, if it slows down in the same spot every time you can try to find a way to rework your code.

[EddieN1]

I do have a few On Timer events, however, I have added audit trails to record when they start and stop and none of this is causing this issue? Also, remember it appears to happen randomly... not on the same form, control, etc.

[rpeare]

my point is (and has been) that if you see the notice you are saying you're getting in your system tray (running query or calculating) something internal to access is running but you have said repeatedly you don't know what is. the debug.print will allow you to figure out what that is, as in if it is always slowing down on running a specific procedure/sub routine/module you can catch it with the debug.print statements.

I am not a fan of 'on timer' events but as long as they are short term (no more than 10 - 15 seconds) I don't see how they could affect your database's performance.

[PAHTDC]

I have somewhat similar symptoms but under different circumstances. Windows7, Access 2013.

I restart the computer, open Access and create a new blank database and new simple (2 column) table with some dummy entries (e.g. 2 rows). Create a simple select query against that table and the resulting time to generate the data sheet appears to randomly vary between instantaneous (as expected) and 5-20-30-45-or more seconds. Access stays stuck at 25% CPU usage, with no memory usage growth while its stuck in the 'not responding' loop (spikes about 10 MB of memory usage while its trying to render).

Similar to the OP's description, Access ultimately does always render the datasheet view.

Tearing my hair out trying to figure it out too - does not seem to randomly disappear like it does for you and this is just a non-split blank desktop database sitting on standard desktop computer.

I recognize different versions and likely not related, but maybe when you get a user into one of those extended 'slow' periods, try getting them to create a blank database and see if similar symptoms get reproduced?

[EddieN1]

PAHTDC, Great, thanks. Here's something else I noticed recently. If I open any Module, even a new, empty one, then while the query is "Running", there is a status message at the top of the Module stating "running Module1 Code" or similar. If I hit ESC, the code stops. Does yours do this as well?

[PAHTDC]

PAHTDC, Great, thanks. Here's something else I noticed recently. If I open any Module, even a new, empty one, then while the query is "Running", there is a status message at the top of the Module stating "running Module1 Code" or similar. If I hit ESC, the code stops. Does yours do this as well?

Hi - I don't see the same status message. My VBA screen just locks up and has the spinning circle when the mouse is over it. If i click on the window the status bar at the top adds "(not responding)".

I've tried rolling back updates, installing from click-to-run, from CD, turning off virus protection, and nothing has a systematic effect. And yet sometimes it will give instanteous response, and others upwards of 2 minutes (but always does respond).

Very frustrating - do post here if you ultimately do find something, even if its months down the road!