How To Get Table Row Counts Quickly And Painlessly

Question

Post reply

How To Get Table Row Counts Quickly And Painlessly

Viewing 15 posts - 46 through 60 (of 108 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Sandesh (9/3/2009)
Guys,
How about the below queries which use only 1 table or view to get the required info.

It might pay you to read back a few posts :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sandesh Segu SSC-Addicted Points: 415 More actions · Answer 2

anything wrong??? 🙁

Regards,
Sandesh Segu
http://www.SansSQL.com

Paul White SSC Guru Points: 150467 More actions · Answer 3

Sandesh (9/3/2009)
anything wrong??? 🙁

Not wrong as such, just not new.

We've kinda been down that track quite a bit in this thread already, you see?

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rja.carnegie SSC Eights! Points: 923 More actions · Answer 4

TheSQLGuru (9/3/2009)
I expect there to be lots of 'messes' out there related to MERGE and triggers. So incredibly difficult to follow some of the logic trees when both are involved!

About how long can I remain employable on SQL Server 2005, do you think? :crying:

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

rja.carnegie (9/3/2009)
TheSQLGuru (9/3/2009)
I expect there to be lots of 'messes' out there related to MERGE and triggers. So incredibly difficult to follow some of the logic trees when both are involved!
About how long can I remain employable on SQL Server 2005, do you think? :crying:

Since I don't know you, I cannot speak to whether or not you are employable on SQL Server 2005 NOW, much less in the future! 😀

Seriously though, there will be plenty of SQL 2005 around for many years to come. Almost half of my clients are still on SQL 2000 as their primary database engine!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 6

Yeah, we've still got one 2000 box...and all this at a time when most are 2005, a small number are 2008, and we're busy evaluating the latest R2 CTP :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

randall.c.newcomb Right there with Babe Points: 715 More actions · Answer 7

I use a method similar to the one described in the article. The procedure runs each night during a quiet time on the server. It writes the information to a table and allows me to monitor trends in data growth. I link Excel to the table to generate a graph of the trend. This makes it much easier for the business to understand the importance of data retention policies.

- Randall Newcomb

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

A well written article, but it does say

First, a table scan is required to figure out the answer;

. Which is not true.

I did a little testing. If there is a clustered index, it will do a clustered index scan. If there is both a clustered and a nonclustered index, it will normally prefer the nonclustered index, which is more efficient for straight counts due to the way it is stored.

Though I doubt it will come up often in practice, if you truly needed a lot of speed in getting that type of count on a regular basis, you can create an indexed view. The indexed view will have to use count_big(*) instead of count(*), but for truly large tables it is the by far fastest option I tested.

Of course, if using standard version, you will also need to use the NOEXPAND query hint. (Some details at : http://www.sqlservercentral.com/articles/Indexed+Views/63963/ ) Also note that if you try to do the testing on a machine with developer edition or enterprise edition, once you create the indexed view, doing a count(*) on the table directly will then use the index on that view.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

randall.c.newcomb Right there with Babe Points: 715 More actions · Answer 9

On my SQL 2000 system the SP_SpaceUsed method was considerably faster than the select Count(*) method. The system was not idle at the time, but the numbers are certainly directional.

I chose a 66M row (112GB) table that has both a clustered and non-clustered index. Display Estimated Execution Plan showed that it was going to use the non-clustered index.

-- select count(*) using the non-clustered index ran in 43 seconds.

-- select count(*) method using the clustered index did not complete even after 22 minutes and no other processes were blocking it

-- SP_SpaceUsed method ran in under 1 second

YMMV

- Randall Newcomb

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

the sp_spaceused issue isn't with performance it is with getting the wrong answer.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Paul White SSC Guru Points: 150467 More actions · Answer 11

Timothy & Randall,

You'll find the very fastest no-overhead method a few posts back (here).

Also:

1. It is true that, in the current versions of SQL Server, the QO will often prefer to scan a NC index to compute COUNT(*), but that is by no means always the case. A clustered index scan will be preferred over a scan of an NC index with a suitably low FILLFACTOR, if it results in fewer page accesses. The degree of fragmentation of the NC index can also result in it being slower in practice that either a clustered index scan, or an IAM-based scan (which a clustered index scan can be, if NOLOCK or TABLOCK are specified). An IAM-based scan of a heap can also out-perform an NC index scan in similar circumstances.

2. An indexed view will be fast - but COUNT(*) has to be performed at least once to build the view, and every data modification operation on the base table will have extra operators added to the plan to maintain the count. The fact that the index maintenance part of the plan runs internally at the SERIALIZABLE isolation level can have unfortunate side-effects too.

I know you guys probably appreciate all that (hence the caveats in your posts), I just wanted to expand on the reasoning 🙂

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

TheSQLGuru (9/3/2009)
the sp_spaceused issue isn't with performance it is with getting the wrong answer.

Yes - although I would concede that 2005 and later are considerably better in that respect than 2000 and earlier were!

The other thing about sp_spaceused is that it requires an ugly INSERT...EXEC to manipulate the returned value in code.

Yuk.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 13

Use sp_statistics and refer to cardinality column for rou count.

Jeff Moden SSC Guru Points: 1004224 More actions · Answer 14

Good tip... but because it get's it's info from the same place as sp_SpaceUsed, it also has the same problems as sp_SpaceUsed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sanjay Rohra Ten Centuries Points: 1158 More actions · Answer 15

I have not faced any issues with sp_statistics as far as comparing the results with SELECT COUNT(*) as I do keep on comparing both the results (for my curiosity).