Get Total Rows.
in [Programming SQL]
|
Prev: Row_Number() Sorting
Next: Remote Access Steps
From: tshad on 26 Mar 2010 19:40 If you are doing paging you need to pass back the total number of rows of the query. What is the best way to do that? What one person does is do the SELECT/INTO a temp table to get the total number of rows. Then he does a "SET ROWCOUNT @RowNumber", then does a SELECT * ... The problem here is that he is using doing an insert into a temp table with 2 selects (one to insert and one to pass back the results). What I do is have 2 functions (could do it in one with a parameter) that have identical queries except 1) in one I have all the columns I want to return and in the other I have a SELECT using the ROW_NUMBER() function. I have to do it this way because the number of @@ROWCOUNT will be equal to the number of rows from your "WHERE ResultSetNumber BETWEEN @StartRow and @EndRow" clause not the number of the total result set. 2) in the other I have a "SELECT Count(*)" with the rest of the query the same as #1. Is there a better way to do this? Thanks, Tom
From: Plamen Ratchev on 26 Mar 2010 20:03 You can do it in a single query: SELECT <columns>, rk, total_count FROM ( SELECT <columns>, ROW_NUMBER() OVER... AS rk, COUNT(*) OVER() AS total_count FROM Foo) AS F WHERE rk BETWEEN @page_start AND @page_end; -- Plamen Ratchev http://www.SQLStudio.com
From: tshad on 26 Mar 2010 21:28 That worked pretty well. I had never seen the COUNT(*) OVER() before. Is it new? But it does add some overhead. I am trying to optimize a query that was doing a FULL JOIN into a temp table (to get the Total Count) then does a SET ROWCOUNT followed by a SELECT *... from the temporary table. Doing that the profiler was showing 340400 Reads. I got rid of the Temporary table and got the number of reads down to 339110 reads (about 10,000 reads less). But when I added the COUNT(*) OVER() to the SELECT - it went up to 339,900 (not sure why that is). I am still trying to see if I can get rid of the JULL JOIN. I noticed it was doing a couple of Hash Matches (right outer join) but the cost was only 1% and 2% but there was a couple Nested Loops (Left Semi Join) - one was 21%. Thanks, Tom "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:hYGdndk2a5qi1jDWnZ2dnUVZ_gKdnZ2d(a)speakeasy.net... > You can do it in a single query: > > SELECT <columns>, rk, total_count > FROM ( > SELECT <columns>, > ROW_NUMBER() OVER... AS rk, > COUNT(*) OVER() AS total_count > FROM Foo) AS F > WHERE rk BETWEEN @page_start AND @page_end; > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 26 Mar 2010 22:25 COUNT() OVER() was added in SQL Server 2005, together with the ranking functions. The OVER clause supports the other aggregate functions too (SUM, MIN, MAX, AVG). If you are talking about logical reads then it may not be an issue. See more here: http://www.sql-server-performance.com/articles/per/logical_reads_p1.aspx -- Plamen Ratchev http://www.SQLStudio.com
From: Erland Sommarskog on 27 Mar 2010 06:19
tshad (tfs(a)dslextreme.com) writes: > I had never seen the COUNT(*) OVER() before. Is it new? > > But it does add some overhead. > > I am trying to optimize a query that was doing a FULL JOIN into a temp > table (to get the Total Count) then does a SET ROWCOUNT followed by a > SELECT *... from the temporary table. > > Doing that the profiler was showing 340400 Reads. > > I got rid of the Temporary table and got the number of reads down to > 339110 reads (about 10,000 reads less). > > But when I added the COUNT(*) OVER() to the SELECT - it went up to 339,900 > (not sure why that is). I wouldn't say that 340400, 339900 or 339110 are all the same to me. And what foremost matters to performance is wallclock time. I you are going to paging, saving the result set in a session-keyed table once, and then page from that table might be the best option. Or if the number of rows is not excessive, just a few thousand rows, get all rows into the client and page there. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |