Denali Paging–Key seek lookups
- by Dave Ballantyne
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 ?. 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.
