Performance differences counting records in a Microsoft Access Database

by Philipp Stiefel, originally published May 6th, 2016

I just stumbled upon a blog post of Microsoft Access MVP Daniel Pineault about different approaches to count the records in a table/query. In the process of optimizing a database created by another developer, he came across the following code to count records.

Daniel than compared the above code to variations of using the DCount-Function and a SELECT-COUNT()-Recordset. Here are his results:

Surprising details

I was surprised by those results for two reasons.

1. I would have expected a massive difference between the DCount approach and using a SELECT-COUNT()-Recordset in favor of the Recordset. Back in the days of Access 97 I wrote my own functions to replace the built-in Domain Aggregate Functions because those were slow, resource hogging and sometimes even failed with a “Cannot open any more tables”-Error if called in a loop or for each record in a query.
   
2. I did not expect any significant difference in performance between the DCount(pk) and DCount(*) approach. I would have thought they were processed exactly the same way internally.

With a clear difference there showing in Daniel’s result, I was curious if there would have been a similar difference between SELECT-COUNT(pk) and SELECT-COUNT(*). Unfortunately this was not included in the results.

So I felt the need to do a performance test myself.

My performance test

I used Microsoft Access 2013 for my test and the Northwind database template included with Access 2013. To get a decent table size I created a Cartesian query including the Customers table 4 times. Then I appended the results of that query to the existing customer data, thus creating 707,310 records in the Customers table.

Then I wrote a couple of simple functions for my performance test. Here they are:

With this code in place, I ran all the test functions by using the TimedExecute method like this.

First I ran the test with the database on my local disk (SSD RAID). – The results were useless. Flat 8 milliseconds for each of the functions. – No difference at all. Zero. None.

But with a real world database, you should always consider your backend database being accessed over the network. - That’s where you sift the wheat from the chaff. – So I moved the backend to a network share (accessed via 100Mbit Ethernet) and re-ran my test procs.

Now we’re talking. There are quite noticeable performance differences between the approaches. Here are the results in detail.

While the tabular results are pretty comprehensible already, here is a visual chart representation as well.

Conclusion

These results are absolutely in line with Daniel’s findings. There is only a tiny difference between the DCount(*)-approach and SELECT COUNT(pk). I guess Microsoft has made significant performance improvements to the DCount-Function since the days of Access 97. Hence we are not seeing the massive differences I would have expected.

However, the first interesting comparison is between DCount(*)  and SELECT COUNT(*). As you can see, DCount(*) is 52% slower than the Recordset approach. That is a very clear win for the Recordset! J

But let’s keep this in perspective here. While there is a clear performance advantage to the Recordset, it still is only 11 milliseconds faster. If you are using a DCount here and there and not calling DCount hundreds or thousands of times in a loop, I suggest you continue to use whatever you like best.

My results confirm the performance difference between SELECT COUNT(pk) and SELECT COUNT(*). I still am curious why this difference exists and I still have no explanation for it.

We could find out by using the JetShowPlan Registry Setting to force the Jet/ACE-Engine to expose its execution plans of these queries.  – But that will have to wait for another day.

Share this article: