SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Optimising Server-Side Paging - Part I


Optimising Server-Side Paging - Part I

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
Comments posted to this topic are about the item Optimising Server-Side Paging - Part I



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
gitendrashah.net
gitendrashah.net
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 6
Nice post ......but i have been using this paging before also....so i didn't get any difference......
Aleksandar Cebov
Aleksandar Cebov
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 13
Hmmm... it seems to me that this will work only if where statement is not being used. What if we need to do server side paging and sorting and apply filter on which rows should be returned (maybe in the example I need to get only those records where the thread_id=some_value and sort them by create_dt)?
desade
desade
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
any idea why key seek method uses much more CPU for 10 pages then it uses for 100 or 200? It looks as some glitch in testing, but maybe there is logical explanation
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
Aleksandar Cebov (4/26/2010)
Hmmm... it seems to me that this will work only if where statement is not being used.

It works whenever there is a useful index to obtain the keys, in the required order.

What if we need to do server side paging and sorting and apply filter on which rows should be returned (maybe in the example I need to get only those records where the thread_id=some_value and sort them by create_dt)?

The appropriate Key-Seek index in that case would be on (thread_id, create_dt). There is a full reproduction script included with the article. I encourage you to download it, and experiment for yourself.

Custom sorting and filtering is covered in more depth in part III - this first part is just about establishing the basic method.

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
desade (4/26/2010)
any idea why key seek method uses much more CPU for 10 pages then it uses for 100 or 200? It looks as some glitch in testing, but maybe there is logical explanation

There's no special reason that I am aware of - the test results are shown exactly as they appeared. I just put it down to the small numbers involved, the limited timing resolution available, and random chance...



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
gitendrashah.net (4/26/2010)
Nice post ......but i have been using this paging before also....so i didn't get any difference......

Well hopefully you found something interesting or useful - if not, perhaps you will in parts 2 and 3.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
Aleksandar Cebov (4/26/2010)
What if we need to do server side paging and sorting and apply filter on which rows should be returned (maybe in the example I need to get only those records where the thread_id=some_value and sort them by create_dt)?

Ok, here's some code to demonstrate what I meant in my previous reply:


-- Key Seek index
CREATE INDEX [IX dbo.Post thread_id, create_dt]
ON dbo.Post (thread_id, create_dt);

DECLARE @PageNumber BIGINT, -- Page number to fetch
@PageSize BIGINT; -- Rows per page

SET @PageSize = 50;
SET @PageNumber = 10;

-- The thread_id to filter on
DECLARE @ThreadID INTEGER;
SET @ThreadID = 6;

-- 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.create_dt ASC),
P1.post_id,
P1.create_dt
FROM dbo.Post P1
WHERE P1.thread_id = @ThreadID
ORDER BY
P1.create_dt 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,
SK.create_dt
FROM Keys SK
WHERE SK.rn > ((@PageNumber - 1) * @PageSize)
ORDER BY
SK.create_dt 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.create_dt ASC
OPTION (RECOMPILE);



Actual execution plan:




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Plan.gif (561 views, 13.00 KB)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36972 Visits: 11361
That query plan highlights something I took out of the article at the last minute - to keep things simple.
You might wonder why SQL Server chooses a non-clustered index seek and key lookup, rather than a clustered index seek in the last step.

The answer is again down to the width of the clustered index: for a relatively small number of rows, the cost of the seek + lookup is less than scanning even a very small range on the cluster.

For larger page sizes, SQL Server may choose a partial scan of the cluster, due to the mounting cost of the random I/O associated with the lookups.

A final point: notice that the final loop joins to perform the seek and lookup both have the WithOrderedPrefetch: True and Optimized: False attributes. This is a read-ahead optimization, looking ahead in the index and issuing asynchronous I/O for rows that will be needed for the joins. More details:

http://blogs.msdn.com/craigfr/archive/2008/10/07/random-prefetching.aspx



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
peter-757102
peter-757102
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 2559
I scanned the article quicly and it is well presented.

But I do have to dive into this particular one as I feel something odd is happening. The optimized statement is still just one statement and the steps in the with clause are no more then inline views. I am interested in why this construct manages to forces a way better execution plan.

No time no to go deep into this, but I will Wink

Thx for the article in advance!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search