How to find top cpu utilized queries in MS Access database?

Question

I want to find those queries in MS Access which are utilizing the CPU mostly and put them in a table in descending order.

I have checked the system tables of MS Access database, but can't find any clue for this.

I am new to MS Access, please help.

Answer 1

For 10 or even 15 years, Access has never been CPU bound. In other words, network speeds, disk drive speeds etc. are the main factor .

In the vast majority of cases throwing more CPU at a problem will not help improve performance. If 99% of time is network or other factors, then a double of CPU will only improve by 2%.

However, I will accept that if a query is using lots of CPU, then it stands somewhat to reason that such a query is pulling a lot of data. There is no CPU logger for the Access database engine. However, you can look at rows and the query plan, and that can be done with showplan. How this works and can be used is outlined here:

How to get query plans (showplan.out) from Access 2010?

And here is a older article on showplan and how to use it: https://www.techrepublic.com/article/use-microsoft-jets-showplan-to-write-more-efficient-queries/#

So, showplan is somewhat similar to looking at the query plan used in SQL server. It will tell you things like if a full table scan is being used to get one row, or if indexing can or was used. So, looking at the query plan, be it sql server, or in this case Access is certainly possible. However, the status on CPU usage are slim, but how much data and things like if the query plan is doing full table scans is available in a similar fashion to query plans like one would see for server based systems such as SQL server.