What's the best way to count?

Question

Post reply

What's the best way to count?

Viewing 13 posts - 46 through 57 (of 57 total)

You must be logged in to reply to this topic. Login to reply

Alexander Kuznetsov SSCrazy Points: 2217 More actions · Answer 1

Alexander Kuznetsov

SSCrazy

Points: 2217

October 21, 2010 at 8:55 am

#1240381

del

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 2

Tom.Thomson (10/21/2010)
mtillman-921105 (10/19/2010)
kevin.l.williams (10/19/2010)
If I saw any production code like 2, 3 or 4, the developer would get an ear full. I will stick with count(*) thank you very much.
Maybe you're right for most everyday applications. I just tested SELECT COUNT(*) on a table with 5,900,000 rows and it was almost immediate. I think I'll stick with that too.
I think that I was being too hard on MS earlier since COUNT(*) is accurate, even if it can be slow in some circumstances.
COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT (or of course if you use HOLDLOCK).

For real? I'll have to look into that Tom. By the way, NULLs do count in a COUNT(*) - I did notice that. 😀

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Alexander Kuznetsov SSCrazy Points: 2217 More actions · Answer 3

mtillman-921105 (10/21/2010)
Tom.Thomson (10/21/2010)
mtillman-921105 (10/19/2010)
kevin.l.williams (10/19/2010)
If I saw any production code like 2, 3 or 4, the developer would get an ear full. I will stick with count(*) thank you very much.
Maybe you're right for most everyday applications. I just tested SELECT COUNT(*) on a table with 5,900,000 rows and it was almost immediate. I think I'll stick with that too.
I think that I was being too hard on MS earlier since COUNT(*) is accurate, even if it can be slow in some circumstances.
COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT (or of course if you use HOLDLOCK).
For real? I'll have to look into that Tom. By the way, NULLs do count in a COUNT(*) - I did notice that. 😀

I cannot completely agree.

In fact, COUNT(*) under REPEATABLE READ may return wrong results. I wrote a repro script here:

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 4

I think the answer is wrong on 2005. Look at the following:

select

sum(row_count)

from

sys.dm_db_partition_stats

where

object_id = object_id('dibs_tmb_saalist') and

(index_id = 0 or index_id = 1)

select count(*) from dibs_tmb_saalist

go

Produced:

(No column name)

310825

(No column name)

311992

That is a pretty significant error in my book. Query 1 is the only reliable method posted.

HTH -- Mark D Powell --

Note - above is corrected to include index_id line which reduced the error but did not eliminate it.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

You didn't include the restriction on the index_id in your query on the DMV, so that might be the issue.

Try this and see what you get:

select

sum(row_count)

from

sys.dm_db_partition_stats

where

object_id = object_id('dibs_tmb_saalist')

and index_id in (0,1)

jbwa SSC Eights! Points: 843 More actions · Answer 6

jbwa

SSC Eights!

Points: 843

October 28, 2010 at 10:51 am

#1243710

My reasoning also.

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 7

Query 1 is reliable while 4th one is the fastest...

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 8

>> I think I know why they can get away with it. It's because Oracle's even harder to use <<

No, I have found Oracle to be way easier to learn and use.

IMHO -- Mark D Powell --

SQLWinther SSCertifiable Points: 6294 More actions · Answer 9

You can read another discussion about row count at :

http://ask.sqlservercentral.com/questions/1980/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts?page=1#1983

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

zymos SSCommitted Points: 1960 More actions · Answer 10

Great question that perfectly helps demo on improving performance by use of already existing system statistics hence avoiding much strain and unnecessary use of system resources to gather the statistics.

Thank you.

Ganesh Kamal SSChasing Mays Points: 602 More actions · Answer 11

Is there any performance difference in the below given three queries.

Note: Col1 is NOT NULL column

SELECT COUNT(*) FROM Sales.SalesOrderDetail;

SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;

SELECT COUNT(0) FROM Sales.SalesOrderDetail;

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

gkganeshbe (12/26/2012)
Is there any performance difference in the below given three queries.
Note: Col1 is NOT NULL column
SELECT COUNT(*) FROM Sales.SalesOrderDetail;
SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;
SELECT COUNT(0) FROM Sales.SalesOrderDetail;

Not really. The second one with COUNT(Col1) may take a very small performance hit during the parse and bind phases, to check whether there is a Col1 column and if it's nullable, but that is an extremely short amount of time.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dave62 SSCertifiable Points: 7459 More actions · Answer 13

gkganeshbe (12/26/2012)
Is there any performance difference in the below given three queries.
Note: Col1 is NOT NULL column
SELECT COUNT(*) FROM Sales.SalesOrderDetail;
SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;
SELECT COUNT(0) FROM Sales.SalesOrderDetail;

Good question. I ran all 3 of these statements as a batch substituting SalesOrderID for Col1 and displayed the estimated execution plan. The estimated plan showed the cost of each query to be the same at 33%.

Just out of curiosity I then added Query 4 from the question and according to the estimated execution plan it is still the fastest. They all were equally accurate.