What is the most efficient paging solution using SQL Server 2005 against a table with around 5,000-10,000 rows? I've seen several out there but nothing comparing them.
+4
A:
For a table that size, use a Common-Table Expression (CTE) and ROW_NUMBER; use a small function to calculate the records to bring back based on @PageNumber and @PageSize variables (or whatever you want to call them). Simple example from one of our stored procedures:
-- calculate the record numbers that we need
DECLARE @FirstRow INT, @LastRow INT
SELECT @FirstRow = ((@PageNumber - 1) * @PageSize) + 1,
@LastRow = ((@PageNumber - 1) * @PageSize) + @PageSize
;
WITH CTE AS
(
SELECT [Fields]
, RowNumber AS ROW_NUMBER() OVER (ORDER BY [Field] [ASC|DESC])
FROM [Tables]
WHERE [Conditions, etc]
)
SELECT *
-- get the total records so the web layer can work out
-- how many pages there are
, (SELECT COUNT(*) FROM CTE) AS TotalRecords
FROM CTE
WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY RowNumber ASC
Keith Williams
2010-09-19 19:57:05
Do you have to wait for the proc to run once before you can get the total number of pages?
Caveatrob
2010-09-19 20:42:38
PS - this is working great so far!
Caveatrob
2010-09-19 20:55:08
Yes - the way we do it is to use the results of the proc to populate a little PagingInfo object which handles the process of writing out the page number links. Assuming you're not doing a crazy number of joins or function calls in the query, this baby should run near-instantly, so it's never been an issue for us. Glad it's working for you :)
Keith Williams
2010-09-19 21:46:25
It doesn't do the count operation over and over for each row, right?
Caveatrob
2010-09-19 23:18:45
It will, but as I understand it the query analyser will have cached the results of the inner CTE expression, so the count expression adds very little noticeable overhead. It will also be executed only for the n rows that you're actually returning. The only way around it would be to store results in a table variable, but that adds memory overhead.
Keith Williams
2010-09-20 12:58:19
A:
One of the best discussions of various paging techniques I've ever read is here: SQL Server 2005 Paging – The Holy Grail. You'll have to complete a free registration on SQLServerCentral.com to view the article, but it's well worth it.
Joe Stefanelli
2010-09-19 20:54:45