Efficient pagination for large set of data?
This is what I pondered about today. Maybe I also will have some time to test it.
CREATE PROCEDURE dbo.uspPaginate
(
@PageNumber INT,
@RecordsPerPage TINYINT = 50
)
AS
SET NOCOUNT ON
DECLARE @MaxRows INT
SET @MaxRows = @PageNumber * @RecordsPerPage
SELECT SomeColumns
FROM (
SELECT TOP (@RecordsPerPage)
SomeColumns
FROM (
SELECT TOP (@MaxRows)
SomeColumns
FROM YourTable
ORDER BY SomeCase ASC/DESC
)
ORDER BY SomeCase DESC/ASC
)
ORDER BY SomeCase ASC/DESC
Topic is here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97550
Legacy Comments
|
Mladen
2008-02-20 |
re: Efficient pagination for large set of data? well we've been using this for quite a while and i can't really say there's any speed benefit... |
|
Eiríkur Fannar Torfason
2008-02-21 |
re: Efficient pagination for large set of data? This will get slow pretty fast as the number of rows in the table grows and as you navigate to higher page numbers. My money is on an O(n log n) growth rate in terms of time complexity. |
|
Jeff Moden
2009-07-20 |
re: Efficient pagination for large set of data? Heh... I agree... remarkably slow on the last page of a million row table.... unless you convert it all to the old style TOP using dynamic SQL and then it flies. --Jeff Moden |