DAO RecordCount vs. Count(*) query

[syswizard]

I'm sure this has surfaced before. For small tables (<10,000 rows), which technique is faster, more efficient ?

 

[pbaldy]

I'd say test it, but my money is on Count(*).

 

[syswizard]

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.

 

[pbaldy]

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.

 

[syswizard]

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.

 

[pbaldy]

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.

 

[syswizard]

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.

 

[pbaldy]

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?

 

[Galaxiom]

Indexes make a difference. Even a DCount on an indexed field is nearly instant.

 

[syswizard]

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 ?