Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Denali Paging–Is it win/win ?

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

Posted by Anonymous on 10 November 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Denali Paging???Is it win/win ? - Dave Ballantynes blog         [sqlservercentral.com]        on Topsy.com

Posted by TheSQLGuru on 14 November 2010

This feature strikes me as being similar to Scalar UDFs - playing "performance Russian Roulette" with a gun with about 5.92 bullets in it.  @TheSQLGuru goes KAAAAACCCHHHIIINNNNNN!!  :-)  

Posted by Anonymous on 23 November 2010

Pingback from  SQL Server 2011 aka Denali : Phalanx Blogosphere

Leave a Comment

Please register or log in to leave a comment.