Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

What's the best way to count? Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 3:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 5,930, Visits: 8,180
vk-kirov (10/19/2010)
Hugo Kornelis (10/19/2010)
The same page in Books Online also contains this warning: "Partitioned tables and indexes are not fully supported in this view; use the sys.indexes catalog view instead." ... Since method 2 does not work for partitioned tables, it's not reliable.

The 2nd query works just fine for partitioned tables
That BOL page states what columns are not supported in case of partitioned tables: first, root, groupid, dpages, reserved, used, rowcnt, rowmodctr, and FirstAIM. The column 'rows' is not in this list, and it's supported.

I missed that - I stopped reading after seeing that quote. Thanks, vk-kirov!
With this additional information, I'd argue that options 2 and 4 are both correct. The unreliability of option 2 stopped after SQL 2000 (and the question specifically mentions SQL 2005 and later), and being deprecated was nog mentioned as a deciding factor in the question.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1006806
Posted Tuesday, October 19, 2010 3:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 13,357, Visits: 10,222
Option 1 isn't that reliable. For really really large tables (e.g. number of records > 2^31-1) it will produce an error. To be 100% sure, COUNT_BIG should have been used.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1006808
Posted Tuesday, October 19, 2010 3:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 13,357, Visits: 10,222
Oh yeah I forgot: great question!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1006809
Posted Tuesday, October 19, 2010 3:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
One more challenging question, thank you Dave.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1006819
Posted Tuesday, October 19, 2010 4:03 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Hugo Kornelis (10/19/2010)
With this additional information, I'd argue that options 2 and 4 are both correct.

And what do you think about counting uncommitted records, is it such a reliable thing?
Post #1006823
Posted Tuesday, October 19, 2010 4:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 5,930, Visits: 8,180
vk-kirov (10/19/2010)
Hugo Kornelis (10/19/2010)
With this additional information, I'd argue that options 2 and 4 are both correct.

And what do you think about counting uncommitted records, is it such a reliable thing?

Depends.
If you want to count order detail lines, none of the queries is good enough. (See below)
If you want to count rows, I'd argue that you WANT uncommitted rows as well. Counting rows is soemthing a DBA does, to estimate space needed. Uncommitted rows take space as well. Counting order detail lines is more a business function; uncommitted rows should not be counted there.

If you want total accuracy, you either have to use one of the snapshot isolation level, or you need to take an explicit table lock. Under RAD_COMMITTED, you can still get incorrect results. For example:
In query window #1, run:
CREATE TABLE dbo.QOTD_TEST (ID INT PRIMARY KEY);

INSERT dbo.QOTD_TEST VALUES(1);
INSERT dbo.QOTD_TEST VALUES(2);
INSERT dbo.QOTD_TEST VALUES(3);

BEGIN TRAN;
INSERT dbo.QOTD_TEST VALUES(4);
INSERT dbo.QOTD_TEST VALUES(5);

In query window #2, run:
SELECT COUNT(*) FROM dbo.QOTD_TEST;

Execution will start, but not finish as it waits for locks from window #1 to be released.

Now go back to window #1 and run:
DELETE dbo.QOTD_TEST WHERE ID IN (2, 3, 4);
COMMIT TRAN;

The query in window #2 will now finish and show an incorrect result: 4. When the SELECT COUNT query started, there were 3 committed and 2 uncommitted rows; when it ended, there were 2 rows. This answer is arguably even less correct than the numbers returned by the other queries!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1006843
Posted Tuesday, October 19, 2010 4:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
Thanks for this one - lots of stuff I didn't know there.

Does anyone know if there's a similar fast count method for views? I have a health check procedure which counts the number of records in a variety of tables and views (to check data load success and ensure data is there for the next stage in the process). The table counts are fine (biggest table has around a million records), but the views are terribly complex and counting them with SELECT COUNT(*)... takes ages.

Cheers,
Dave.
Post #1006851
Posted Tuesday, October 19, 2010 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:40 PM
Points: 5,930, Visits: 8,180
dave.farmer (10/19/2010)
Does anyone know if there's a similar fast count method for views?

Only if they are indexed. Or if the relationships between the underlying tables are such that you can derive the number of rows in the view from the number of rows in the tables.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1006855
Posted Tuesday, October 19, 2010 6:24 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 9:54 AM
Points: 912, Visits: 654
Hugo Kornelis (10/19/2010)

Only if they are indexed. Or if the relationships between the underlying tables are such that you can derive the number of rows in the view from the number of rows in the tables.


Sadly no, on both counts

Oh well... thanks anyway.

Post #1006903
Posted Tuesday, October 19, 2010 6:53 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, June 9, 2014 7:11 PM
Points: 753, Visits: 170
I tried the four queries on a table that has 36k rows, using as parameter the amount of rows returned by the count(*) query, the fourth option is not accurate at all, actually it displays about 400k rows (this table have no modifications at all), and compared to the count(*) one, it less efficient as well.

The only one that gave the most reliable result and was fast, was the count(*) option, followed by the third query. That's what I've find for this table, it might change for bigger tables


Frank.
Post #1006926
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse