count(id) vs. query.recordcount

[christopherkowalski]

I've coded queries both ways over the years, and never noticed problems either way, but I was coding today and it made me question: When I need to query to see how many records match my criteria I could do a count(id) on the table, or just query for the id and check the recordcount; but is one less taxing on the database than the other?

I'm assuming the count(id) method is. Am I correct?

[Newsgroup_User]

on the db level the difference, if any, is insignificant - databases are
internally optimized to perform aggregations and counts fast.
what is more important is the data the db sends back to cf - a large
recordset that is used only for query.recordcount is a lot of [useless]
data; one row containing count(id) value is far more efficient...
but it is not very often (at least in my practise) that one needs only
the recordcount from a query - usually the full recordset is also
needed, for output or other purposes...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Kapitaine]

If you are going to use all sorts of other data from the same query results IN ADDITION to using the recordcount, then query.recordCount is fine.

However, if all you need is the count of how many rows, then use the COUNT() function in your SQL.

Remember that databases are designed for this very purpose - to manipulate and calculate data. Fetching an entire recordset JUST to output the count is insanity and gives more for CF to do.

COUNT() will be much faster. You may not notice this on smaller sets, but if your queries end up returning thousands and thousands of rows, you'll soon see that COUNT() is more useful.

Mikey.