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 123»»»

Optimising Server-Side Paging - Part II Expand / Collapse
Author
Message
Posted Saturday, May 22, 2010 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
Comments posted to this topic are about the item Optimising Server-Side Paging - Part II



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926453
Posted Sunday, May 23, 2010 10:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932
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
Post #926527
Posted Monday, May 24, 2010 1:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 9:44 AM
Points: 4, Visits: 30
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.

Post #926645
Posted Monday, May 24, 2010 2:28 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:41 PM
Points: 2, Visits: 190
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.
Post #926659
Posted Monday, May 24, 2010 6:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
Florian Reischl (5/23/2010)
Hi Paul
Hard stuff for sunday, but a really great article!

Thanks Flo - I appreciate it.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926787
Posted Monday, May 24, 2010 7:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926811
Posted Monday, May 24, 2010 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926814
Posted Monday, May 24, 2010 7:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 31, 2011 3:30 PM
Points: 1, Visits: 10
To what extent can your results be applied to SQL Azure? Will the indexed view method work there?
Post #926836
Posted Monday, May 24, 2010 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926857
Posted Monday, May 24, 2010 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 8, Visits: 361
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?
Post #926866
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse