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

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

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

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

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.

<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!

Hi,

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

Hope this helps,
Grofaty

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).

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! =)