Skip to main content
C
christopherkowalski
Inspiring
March 26, 2009
Question

count(id) vs. query.recordcount

  • March 26, 2009
  • 1 reply
  • 762 views
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?
    This topic has been closed for replies.

    1 reply

    N
    Newsgroup_User
    Inspiring
    March 26, 2009
    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/
    A
    Anonymous
    March 26, 2009
    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.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices