Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

SQL Server 2005 Paging – The Holy Grail Expand / Collapse
Author
Message
Posted Tuesday, December 7, 2010 2:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 323, Visits: 2,200
SwePeso (12/6/2010)
It seems the dual TOP/ORDER BY was the most efficient approach by the time.
I tested the new Denali OFFSET/FETCH and I got the exact same number of reads for @StartRow = 5000, but 2 ms instead of 4 ms.

SELECT		Number
FROM dbo.TallyNumbers
ORDER BY Number
OFFSET @StartRow - 1 ROWS
FETCH NEXT 50 ROWS ONLY


This is unreleased SQL Server 2011 functionality only, right?
Post #1031092
Posted Tuesday, December 7, 2010 3:27 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
SwePeso (12/6/2010)
It seems the dual TOP/ORDER BY was the most efficient approach by the time.
I tested the new Denali OFFSET/FETCH and I got the exact same number of reads for @StartRow = 5000, but 2 ms instead of 4 ms.

SELECT		Number
FROM dbo.TallyNumbers
ORDER BY Number
OFFSET @StartRow - 1 ROWS
FETCH NEXT 50 ROWS ONLY



My take on the Denali paging....

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/11/10/denali-paging-is-it-win-win.aspx

and

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/11/19/denali-paging-key-seek-lookups.aspx




Clear Sky SQL
My Blog
Kent user group
Post #1031105
Posted Monday, November 7, 2011 11:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 8, 2011 2:28 AM
Points: 1, Visits: 2
This is good. But i wanted to ask, what about the situation in which, say there are only 5 records in the table, and you ask for page number 2 with page size 10?
In that case there is no data returned to you, and you will not get to know how many records are there, in the table.
Post #1201817
Posted Saturday, October 26, 2013 12:58 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 27, 2013 5:56 AM
Points: 3, Visits: 14
I'm quite surprised that the "holy grail of paging" doesn't mention the (granted a bit less popular) "seek method" as described here:
http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

Take the following example:

    SELECT TOP 10 first_name, last_name, score
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

The @previousScore and @previousPlayerId values are the respective values of the last record from the previous page. This allows you to fetch the "next" page. If the ORDER BY direction is ASC, simply use > instead.

With the above method, you cannot immediately jump to page 4 without having first fetched the previous 40 records. But often, you do not want to jump that far anyway. Instead, you get a much faster query that might be able to fetch data in constant time, depending on your indexing. Plus, your pages remain "stable", no matter if the underlying data changes (e.g. on page 1, while you're on page 4).

This is the best way to implement paging when lazy loading more data in web applications, for instance.


Lukas

---------------
When Java and SQL work together, great software can evolve. That's why we have created jOOQ. Follow us on blog.jooq.org
Post #1508670
Posted Saturday, October 26, 2013 6:16 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 4,320, Visits: 6,113
Sorry, but at first blush I think parameter sniffing (without some other query addition) could totally screw you here. Maybe an OPTION (RECOMPILE) could help, depending on what version of SQL Server you are on.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1508683
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse