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 Monday, May 24, 2010 9:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Anthony Perkins (5/24/2010)
Thank you for the very good article.

Thanks for reading it

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?

Yes, it matters.

The Keys CTE is all about finding the TOP (@PageNumber * @PageSize) rows in (observation_date, observation_hour, observation_minute) order. If we want page 5, where each page has 10 rows, the Keys CTE will return just 5 * 10 = 50 rows (in that defined order) for the next CTE to work on (logically speaking).

The ORDER BY in the ranking function only applies to the ranking function, so we cannot assume it affects the TOP operation in any way. The WHERE clause is just there to specify the overall restriction on the date range we are interested in, so that does not help either.

For more information the technical details of TOP with ORDER BY, see the following post by Connor Cunningham:
http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926935
Posted Monday, May 24, 2010 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:54 AM
Points: 8, Visits: 373
The ORDER BY in the ranking function only applies to the ranking function, so we cannot assume it affects the TOP operation in any way. The WHERE clause is just there to specify the overall restriction on the date range we are interested in, so that does not help either.

You are of course absolutely right. I stand corrected.

Thanks again.
Post #926968
Posted Monday, May 24, 2010 11:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Anthony Perkins (5/24/2010)
You are of course absolutely right. I stand corrected.

No worries. It was a reasonable question, and I would be very surprised if you were the only one to wonder about it.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #926982
Posted Monday, May 24, 2010 12:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 2:47 PM
Points: 32, Visits: 105
As I've mentioned on other posts that discuss server side paging, most of the examples I have seen deal with Single Table paging examples.

In most cases that I have seen, paging tends to be implemented on more complex search-type queries, with significantly more complex base execution plans. I would be interested in seeing a comparison of the various methods based on a root query with 3-4 joins as a mix of inner and outer, and possibly some other filter operations (IN, LIKE) to mess with the execution plans.

I like the idea of an indexed view, and in those rare cases where I get to build the system from scratch, and design for thier later use, this is an excellent approach. However, I rarely get to do that as a consultant. More often, I am brought in to address an existing performance problem because the people who designed and wrote the system didn't know what they were doing, and most often don't even know what an execution plan is, much less have built a system that meets the strict pre-requisites of implementing indexed views.

As is, based on the article (which is well written and well researched, by the way), I can take a way a great way of handling a milllion+ single table paging solution. Though in the many years I have spent as a DBA, I have rarely run into this case. Thanks for the ideas and the research though, and hope to see more in the future.
Post #927035
Posted Monday, May 24, 2010 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Paul Muharsky-474732 (5/24/2010)
As I've mentioned on other posts that discuss server side paging, most of the examples I have seen deal with Single Table paging examples.

The examples provided in Part I and Part II are simplified for several reasons, including ease of access for those new to the subject, and space constraints imposed by SSC.

The concepts do apply to more complex scenarios, and I will be addressing at least one of those in future parts. As I said in an earlier response, there will be at least one more part to this series, as I am keen to cover more complex base queries and dynamic search and ordering conditions. The challenge, of course, is to keep it interesting and relevant to as wide an audience as possible.

I do not intend to duplicate the fine work of Erland Sommarskog, but as Kevin Boles pointed out in the discussion for Part I, the Key Seek concept translates well to more complex requirements. Kevin, as you may know, is a SQL Server MVP of long standing and a very well-known and experienced consultant.

If you have any specific ideas that you would like to see me cover, please let me know.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #927040
Posted Monday, May 24, 2010 1:42 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:09 PM
Points: 21,252, Visits: 14,960
Great article Paul. This is useful stuff.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #927049
Posted Monday, May 24, 2010 1:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
CirquedeSQLeil (5/24/2010)
Great article Paul. This is useful stuff.

Appreciated Jason, thanks.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #927051
Posted Tuesday, May 25, 2010 12:48 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
Thanks, you've won me over.
Post #927225
Posted Tuesday, November 9, 2010 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 19, 2013 2:55 PM
Points: 3, Visits: 148
Great article. I have been trying to implement your ideas but have come unstuck with what I am doing. The issue revolves around implemenmting the key seek method when trying to get distinct results from the first query. ie

with Keys as (
SELECT distinct
....
)

when using distinct you can't use the row_number over mothod as you don't get sequential results, though this can be resolved by adding another section below the keys part and moving the row_number to that.

distinctKeys AS
(
SELECT TOP (@page * @recsPerPage)
rn = ROW_NUMBER() OVER (ORDER by '+@SQLOrderByTemp+')
FROM keys sk
)

With that working the problem is taht I can't figure out how to get the count of keys quickly. Because I am using the distinct in a way that means that I can't just get the pk back but have to use a number of columns you can't guarantee that the count will match the select.

The only way to do this seems to ve to move the count sub query to the distinctKeys section and remove the TOP part of the Keys section. This works but obviously I am now having to get all the records back from keys which is not very efficient on large sets.

I hope that makes sense! Any help on this would be much appreciated.

cheers

Tim




Post #1017973
Posted Tuesday, November 9, 2010 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
Hi Tim,

I almost see what you're getting at, but it would make it a lot easier to help if you could provide a CREATE TABLE statement, and a few rows of sample data to illustrate.

Cheers,

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1018033
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse