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


Optimising Server-Side Paging - Part II


Optimising Server-Side Paging - Part II

Author
Message
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
Comments posted to this topic are about the item Optimising Server-Side Paging - Part II



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3439 Visits: 3934
Hi Paul

Hard stuff for sunday, but a really great article! :-)

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
jcaradocdavies
jcaradocdavies
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 31
Fantastic work, great analysis.

If I may make the following observation: the Indexed View solution simply defers calculation from the SELECT query to the UPDATE/INSERT/DELETE queries that edit the source data, so we have simply moved the problem from an isolated domain (getting a count as part of a particular use case or procedure) to one that is not always obvious - any time when the unerlying data is modified.

What is the performance hit when the base table(s) are updated? Do ALL aggregates have to be recalculated? This is perhaps not ideal for a transactional database, but great for a data mart scenario in which updates are well defined and can be performance tuned.

IMHO the lack of paging support in SQL is a glaring omission, and the solutions that exist are all, in some measure, hacks. I guess the problem is, to some degree, moot - but that's another discussion!

Thanks again.
Ioannis Tsakpinis
Ioannis Tsakpinis
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 275
Hey Paul and thanks for this series, both articles were great.

I think the double-sort situation in the Double Row Number method can be avoided if you simply swap the ROW_NUMBER() OVER statements. That is, calculate the descending order first and then the ascending (or the opposite if you want last-to-first paging). I tried this on a paging query in our application (SQL Server 2005) and it works great, one of the two sorts is gone.
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
Florian Reischl (5/23/2010)
Hi Paul
Hard stuff for sunday, but a really great article! :-)

Thanks Flo - I appreciate it.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
jcaradocdavies (5/24/2010)
Fantastic work, great analysis.

Thank you.

If I may make the following observation: the Indexed View solution simply defers calculation from the SELECT query to the UPDATE/INSERT/DELETE queries that edit the source data, so we have simply moved the problem from an isolated domain (getting a count as part of a particular use case or procedure) to one that is not always obvious - any time when the unerlying data is modified.

That is a valid observation; however, this sort of pre-aggregation (or denormalisation, if you like) has been a useful optimisation for a very long time. Many (most?) systems read data much more often than they write it, so it can make sense to compute the value once (at write time) rather than many times (each time the SELECT is run).

In the past, these aggregates would have been maintained using triggers. Indexed views offer many benefits over triggers, not least of which is that SQL Server does all the maintenance for you. The maintenance is performed as part of the INSERT/UPDATE/DELETE/MERGE plan, rather than in a separate batch as for trigger code.

I won't bore you with a full discussion of the advantages of indexed views here, but it is useful to note that the optimiser in Enterprise Edition can take advantage of the aggregated view data in existing (and future) queries that do not reference the view directly, but would nevertheless benefit from using it - see Designing Indexed Views for more details on that feature.

What is the performance hit when the base table(s) are updated? Do ALL aggregates have to be recalculated? This is perhaps not ideal for a transactional database, but great for a data mart scenario in which updates are well defined and can be performance tuned.

SQL Server will only maintains the row-level aggregates which are affected by the change - and in a way that is lighter than using a trigger. Maintaining the view is very efficient and pretty cheap, especially compared to the cost of making the data change in the first place. I have personally used indexed views with great benefits on extremely busy OLTP-type systems (a large auction site for example).

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
Ioannis Tsakpinis (5/24/2010)
Hey Paul and thanks for this series, both articles were great.

Thanks, I'm glad you enjoyed them. There will be at least one more in this series.

I think the double-sort situation in the Double Row Number method can be avoided if you simply swap the ROW_NUMBER() OVER statements. That is, calculate the descending order first and then the ascending (or the opposite if you want last-to-first paging). I tried this on a paging query in our application (SQL Server 2005) and it works great, one of the two sorts is gone.

This is true, and something that I missed when writing the test rig. Nonetheless, even one full sort of the full million-row set is enough to make the double-row-number method a poor choice in my view. Thanks for pointing this out though!

Paul



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sbrownell 30624
sbrownell 30624
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
To what extent can your results be applied to SQL Azure? Will the indexed view method work there?
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15508 Visits: 11354
sbrownell 30624 (5/24/2010)
To what extent can your results be applied to SQL Azure? Will the indexed view method work there?

The honest answer is: I have absolutely no idea, since I have no familiarity with that product at all.
Perhaps someone else will be able to help here?

edit: Just found the following reference that shows that indexed views are available on SQL Azure:
CREATE INDEX (SQL Azure Database)



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Anthony Perkins
Anthony Perkins
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 657
Thank you for the very good article.

One question I have, has to do with the need for an ORDER BY clause for the overall SQL statement in the first CTE (Keys). Yes, I understand that using a TOP without an ORDER BY does not guarantee results, however the combination of the ORDER BY in the Window function and the WHERE Clause should take care of that.

Is there something I am missing? Does it even matter?
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