InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 11:21:32 GMT20http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]gkganeshbe (12/26/2012)[/b][hr]Is there any performance difference in the below given three queries.Note: Col1 is NOT NULL columnSELECT COUNT(*) FROM Sales.SalesOrderDetail;SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;SELECT COUNT(0) FROM Sales.SalesOrderDetail;[/quote]Good question. I ran all 3 of these statements as a batch substituting SalesOrderID for Col1 and displayed the estimated execution plan. The estimated plan showed the cost of each query to be the same at 33%.Just out of curiosity I then added Query 4 from the question and according to the estimated execution plan it is still the fastest. They all were equally accurate.Thu, 27 Dec 2012 06:43:38 GMTDave62http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]gkganeshbe (12/26/2012)[/b][hr]Is there any performance difference in the below given three queries.Note: Col1 is NOT NULL columnSELECT COUNT(*) FROM Sales.SalesOrderDetail;SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;SELECT COUNT(0) FROM Sales.SalesOrderDetail;[/quote]Not really. The second one with COUNT(Col1) may take a very small performance hit during the parse and bind phases, to check whether there is a Col1 column and if it's nullable, but that is an extremely short amount of time.Thu, 27 Dec 2012 03:57:40 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxIs there any performance difference in the below given three queries.Note: Col1 is NOT NULL columnSELECT COUNT(*) FROM Sales.SalesOrderDetail;SELECT COUNT(Col1) FROM Sales.SalesOrderDetail;SELECT COUNT(0) FROM Sales.SalesOrderDetail;Wed, 26 Dec 2012 23:48:41 GMTGanesh Kamalhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxGreat question that perfectly helps demo on improving performance by use of already existing system statistics hence avoiding much strain and unnecessary use of system resources to gather the statistics.Thank you.Mon, 27 Feb 2012 11:11:58 GMTzymoshttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxYou can read another discussion about row count at :http://ask.sqlservercentral.com/questions/1980/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts?page=1#1983Tue, 09 Aug 2011 06:51:50 GMThakan.wintherhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx>> I think I know why they can get away with it. It's because Oracle's even harder to use <<No, I have found Oracle to be way easier to learn and use.IMHO -- Mark D Powell --Mon, 01 Nov 2010 07:42:03 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxQuery 1 is reliable while 4th one is the fastest...Fri, 29 Oct 2010 20:36:31 GMTDhruvesh Shahhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxMy reasoning also.Thu, 28 Oct 2010 10:51:02 GMTjbwahttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxYou didn't include the restriction on the index_id in your query on the DMV, so that might be the issue.Try this and see what you get:[code="sql"]selectsum(row_count)fromsys.dm_db_partition_statswhereobject_id = object_id('dibs_tmb_saalist')and index_id in (0,1)[/code]Fri, 22 Oct 2010 12:19:24 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI think the answer is wrong on 2005. Look at the following:select sum(row_count)from sys.dm_db_partition_statswhere object_id = object_id('dibs_tmb_saalist') and (index_id = 0 or index_id = 1) select count(*) from dibs_tmb_saalistgoProduced:(No column name)310825(No column name)311992That is a pretty significant error in my book. Query 1 is the only reliable method posted.HTH -- Mark D Powell --Note - above is corrected to include index_id line which reduced the error but did not eliminate it.Fri, 22 Oct 2010 12:15:12 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]mtillman-921105 (10/21/2010)[/b][hr][quote][b]Tom.Thomson (10/21/2010)[/b][hr][quote][b]mtillman-921105 (10/19/2010)[/b][hr][quote][b]kevin.l.williams (10/19/2010)[/b][hr]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.[/quote]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.[/quote]COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT (or of course if you use HOLDLOCK).[/quote]For real? I'll have to look into that Tom. By the way, NULLs do count in a COUNT(*) - I did notice that. :-D[/quote]I cannot completely agree.In fact, COUNT(*) under REPEATABLE READ may return wrong results. I wrote a repro script here:[url=http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/10/21/myth-busting-count-under-repeatable-read-may-return-wrong-results.aspx][/url]Thu, 21 Oct 2010 10:51:57 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Tom.Thomson (10/21/2010)[/b][hr][quote][b]mtillman-921105 (10/19/2010)[/b][hr][quote][b]kevin.l.williams (10/19/2010)[/b][hr]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.[/quote]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.[/quote]COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT (or of course if you use HOLDLOCK).[/quote]For real? I'll have to look into that Tom. By the way, NULLs do count in a COUNT(*) - I did notice that. :-DThu, 21 Oct 2010 08:59:17 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxdelThu, 21 Oct 2010 08:55:17 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]mtillman-921105 (10/19/2010)[/b][hr][quote][b]kevin.l.williams (10/19/2010)[/b][hr]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.[/quote]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.[/quote]COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT (or of course if you use HOLDLOCK).Thu, 21 Oct 2010 08:19:55 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxThe Question and the discussion is awesome.Tue, 19 Oct 2010 23:50:21 GMTsharath.chalamgarihttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]UMG Developer (10/19/2010)[/b][hr]I don't know that that is true anymore, at least with Oracle 10g. Count(*) is still slow compared to SQL Server, but I find that Count('X') is even slower. (36 secs vs. 80 secs in my test.)[/quote]Fortunately for me, I don't know anything about Oracle past version 8i and 9. Perhaps they figured out how to speed up their count(*) by now (it is 21st century after all). The bottom line is that the execution time of Oracle's count(*) is still inferior when compared with SQL Server 2000 or better.OlegTue, 19 Oct 2010 20:15:47 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Oleg Netchaev (10/19/2010)[/b][hr]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 [b]select count(*) from the_table;[/b] 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 [b]count(*)[/b], but opt for a much better performing [b]count('X')[/b] instead, but still it was frustrating. [/quote]I don't know that that is true anymore, at least with Oracle 10g. Count(*) is still slow compared to SQL Server, but I find that Count('X') is even slower. (36 secs vs. 80 secs in my test.)Tue, 19 Oct 2010 18:08:16 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI answered it correctly because I knew what the author was talking about, but for myself I will stick with COUNT(*). (It runs in under 2 seconds on our 70 GB table, so speed isn't that big of an issue.) Also, most code completion tools won't help you with the schema and table name when writing the alternate count queries.Tue, 19 Oct 2010 18:01:54 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxgood question. ThanksTue, 19 Oct 2010 16:58:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI 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:23Tue, 19 Oct 2010 14:09:29 GMTRon Kuncehttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]kevin.l.williams (10/19/2010)[/b][hr][quote][b]mtillman-921105 (10/19/2010)[/b][hr]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?[/quote]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.[/quote]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.Tue, 19 Oct 2010 12:25:27 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]mtillman-921105 (10/19/2010)[/b][hr]I think I know why they can get away with it. It's because Oracle's even harder to use. :hehe:[/quote]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 [b]select count(*) from the_table;[/b] 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 [b]count(*)[/b], but opt for a much better performing [b]count('X')[/b] instead, but still it was frustrating. Even though now I work with SQL Server and do undertand that [b]count(*)[/b] is basically just as good as [b]count(some_literal)[/b] 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 [b]count('X')[/b] because it emits a very strong and offensive Oracle odor, but opt for [b]count(1)[/b] just in case. :-)OlegTue, 19 Oct 2010 11:35:06 GMTOleg Netchaevhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Alexander Kuznetsov (10/19/2010)[/b][hr]Did you verify how method 4 works under high concurrency? Can we get somewhat incorrect totals?[/quote]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.[quote][b]mtillman-921105 (10/19/2010)[/b][hr]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:[/quote]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.[quote][b]Richard Sisk (10/19/2010)[/b][hr]Well, if I'm typing the query into a SSMS window, I'll go with number 1 and finish way before anyone else :-D[/quote]Not if it's a 20TB table :-PTue, 19 Oct 2010 11:06:27 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]mtillman-921105 (10/19/2010)[/b][hr]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?[/quote]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.Tue, 19 Oct 2010 10:30:33 GMTkevin.l.williamshttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxWell, if I'm typing the query into a SSMS window, I'll go with number 1 and finish way before anyone else :-DTue, 19 Oct 2010 09:54:53 GMTRichard Siskhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxHere 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.Tue, 19 Oct 2010 09:31:17 GMTLana Goldhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]vk-kirov (10/19/2010)[/b][hr][quote][b]mtillman-921105 (10/19/2010)[/b][hr]Oracle's even harder to use.[/quote]Hmm... Are you saying this as a developer or as a DBA?[/quote]I'm saying this as a developer, but my understanding is that both are more difficult with Oracle.Tue, 19 Oct 2010 09:30:40 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]mtillman-921105 (10/19/2010)[/b][hr]Oracle's even harder to use.[/quote]Hmm... Are you saying this as a developer or as a DBA?Tue, 19 Oct 2010 09:27:30 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxTerrific question, thanks.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:Tue, 19 Oct 2010 09:17:06 GMTmtillman-921105http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxGreat discussion. I think I've learned more from reading the discussion than I did when researching the question.For those concerned with maintenance nightmares or the difficulty in writing query 4 versus Select Count(*) I would suggest putting query for in a stored procedure or a function. Something likesproc_RecordCount '[schema].TableName'Not much more difficult to write than Select Count(*).For maintenance just change the procedure or function as needed and everywhere your code is using it continues working (assuming change does not add any required parameters and maintains backward compatibility).DaveTue, 19 Oct 2010 09:10:49 GMTDave62http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxHello!First of all - EXCELLENT QUESTION!I got it right, however I followed the following reasoning:1. As most have highlighted - the Query# 1 is obviously "accurate", but does a table scan and is therefore slower2. Query# 4 uses system views and per BOL: "The counts on which the output is based are cached in memory or stored on disk in various system tables."Hugo: Thank-you very much for your sound insight on the internals of SQL Server.You simply blasted the foundations of my reasoning# 1 by proving that SELECT COUNT(*) is indeed inaccurate.Hats off to you as well.At the end of the day, all I can say is - it's been fun learning today!Tue, 19 Oct 2010 09:08:50 GMTNakul Vachhrajanihttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxReally nice question & good knowledge sharing conversation.Tue, 19 Oct 2010 09:05:13 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Hugo Kornelis (10/19/2010)[/b][hr]I liked the question - allthough with the exception of option 1 on really large (or busy) tables, all options will be blindingly fast, and you won't run this type of query often enough that a few microseconds more or less really matters.I gave the right answer, but not for the reasons listed. The question specifically asked for "fastest and most reliable", so I focused on those aspects of the queries.Option 1 scans the complete table (or one of the indexes). Absolutely reliable, but not fast. This answer can only be correct if all others are not.Option 3 is not reliable. The [url=http://msdn.microsoft.com/en-us/library/ms175012.aspx]Books Online entry for sys.partitions[/url] specifically describes the "rows" column as an "[b]approximate[/b] number of rows in this partition". (The cast to float of the result and the cast to int of object_id are not wrong, though they are of course weird).Options 2 and 4 are both reliable. (The explanation of the question says sysindexes can be unreliable. This is not supported by the referenced sources. The sysindexes system table [i]was[/i] unreliable in SQL 2000 and before, but this has changed in SQL Server 2005 - [url=http://sqlblogcasts.com/blogs/simons/archive/2007/11/06/Is-the-rows-figure-stored-in-sysindexes-accurate.aspx]here is a reference[/url]. Also, Books Online does not contain the words "approximate", "inaccurate", or anything similar for these views, as it does for sys.partitions.Both the question explanation and the [url=http://msdn.microsoft.com/en-us/library/ms190283.aspx]description in Books Online of sys.sysindexes[/url] mention that the sys.sysindexes compatibility view will be removed in future versions of SQL Server. This is very relevant for real work - but it was not a deciding factor in this question, so this should not be used to decide between options 2 and 4.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." The table used in the example code is not partitioned, but I interpreted the code as examples for a randomly chosen sample table, and the question about the method in general. Since method 2 does not work for partitioned tables, it's not reliable.Method 4 and method 1 are both reliable. Method 1 scans the table (or an index); method 4 uses systemm views - this one is much faster. That's why I chose method 4.[/quote]Hi Hugo,Did you verify how method 4 works under high concurrency? Can we get somewhat incorrect totals?Tue, 19 Oct 2010 08:19:48 GMTAlexander Kuznetsovhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI went with the obvious and straightforward answer; I expected to be wrong."Most reliable", "best way to count", etc. are somewhat subjective.If volumes of code are deployed using cool-counting-trick #4 and the engineers further optimize the implementation of less-cool-but-semantically-correct method #1, the cool trick becomes the underperforming cruft of legacy code. I would rather exercise the discipline to use count(*) when I want a count even if it costs more than to use alternative methods that return "something like count" that isn't a true count. ex: We don't put [ChildrenRows] on a parent record and read from it with confidence that the mechanisms that keep it up to date are infallible (at least I hope we know that).If I am interested in knowing the approximate number of rows in a table that is too large to count() I would use sp_spaceused because it's much shorter to type than #4. btw, if you do deploy #4 in production code, please leave me a comment such as -- this performs better than: select count(*) .... so when I inherit the maintenance of your cleverness I don't have to spend time tracking down how that query works. thanks.Tue, 19 Oct 2010 08:06:36 GMTMike Dougherty-384281http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI ran query 1 and query 4 on two tables. One table had 183 rows and the other had 9,886,862 rows. On the first table, query one had a cost of 52% and query 4 had a cost of 48%. Not much of a difference. However, when I ran it on a much larger table, query 1 had a cost of 100% and query 2 had a cost of 0%. Although I my initial answer was query 1, I am in agreement with others that query 4 is the way to go. Despite the difference in cost, both queries took less than a second to run on both tables.Tue, 19 Oct 2010 07:51:06 GMTdaveb87http://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Hugo Kornelis (10/19/2010)[/b][hr]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.[/quote]Ah! Indeed, I forgot about this behavior. Thanks to Microsoft for introducing snapshot isolation in SQL Server 2005 :-)Tue, 19 Oct 2010 07:41:45 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI have been really getting beat up by the QoTD lately, but I am learning a lot. The code from #4 went right into my code library.The follow up discussion here is also very good. Yes, Hugo you have developed a reputation here. Whenever I get a question incorrect, it is usually your follow on explanation that helps me find some understanding. Thanks.Tue, 19 Oct 2010 07:31:30 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxShirley the fastest query to enter at a console is #1. Also, being the shortest, it would be the most reliable to run the first time without some sort of unintended syntax error.Tue, 19 Oct 2010 07:27:08 GMTrod moellerhttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspxI 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 :-)Tue, 19 Oct 2010 06:53:56 GMTf_ernestoghttp://www.sqlservercentral.com/Forums/Topic1006683-2739-1.aspx[quote][b]Hugo Kornelis (10/19/2010)[/b][hr]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.[/quote]Sadly no, on both counts :crying:Oh well... thanks anyway.Tue, 19 Oct 2010 06:24:14 GMTdave.farmer