SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's the best way to count?


What's the best way to count?

Author
Message
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4296 Visits: 4408
mtillman-921105 (10/19/2010)
Oracle's even harder to use.

Hmm... Are you saying this as a developer or as a DBA?
mtillman-921105
mtillman-921105
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1468 Visits: 3852
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
Lana Gold
Lana Gold
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 215
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.
Richard Sisk
Richard Sisk
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2105 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 :-D
kevin.l.williams
kevin.l.williams
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3633 Visits: 1323
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.



Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18351 Visits: 12426
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. Hehe

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 :-D

Not if it's a 20TB table :-P


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Oleg Netchaev
Oleg Netchaev
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2169 Visits: 1817
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. Hehe

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
mtillman-921105
mtillman-921105
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1468 Visits: 3852
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
Ron Kunce
Ron Kunce
SSC Eights!
SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)SSC Eights! (808 reputation)

Group: General Forum Members
Points: 808 Visits: 496
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64231 Visits: 18570
good question. Thanks



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search