Comparing ways to get rowcount from a database table.

Just read this and found it very interesting.  The following are 2 queries to get rowcount on a database table:

Query 1 (the one everyone knows):

SELECT COUNT(RowID) AS [RowCount]

FROM elig_Inbox

 

Query 2 (the interesting one):

 

SELECT st.row_count AS [RowCount]

FROM sys.dm_db_partition_stats st

WHERE index_id < 2

and OBJECT_NAME(OBJECT_ID)='elig_Inbox'

 

What's really interesting to me is that once you look at the execution plan of the 2 queries, you'd find the improvement in performance.

 

 

 

In real life, we probably don't care about it as often as we should.  For example, both queries, executed against a table of 260k + rows, returned result within a second.

 

However, one drawback I can think of immediately, is that Query 2 does not have the Table Hints option.  Most of the time we do RowCount because we need some logic done and we need to lock the table while we do the count.  I guess the way around that would be to manually initiate a lock before Query 2, then release it after.  It will be interesting to see whether that puts the 2 query performance back on par.

Print | posted on Thursday, February 25, 2010 10:07 AM

Feedback

# re: Comparing ways to get rowcount from a database table.

Left by BigJim at 2/25/2010 11:41 AM
Gravatar One big drawback to this alternate approach. Not everyone has DBA rights. I tried running this under our normal data-access account and receive:

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski