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
GO
CREATE PROCEDURE dbo.DenaliPageFetch   @PageSize   BIGINT,
                                      
@PageNumber BIGINT

AS

SELECT 
P2.post_id,
       
P2.thread_id,
       
P2.member_id,
       
P2.create_dt,
       
P2.title,
       
P2.body
FROM    dbo.Post P2
ORDER   BY p2.post_id ASC
OFFSET  @PageSize * (@PageNumber-1) ROWS
FETCH NEXT @PageSize ROWS ONLY

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.

image

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

image

 

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.

Comments

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