DAO RecordCount vs. Count(*) query

syswizard

Registered User.
Local time
Today, 10:08
Joined
Dec 27, 2008
Messages
61
I'm sure this has surfaced before. For small tables (<10,000 rows), which technique is faster, more efficient ?
 
I'd say test it, but my money is on Count(*).
 
Yeah, but then you have to open the query result to fetch the number into a return variable of a function. I know that DCount is likely inefficient, so that's why I asked.
If I open a recordset Readonly and ForwardOnly I would think that an EOF and then a return of the RECORDCOUNT would be blazing fast.
 
I would open a recordset on SQL that used Count. My recordset will pull 1 record from the backend, yours will pull all of them. That's why my money is on Count.
 
Thanks Paul, I cannot believe this has not been benchmarked.
Note: For Tables, MoveLast is not required...so it is lightening fast....just grab the RecordCount property. For queries, it's a bit slower.
 
In my experience, the MoveLast is required, at least to be sure of a correct count (I should specify that's DAO, which I normally use). A brief test confirms that. I get a count of 1 on a table with only 35 records in it without a MoveLast.
 
In my experience, the MoveLast is required, at least to be sure of a correct count (I should specify that's DAO, which I normally use). A brief test confirms that. I get a count of 1 on a table with only 35 records in it without a MoveLast.

You forgot to open it with the acTable option.
THAT'S THE KEY.
 
Not familiar with that one. I tried dbOpenTable, which does return the correct count, but will only work with a local table if memory serves. Or perhaps it's only Jet tables. I use methods that work more flexibly than that.

Have you benchmarked it?
 
When you use Count(*) in a query, the query engine can frequently use statistics to return the count without having to actually read a single row from the table. If you open a recordset and move to the end, you have to read every single row. So - there really is no contest. Jet and ACE are blazingly fast so you are going to need a pretty large recordset for a human to see the difference.
 
Indexes make a difference. Even a DCount on an indexed field is nearly instant.
 
Thanks much for that Pat....that's what I was looking for.
The database engine can use the stats to determine the count.
However, that's in the case of when there is no where clause.
Also, I wonder how smart it is when there are joined tables involved in a query ?
 
Once you include joins and criteria, indexes come into play. The engine may still be able to use statistics but probably not. Again, use count(*) rather than count(somefield) because the later always forces Access to actually read all selected records because it ignores null values of somefield when counting. Unless you know there will be null values and you want to ignore them count(*) is always more efficient.
 

Users who are viewing this thread

Back
Top Bottom