Results 1 to 12 of 12

Thread: counting rows

  1. #1
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21

    Lightbulb Unanswered: counting rows

    anyone can contribute his/her 2 cents...

    i believe these 2 sql statement will produce the same result, however, which is better in terms of performance:

    select count(col1)
    from table
    where col2 = :ws-col2
    and col3 = :ws-col3

    or

    select count(*)
    from table
    where col2 = :ws-col2
    and col3 = :ws-col3

  2. #2
    Join Date
    Feb 2002
    Posts
    96
    Optimized(rewritten) statement shows same sql for both queries when i do the explain. So it shows both will give you the same performance.

    Regards
    Prakash

  3. #3
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21
    i did just the same and was wondering if there are any difference at all.
    Thanks very much!

  4. #4
    Join Date
    Jan 2004
    Posts
    3
    Originally posted by starra
    i did just the same and was wondering if there are any difference at all.
    Thanks very much!
    I feel the first one is more optimized version....
    (While i working in a project where DB2 is used extensively, it a standard to write count(column1) to count the number of records).


    Thanks
    Sudhakar

  5. #5
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21
    Originally posted by sudhakarkreddy
    I feel the first one is more optimized version....
    (While i working in a project where DB2 is used extensively, it a standard to write count(column1) to count the number of records).


    Thanks
    Sudhakar

    thanks! you're right! it is usually suggested that the column be written instead of using "*"

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I think the suggestion to use the column name instead of * applies to "select *" and not "select count(*)". I don't think that “select count(*)” is any worse performance, it just tells DB2 to count the number of rows.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: counting rows

    <snip>
    i believe these 2 sql statement will produce the same result...
    </snip>

    The fact that you have the same access path doesn't mean that you get the same result!!! If col1 allows nulls, and you have some entities with col1 = null, you WILL get different results.

    HTH,
    Rodney Krick

  8. #8
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21

    Re: counting rows

    Originally posted by RKrick
    <snip>
    i believe these 2 sql statement will produce the same result...
    </snip>

    The fact that you have the same access path doesn't mean that you get the same result!!! If col1 allows nulls, and you have some entities with col1 = null, you WILL get different results.

    HTH,
    thanks for pointing that out. the column i was trying to test does not allow nulls. I just wanna know the difference on performance perspective. thanks!

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Please read article about "Select count(*)..." performance.

    Hope this helps,
    Grofaty

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The article mentioned above refers to determining existence, not for determining a count when a count is needed. These are two different things.

    I don’t believe that the author compared using count(*) with count(col1), which are the options under discussion in this thread.

    Also note that the test was done on DB2 for OS/390 version 6. In any event, even for determining existence, it does not appear that the results for tests run in the article are significantly different if the same access path is used (index vs. table space scan).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    My Explain shows that both them gives the same result. i.e. both of them uses the index and does NOT result in a tablespace scan.

    So what I think is that whenever DB2 sees a COUNT(*) and if there is a clustered unique index for the table, DB2 just uses that index column to do a COUNT. Internally DB2 converts the SQL as COUNT(COL1).
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  12. #12
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21
    Originally posted by gsreejith
    My Explain shows that both them gives the same result. i.e. both of them uses the index and does NOT result in a tablespace scan.

    So what I think is that whenever DB2 sees a COUNT(*) and if there is a clustered unique index for the table, DB2 just uses that index column to do a COUNT. Internally DB2 converts the SQL as COUNT(COL1).
    Thanks for your input. I really appreciate it! =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •