Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Optimising Server-Side Paging - Part I Expand / Collapse
Author
Message
Posted Saturday, April 24, 2010 11:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
Comments posted to this topic are about the item Optimising Server-Side Paging - Part I



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #909997
Posted Monday, April 26, 2010 1:16 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 27, 2013 12:29 AM
Points: 1, Visits: 6
Nice post ......but i have been using this paging before also....so i didn't get any difference......
Post #910210
Posted Monday, April 26, 2010 2:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 18, 2010 11:51 AM
Points: 1, 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)?
Post #910236
Posted Monday, April 26, 2010 2:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 11, 2013 3:47 PM
Points: 1, Visits: 8
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
Post #910238
Posted Monday, April 26, 2010 2:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910241
Posted Monday, April 26, 2010 3:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910250
Posted Monday, April 26, 2010 3:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910251
Posted Monday, April 26, 2010 3:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Plan.gif (510 views, 13.92 KB)
Post #910259
Posted Monday, April 26, 2010 6:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #910304
Posted Monday, April 26, 2010 6:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:43 AM
Points: 323, Visits: 2,199
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 ;)

Thx for the article in advance!
Post #910315
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse