Click here to monitor SSC
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
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: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Anthony Perkins
Anthony Perkins
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 622
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.
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: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Paul Muharsky-474732
Paul Muharsky-474732
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 153
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.
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: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Great article Paul. This is useful stuff.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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: 10338 Visits: 11350
CirquedeSQLeil (5/24/2010)
Great article Paul. This is useful stuff.

Appreciated Jason, thanks.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
jcaradocdavies
jcaradocdavies
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 31
Thanks, you've won me over.
tim.sewell
tim.sewell
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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
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: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
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