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 «««23456

Optimising Server-Side Paging - Part I Expand / Collapse
Author
Message
Posted Wednesday, May 12, 2010 9:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #920967
Posted Wednesday, May 12, 2010 10:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
Thanks Paul, I'll give it a try and test it out.

Amol Naik
Post #920970
Posted Thursday, May 13, 2010 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 7:45 AM
Points: 25, Visits: 171
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


Post #921109
Posted Thursday, May 13, 2010 11:45 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #921769
Posted Friday, May 14, 2010 5:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 24, 2014 7:45 AM
Points: 25, Visits: 171
Paul,

Thanks for your response

Tony
Post #921926
Posted Tuesday, May 18, 2010 11:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 21, 2013 11:43 PM
Points: 945, Visits: 1,234
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
Post #923782
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse