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

Denali Paging–Key seek lookups

In my previous post “Denali Paging – is it win.win ?” I demonstrated the use of using the Paging functionality within Denali.  On reflection,  I think i may of been a little unfair and should of continued always planned to continue my investigations to the next step.

In Pauls article, he uses a combination of ctes to first scan the ordered keys which is then filtered using TOP and rownumber and then uses those keys to seek the data.  So what happens if we replace the scanning portion of the code with the denali paging functionality.

Heres the original procedure,  we are going to replace the functionality of the Keys and SelectedKeys ctes :

CREATE  PROCEDURE dbo.FetchPageKeySeek
       
@PageSize   BIGINT,
       
@PageNumber BIGINT
AS
BEGIN
       
-- Key-Seek algorithm
       
WITH    Keys
       
AS      (
               
-- Step 1 : Number the rows from the non-clustered index
                -- Maximum number of rows = @PageNumber * @PageSize
               
SELECT  TOP (@PageNumber * @PageSize)
                       
rn = ROW_NUMBER() OVER (ORDER BY P1.post_id ASC),
                       
P1.post_id
               
FROM    dbo.Post P1
               
ORDER   BY
                       
P1.post_id ASC
               
),
               
SelectedKeys
       
AS      (
               
-- Step 2 : Get the primary keys for the rows on the page we want
                -- Maximum number of rows from this stage = @PageSize
               
SELECT  TOP (@PageSize)
                       
SK.rn,
                       
SK.post_id
               
FROM    Keys SK
               
WHERE   SK.rn > ((@PageNumber - 1) * @PageSize)
               
ORDER   BY
                       
SK.post_id ASC
               
)
       
SELECT  -- Step 3 : Retrieve the off-index data
                -- We will only have @PageSize rows by this stage
               
SK.rn,
               
P2.post_id,
               
P2.thread_id,
               
P2.member_id,
               
P2.create_dt,
               
P2.title,
               
P2.body
       
FROM    SelectedKeys SK
       
JOIN    dbo.Post P2
               
ON  P2.post_id = SK.post_id
       
ORDER   BY
               
SK.post_id ASC;
END;

and here is the replacement procedure using paging:

CREATE  PROCEDURE dbo.FetchOffsetPageKeySeek
       
@PageSize   BIGINT,
       
@PageNumber BIGINT
AS
BEGIN
       
-- Key-Seek algorithm
       
WITH    SelectedKeys
       
AS      (
               
SELECT  post_id
               
FROM    dbo.Post P1
               
ORDER   BY post_id ASC
               
OFFSET  @PageSize * (@PageNumber-1) ROWS
               
FETCH NEXT @PageSize ROWS ONLY
               
)
       
SELECT  P2.post_id,
               
P2.thread_id,
               
P2.member_id,
               
P2.create_dt,
               
P2.title,
               
P2.body
       
FROM    SelectedKeys SK
       
JOIN    dbo.Post P2
               
ON  P2.post_id = SK.post_id
       
ORDER   BY
               
SK.post_id ASC;
END;

Notice how all i have done is replace the functionality with the Keys and SelectedKeys CTEs with the paging functionality.

So , what is the comparative performance now ?.

offsetfetch

Exactly the same amount of IO and memory usage , but its now pretty obvious that in terms of CPU and overall duration we are onto a winner.

 

 

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.