SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

Denali Paging–Is it win/win ?

UPDATE : 2012-07-26 : My latest commentry of the paging subject can be found here :  http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx


I'm not one for automatically poo-pooing new ideas, new practices and new technology.  Rather I like to take a measured view and try to evaluate based upon merits.

One of the new features in Denali is paging which is now supported by the FETCH option of the ORDER BY clause.  On the face of it, it does seem like a really neat addition to the product set,  but as ever we do need to find out what the real costs associated with a query are.

So, using Paul Whites (blog|twitter) rather excellent series on paging as a comparison base ive created a simple stored proc using FETCH.

DROP PROCEDURE dbo.DenaliPageFetch
CREATE PROCEDURE dbo.DenaliPageFetch   @PageSize   BIGINT,
@PageNumber BIGINT


FROM    dbo.Post P2
ORDER   BY p2.post_id ASC
OFFSET  @PageSize * (@PageNumber-1) ROWS

and then i’ve compared the performance of Paul’s FetchPageKeySeek procedure to my new DenaliPageFetch.  Starting of at a low range , 1st page of 50 rows, the denali option does indeed give better performance , but by jumping to the 200th page of 50 rows we see a rather different picture emerge.


I think those figures speak for themselves,  so does the execution plan give a reason for this high cost ?



Yup, that’s right is has scanned through the top 10000 rows.  Admittedly this is only CTP1 and maybe Microsoft will invest some more time and effort into this area , but right now im not going to.


steveh99999 said:

excellent bit of research Dave - was wondering, did you fnd out the tipping point where the new functionality starts to become less efficient ?

# November 10, 2010 9:46 PM

Dave Ballantyne said:

Hi Steve, can i say , "it depends" ? :)

I wouldnt say that there are hard and fast rules for a tipping point , its going to be down to data size etc.

Maybe the most efficient method would be to use the denali functionality for 'low' pages but Paul's for 'high' pages.  Will make for more maint, but if it is being executed thousands of times , it should make a difference overall.

# November 11, 2010 10:36 AM

Row Offset in SQL Server - DexPage said:

Pingback from  Row Offset in SQL Server - DexPage

# July 24, 2015 10:04 PM