Optimising Server-Side Paging - Part I

Question

Optimising Server-Side Paging - Part I

Viewing 11 posts - 46 through 55 (of 55 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Florian Reischl (5/4/2010)
Thanks a bunch! Sadly I had no time over the last three month to publish some new things.
(Just started in a new company - yesterday)

That's ok - quality over quantity 🙂

Congratulations on the new position!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tobyteel SSC Journeyman Points: 91 More actions · Answer 2

Strangely, when executing your FetchPageKeySeek and my version above, whichever runs second runs the fastest. I'm running on SQL Server 2008 (10.0.2531) from SSMS.
This is the effect of cached data pages. The downloadable code in the Resources section provides a full test rig which eliminates this problem and makes a fairer comparison.

I was using the test rig, and running your ClearCaches sp after each test as well. I may bust out the profiler and try to see what's going on. I should note the difference was in the 10ms range, so it's perhaps not too significant.

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 3

Brilliant article and the timing is so right. I have had code being sent by App Developer today to develop a paged recordset using stored procedures. Just began to work on this project when i got an opportunity of going over your article, tested out the pagination approach using your code vs the one provided by the App developers and there is huge performance gain in using your code.

Thanks for sharing this approach.

One question is do we need to really use the second CTE 'Selected Keys', coz even w/o using that i got the same results since we are already doing a TOP in the first CTE?

Thanks,

Amol Naik

Paul White SSC Guru Points: 150467 More actions · Answer 4

Amol Naik-681410 (5/11/2010)
Brilliant article and the timing is so right. I have had code being sent by App Developer today to develop a paged recordset using stored procedures. Just began to work on this project when i got an opportunity of going over your article, tested out the pagination approach using your code vs the one provided by the App developers and there is huge performance gain in using your code. Thanks for sharing this approach.

Thank you, I'm glad you found it useful.

One question is do we need to really use the second CTE 'Selected Keys', coz even w/o using that i got the same results since we are already doing a TOP in the first CTE?

It is always worth giving the SQL Server optimiser as much information as possible, since this can only help it to come up with a good execution plan in a wide set of circumstances. A similar question was asked a little while ago in this discussion thread, so I'll refer you to the answer I gave there: http://www.sqlservercentral.com/Forums/FindPost915047.aspx

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 5

Paul,

I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imagine if there are several thousand records, SQL Server still has to order those records using ROW_NUMBER(). Wouldn't the performance suffer at some point of time? What is your opinion? Right now there is a debate within our DBA team to implement this or not because of performance issues.

Thanks in advance,

Amol Naik

Paul White SSC Guru Points: 150467 More actions · Answer 6

Amol Naik-681410 (5/12/2010)
I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imaging if there are several thousand records, SQL Server still has to order those records using ROW_NUMBER(). Wouldn't the performance suffer at some point of time? What is your opinion? Right now there is a debate within our DBA team to implement this or not because of performance issues.

You will probably have noticed that SQL Server does not have to do any ordering - there are no Sort operators in the query plans shown - the index provides the order.

The Key Seek method, properly applied, is one of the fastest ways I know of to page a result set. I have used this method on large sets of data on massively concurrent systems. Properly configured, it is extremely hard to beat.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 7

Thanks Paul, I'll give it a try and test it out.

Amol Naik

tony.turner Right there with Babe Points: 783 More actions · Answer 8

As I understand the original article, and please correct me if I am wrong

* It addresses a particular paging model, where the number of items in the paging set is pre-determined, and you can position to any page within the paging set. There are other paging models (first, last, next, previous) where next is determined as being higher than the highest key on the current page (where the clustered index may work just fine), previous is determined as being the highest keys lower than the lowest key on the current page (you probably need a descending key to optimise this), etc

* The performance issue can be generalised as a wide index (where a clustered index can be seen as an index with all non-key columns included as included columns) being unsuitable for full index scans, or partial index scans where the range is large. The paging model discussed in the article requires a relatively large scan for each page position

* The essence of your article is to describe a paging model implementation that works significantly more effectively if you have a clustered index over a wide table; it does not specifically advocate unclustered tables

Why is it important to me to get clarity on this? The original article is being read in some circles as indicating that there is a severe general performance penalty on clustered indices; and that existing (up to 600GB) unclustered tables should remain so. The application accessing these tables has no requirement for paging in the sense described in the article

Whether I like it or not, I am involved in the clustered/unclustered religious war. However, in this case, I am asking whether I understood the import of the article as the author intended it to be understood

Paul White SSC Guru Points: 150467 More actions · Answer 9

Tony,

The article presents a general model for paging, in particular one which makes it fast to return any page from a set of any size. If you have a system which requires access only to the first/last/previous/next page, you may find the 'anchor' model works just fine.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tony.turner Right there with Babe Points: 783 More actions · Answer 10

tony.turner

Right there with Babe

Points: 783

May 14, 2010 at 5:48 am

#1167441

Paul,

Thanks for your response 🙂

Tony

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 11

Hey Paul,

Just wanted to let you know the App Development team came back to me saying the pagination is lightning fast using the TOP (@var) approach. They are going to go ahead and start implementing this as a pilot project.

Thanks for the informative article and eagerly waiting for the next one's 🙂

Amol Naik