Getting a subset of records along with the total number of records

Question

I am working on returning a recordset from SQL Server 2008 to do some pagination. I only return 15 records at a time, but I need to have a total number of matches along with a subset of the records. I used two different queries with mixed results depending on where in the larger group I need to pull out a subset. Here's a sample:

SET NOCOUNT ON; WITH tempTable AS ( SELECT FirstName , LastName , ROW_NUMBER() OVER(ORDER BY FirstName ASC) AS RowNumber FROM People WHERE Active = 1 ) SELECT tempTable.* , (SELECT Max(RowNumber) FROM tempTable) AS Records FROM tempTable WHERE RowNumber >= 1 AND RowNumber <= 15 ORDER BY FirstName 

This query is very fast when I return items at the lower end of matches, for example records 1 to 15. However, when I start returning records 1000-1015, processing will go from less than a second to more than 15 seconds.

So, I replaced the request with the following:

 SET NOCOUNT ON; WITH tempTable AS ( SELECT * FROM ( SELECT FirstName , LastName , ROW_NUMBER() OVER(ORDER BY FirstName ASC) AS RowNumber , COUNT(*) OVER(PARTITION BY NULL) AS Records FROM People WHERE Active = 1 ) derived WHERE RowNumber >= 1 AND RowNumber <= 15 ) SELECT tempTable.* FROM tempTable ORDER BY FirstName 

This request starts a large number of returns in 2-3 seconds, but also starts a low-number request in 2-3 seconds. Since it does the counting for each of the 70,000+ lines, it makes each request longer, and not just large line numbers.

Therefore, I need to figure out how to get a good row counter, and also return only a subset of elements at any point in the result set without experiencing such a huge penalty. I could do a fine in 2-3 seconds for high line numbers, but 15 is too much, and I don’t want to endure the slow loads on the first few pages that people see.

NOTE. I know that in the second example I do not need CTE, but this is a simple example. In production, I do further joining tempTable after I filter it to 15 lines that I need.

Answer 1

Here is what I did (and its as fast, no matter what records I return):

 --Parameters include: @pageNum int = 1, @pageSize int = 0, DECLARE @pageStart int, @pageEnd int SELECT @pageStart = @pageSize * @pageNum - (@pageSize - 1), @pageEnd = @pageSize * @pageNum; SET NOCOUNT ON; WITH tempTable AS ( SELECT ROW_NUMBER() OVER (ORDER BY FirstName ASC) AS RowNumber, FirstName , LastName FROM People WHERE Active = 1 ) SELECT (SELECT COUNT(*) FROM tempTable) AS TotalRows, * FROM tempTable WHERE @pageEnd = 0 OR RowNumber BETWEEN @pageStart AND @pageEnd ORDER BY RowNumber 

Answer 2

I handled a situation a bit like this in the past, not bothering to determine a specific number of rows, but using a query plan to give me an approximate number of rows, a bit like the first element in this link, describes

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108658

Then it was intended to deliver any rows specified within the range (say, 900-915), and then return an estimated number of rows, for example

 rows 900-915 of approx. 990 

which avoided counting all rows. As soon as the user moves beyond this point, I just showed

 rows 1000-1015 of approx. 1015 

i.e. just taking the last requested string as my new estimate.