Click here to monitor SSC
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
AmolNaik
AmolNaik
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1234
Thanks Paul, I'll give it a try and test it out.

Amol Naik
tony.turner
tony.turner
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 180
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10352 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
tony.turner
tony.turner
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 180
Paul,

Thanks for your response :-)

Tony
AmolNaik
AmolNaik
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 1234
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
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