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 «««23456»»»

What's the best way to count? Expand / Collapse
Author
Message
Posted Tuesday, October 19, 2010 9:27 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
mtillman-921105 (10/19/2010)
Oracle's even harder to use.

Hmm... Are you saying this as a developer or as a DBA?
Post #1007066
Posted Tuesday, October 19, 2010 9:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
vk-kirov (10/19/2010)
mtillman-921105 (10/19/2010)
Oracle's even harder to use.

Hmm... Are you saying this as a developer or as a DBA?


I'm saying this as a developer, but my understanding is that both are more difficult with Oracle.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1007069
Posted Tuesday, October 19, 2010 9:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 10:05 AM
Points: 91, Visits: 184
Here is the execution resuls of the 1-st and 4-th query:

--Query 1
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 76 ms.

--Query 4
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 142 ms.

So the Query #1 was faster.
Post #1007071
Posted Tuesday, October 19, 2010 9:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 16, 2013 8:08 AM
Points: 1,987, Visits: 210
Well, if I'm typing the query into a SSMS window, I'll go with number 1 and finish way before anyone else
Post #1007087
Posted Tuesday, October 19, 2010 10:30 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: Today @ 8:08 AM
Points: 3,146, Visits: 1,262
mtillman-921105 (10/19/2010)

It's simple tasks like this that I think MS needs to work on. Judging by this question, most of us (yours truly included) can't even get an accurate count of rows in a table. That's just nuts! MS can't simply write us a function so we don't have to sweat the small stuff like this?


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.



Post #1007129
Posted Tuesday, October 19, 2010 11:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 6,007, Visits: 8,270
Alexander Kuznetsov (10/19/2010)
Did you verify how method 4 works under high concurrency? Can we get somewhat incorrect totals?

Hi Alex,

Examples of incorrect rsults for this method have already been posted in the disussion. Though "incorrect" depends on the goal here - do you need to know the number of order detail lines, or the number of rows that the table occupies at moment X. In the latter case, I'd argue that uncommitted rows HAVE to be counted.

In the former case, the only truly reliable way is method #1 with either snapshot isolation, or a table lock.


mtillman-921105 (10/19/2010)
It's simple tasks like this that I think MS needs to work on. Judging by this question, most of us (yours truly included) can't even get an accurate count of rows in a table. That's just nuts! MS can't simply write us a function so we don't have to sweat the small stuff like this?

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

Note that you only run into problems under high concurrency. Oracle has less of those problems, because it uses snapshot isolation by default. You can set the same default in SQL Server (read_committed_snapshot). This does come at the expense of extra overhead, which is why this option is disabled by default in SQL Server.


Richard Sisk (10/19/2010)
Well, if I'm typing the query into a SSMS window, I'll go with number 1 and finish way before anyone else

Not if it's a 20TB table



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1007160
Posted Tuesday, October 19, 2010 11:35 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
mtillman-921105 (10/19/2010)
I think I know why they can get away with it. It's because Oracle's even harder to use.

Very true indeed. I did have quite few unfortunate years in my career when I had to work with Oracle databases. I remember it used to really frustrate me that the select count(*) from the_table; in Oracle takes forever longer than the similar query against similar table in SQL Server. Of course there was a decent workaround to NEVER use count(*), but opt for a much better performing count('X') instead, but still it was frustrating.

Even though now I work with SQL Server and do undertand that count(*) is basically just as good as count(some_literal) because scan is a scan, I still remember my dark days and opt for some_literal even in T-SQL. I don't go for count('X') because it emits a very strong and offensive Oracle odor, but opt for count(1) just in case.

Oleg
Post #1007184
Posted Tuesday, October 19, 2010 12:25 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 598, Visits: 3,816
kevin.l.williams (10/19/2010)
mtillman-921105 (10/19/2010)

It's simple tasks like this that I think MS needs to work on. Judging by this question, most of us (yours truly included) can't even get an accurate count of rows in a table. That's just nuts! MS can't simply write us a function so we don't have to sweat the small stuff like this?


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.


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #1007227
Posted Tuesday, October 19, 2010 2:09 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 1:33 PM
Points: 457, Visits: 476
I also tried all four queries and found that query 1 (SELECT COUNT(*)) was marginally faster in the Client Statistics! Possibly its server dependent?


q4 q3 q2 q1
Client Execution Time 14:55:22 14:54:45 14:54:27 14:54:05

q4 q3 q2 q1
14:52:13 14:51:52 14:51:39 14:51:23


Ron K.

"Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler
Post #1007288
Posted Tuesday, October 19, 2010 4:58 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 4:52 PM
Points: 17,654, Visits: 15,507
good question. Thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1007392
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse