MS Access database runs slower on SSD than on Spinning disc

[Pat Hartman]

And one more test. Put the db on a thumb drive or a portable hard drive and compare the speed.

 

[The_Doc_Man]

I have got this

View attachment 120026

Memory is at 14%, therefore you are NOWHERE near saturation of memory AND at that level, I guarantee you aren't swapping or paging anything. I.e. dynamic memory is not actively load-balancing. In fact, with that small memory load, you probably have only Access itself, an anti-virus, and Windows Task Manager active.

CPU load is at 6%, so you are nowhere near saturating THAT, though it might help to know the CPU load in greater detail. Did you tell us WHICH i7 processor you have? The i7 line of chips has been multi-CPU for years now, but later i7s have split-speed CPUs. That's why I earlier asked if you had been playing around with CPU Affinity. But, you said NO on that. It is still worth knowing what i7 chip you have.

The disk is showing no particular load, zero read usage, and fast write-back speed, though it is taking a hit every 5 seconds. That could be ordinary logging, though there IS a possible resource hog to consider. Does your system have Copilot active? If so,, do you know if you have the RECALL feature active? That might cause a regular periodic load, though the timing doesn't seem right. (RECALL isn't a once-per-5-seconds feature.)

The GPU is not doing much, though when Access is involved, advanced graphics card loads are not expected anyway. Unless you have programmed something using complex graphics API code - and I'm betting against that. You would know if you had done such a thing.

You have a slight level of activity on the Ethernet connection, but since you explained that the app is all on the same machine, the network activity is something probably unrelated to Access. It is probably very low network action, scaled precisely BECAUSE it is so low.

That covers all of the system issues that are the first places to look for bottlenecks. When doing system load analysis, you always look for network issues, then disk problems, then memory problems, then CPU/GPU problems (in that order), but you've got nothing that would even give me a case of hiccups as far as system loads go.

This means that we need to decide what is going on with the application when it is running, 'cause your problem ain't due to a bum system. You said earlier that reports were reasonably fast, but forms were troublesome. What are the forms doing?

Another question, still related to forms: What is the nature of the forms' .RecordSource properties? Do you directly reference a table? Do you have a query that drives the form? Do you have a literal SQL query as a .RecordSource? Are you using a lot of multi-value fields (MVFs)? Is there a very large table involved? Is there a JOIN involved? Do you have a lot of combo or list boxes active?

At the moment, I don't see anything that would trigger an obvious slowdown, though a problem within Access or your app could surely go down that path. It may sound like a negative result, but look at it this way. We still might not know what IS wrong, but we know it isn't a Windows or system hardware problem per se. It has to be in the version of Access, the libraries, or something being done in the forms. So there is at least some elimination of places to look.

 

[cheekybuddha]

Have you noticed whether the slowdown happens when you are performing particular actions in your db, eg copying records?

This bug doesn't yet seem to have been fixed, though it suggests a workaround

 

[jeffreylewis]

And one more test. Put the db on a thumb drive or a portable hard drive and compare the speed.

Did not make any difference except slightly slower

 

[jeffreylewis]

Memory is at 14%, therefore you are NOWHERE near saturation of memory AND at that level, I guarantee you aren't swapping or paging anything. I.e. dynamic memory is not actively load-balancing. In fact, with that small memory load, you probably have only Access itself, an anti-virus, and Windows Task Manager active.

CPU load is at 6%, so you are nowhere near saturating THAT, though it might help to know the CPU load in greater detail. Did you tell us WHICH i7 processor you have? The i7 line of chips has been multi-CPU for years now, but later i7s have split-speed CPUs. That's why I earlier asked if you had been playing around with CPU Affinity. But, you said NO on that. It is still worth knowing what i7 chip you have.

The disk is showing no particular load, zero read usage, and fast write-back speed, though it is taking a hit every 5 seconds. That could be ordinary logging, though there IS a possible resource hog to consider. Does your system have Copilot active? If so,, do you know if you have the RECALL feature active? That might cause a regular periodic load, though the timing doesn't seem right. (RECALL isn't a once-per-5-seconds feature.)

The GPU is not doing much, though when Access is involved, advanced graphics card loads are not expected anyway. Unless you have programmed something using complex graphics API code - and I'm betting against that. You would know if you had done such a thing.

You have a slight level of activity on the Ethernet connection, but since you explained that the app is all on the same machine, the network activity is something probably unrelated to Access. It is probably very low network action, scaled precisely BECAUSE it is so low.

That covers all of the system issues that are the first places to look for bottlenecks. When doing system load analysis, you always look for network issues, then disk problems, then memory problems, then CPU/GPU problems (in that order), but you've got nothing that would even give me a case of hiccups as far as system loads go.

This means that we need to decide what is going on with the application when it is running, 'cause your problem ain't due to a bum system. You said earlier that reports were reasonably fast, but forms were troublesome. What are the forms doing?

Another question, still related to forms: What is the nature of the forms' .RecordSource properties? Do you directly reference a table? Do you have a query that drives the form? Do you have a literal SQL query as a .RecordSource? Are you using a lot of multi-value fields (MVFs)? Is there a very large table involved? Is there a JOIN involved? Do you have a lot of combo or list boxes active?

At the moment, I don't see anything that would trigger an obvious slowdown, though a problem within Access or your app could surely go down that path. It may sound like a negative result, but look at it this way. We still might not know what IS wrong, but we know it isn't a Windows or system hardware problem per se. It has to be in the version of Access, the libraries, or something being done in the forms. So there is at least some elimination of places to look.

Hi The record source is a table not a query.
The processor details for both machine are on the image below

Thanks

 

[AHeyne]

Can you try (with a copy of your database/environment) to create a minimally reproducible environment by deleting more and more until only essential objects really remain in the database to be able to reproduce the problem? In the best case, perhaps only one table and one form that are causing the problem remain, so that you can look there specifically (e.g. by exporting to text files).

 

[The_Doc_Man]

Hi The record source is a table not a query.

The Xeon X5680 (old machine) is a 6-core (12-thread) chip with a base speed of 3.33 GHz. The Intel i7-7700 (new machine) is a 4-core (8-thread) chip with a base speed of 3.60 GHz. That makes the new machine a little less than 10% faster. In theory, an Access app uses no more than two threads at a time - one for the GUI and one for the JET or ACE engine (probably ACE) that runs the SQL while the GUI runs VBA and displays and report generation. Therefore, you should be at about the same performance. For most Access forms, a 10% load-speed difference SHOULD be negligible, but apparently not for your case.

If your Access app doesn't require full-screen operation, you can start the Task Manager in less than full screen mode and view both WTM and your GUI. Even though it is also in use, you will never see ACE in a window because it is ALWAYS counted as a background task.

Earlier you showed us the DISK page of Windows Task Manager, but there are a couple of things you can do with WTM - simple tests - to see what is going on.

Look at the PROCESSES display and click in the CPU column header. That toggles between ascending and descending sort of CPU usage. When I click on my own system with the i7-14700 and show (sorted descending) processes, I have 14 active threads with non-zero CPU% usage. None of them take up a ton of time, but what you NEED to see is that "processes" display when your form is trying to do something that seems so slow. That will give you a view into what is actually active at the time. It would show you if there was contention.

The OTHER thing to consider is that you can also switch to the CPU load screen. Since you have a multi-thread system, you can RIGHT-click on the space above the CPU graph to switch between individual-thread graphs or summary graphs (all threads rolled up into one). With the CPU performance per-thread graphs visible, if you trigger opening your sluggish form, you would able to see if any CPU suddenly becomes saturated to 100%. IF that happens, then whatever is going on, you will never get any faster, because the Access GUI runs single-threaded. It does you no good to have 8 threads - or 28 threads like on my gaming beast. A single-threaded process will go no faster than the CPU to which it is assigned, and if you saturate the CPU, you are going to have to find another way to do what you are doing, 'cause you can't tune saturation.

Having said that, there are things you can do that change the rules slightly. IF you can create a query for that table and the query contains all of the fields needed for the form, believe it or not there is sometimes a difference in performance. Regardless of sorting or reformatting or just straight-up echoing all of the fields in the table, a form's behavior sometimes improves when it is based on a query. I think it has to do with the implied connection persistence because of how queries work, but that IS a guess. It could also be because the stored query will implicitly have a stored query plan, created behind-the-scenes when you created the query. If it isn't too hard to try, do that to see if it makes a difference.