Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

 

Print | posted on Tuesday, February 19, 2008 4:12 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# 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...
2/20/2008 10:23 AM | Mladen
Gravatar

# 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.
2/21/2008 3:44 PM | Eiríkur Fannar Torfason
Gravatar

# 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
7/20/2009 1:36 AM | Jeff Moden
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET