I’ve recently been working on doing some serious code cleanup in a client database, and also wanted to try and do some optimization when possible.

Premise

One thing I’ve been curious about was getting record count. The database that I’m working on, the previous developer would check the record count before opening any form or report, so it is checked constantly. Now moving beyond to horrific setup of checking things in this manner rather than, in the case of reports, simply using the No Data event (for instance), it got me thinking a bit about what is the best way to get a record count.

So I put out to test a couple different approaches to getting a Record Count for a recordset to see which was optimal.

Methods

Below are 2 major technique with a total of 5 different variations.  What I did is I create a few procedures to perform a thousand iterations over each technique to log the execution time and then created a simple query to gathered an average value to compare with.

Method 1 – SELECT *

The existing developer would used code similar to

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT * FROM TableName;")
rs.MoveLast
lRecCount = rs.RecordCount
rs.Close
Set rs = Nothing

Method 2 – DCount(*)

Now I was curious how this would compare to using DCount()

Dim lRecCount             As Long
lRecCount = DCount("*", "TableName")

Method 3 – SELECT pk

Then this got me wondering how each of these perform when running on a single fields.

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT [pkFieldName] FROM TableName;")
rs.MoveLast
lRecCount = rs.RecordCount
rs.Close
Set rs = Nothing

Method 4 – DCount(pk)

Dim lRecCount             As Long
lRecCount = DCount("[pkFieldName]", "TableName")

Comparing .OpenRecordset(“SELECT * …. vs. DCount(“*” …., DCount was the clear winner. Switching DCount to use the PK field instead of * negatively impacted performance but it was still slightly faster than the OpenRecordSet technique. But only working with the PK field with the OpenRecordset technique (.OpenRecordset(“SELECT [pkFieldName] FROM …) drastically improved performance to the point that it was noticeably faster than any other technique.

Method 5 – SELECT Count(pk)

Out of personal curiosity, I decided to quickly test using the Count() in a SQL Statement to see how it fit into the grand scheme of thing using something along the lines of

Dim rs                    As DAO.Recordset
Dim lRecCount             As Long
Set rs = db.OpenRecordset("SELECT Count([pkFieldName]) AS RecCount FROM TableName;")
lRecCount = rs![RecCount]
rs.Close
Set rs = Nothing

This approach performed very similarily to that of DCount, only slightly edging out DCount.

Results

Here are the detailed results of my findings.

	Execution Time (ms)	Delta w/ Method 3 (ms)	% Decrease in Performance
Method 1 – SELECT *	14.37	8.08	-78%
Method 4 – DCount(pk)	13.1	6.81	-70%
Method 2 – DCount(*)	9.7	3.41	-43%
Method 5 – SELECT Count(pk)	9.09	2.8	-36%
Method 3 – SELECT pk	6.29	–	–

In conclusion, based on my testing, the following (Method 3 – SELECT pk) was the fastest when working on MS Access Linked tables.

Let’s Further the Discussion

Further considerations to extend this discussion. My tests were performed on a relatively small table (5k records) with 163 fields (a mess of a table that I’m working on cleaning up), so it would be very interesting to perform the same tests on a table with a million or more records to compare and a properly normalized table too.

Update 2016-02-25

Okay, so I wanted to further the discussion (simple personal curiosity) and see for myself how the above stood up to larger recordsets. So I downloaded George Hepworth’s Test Bed database (300k records) and set out to run the same set of tests as above. Here were my results

	Execution Time (ms)	Delta w/ Method 5 (ms)	% Decrease in Performance
Method 1 – SELECT *	21.77	15.61	-112%
Method 3 – SELECT pk	20.88	14.72	-109%
Method 4 – DCount(pk)	6.51	0.35	-6%
Method 2 – DCount(*)	6.46	0.3	-5%
Method 5 – SELECT Count(pk)	6.16	–	–

 

Conclusions

It is pretty obvious from the data above that Method 1 is to be avoided at all cost (this was what the previous developer used throughout the db I am working on, so I have some work ahead of me to clean it all up), but beyond that the interpretation become more tricky.

One could say:

• for small recordsets use Method 3 – SELECT pk
• for larger recordsets use Method 5 – SELECT Count(pk)

It does make perfect sense that Method 3’s performance would be based upon the size of the recordset as it has to travers the entire set (.MoveLast) to get an accurate count.  Thus, the larger the recordset the bigger the performance hit.

Then again, someone might split the difference and simply say use Method 5 for all situations as the difference is negligible.

Let’s Further the Discussion Even More

What’s left to discuss you might ask?  Well, we could push things further and:

• explore how each technique reacts when applying filters to restrict the count.
• explore how these technique behave through ODBC

but this will have to wait for another moment.