SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul White / Optimising Server-Side Paging - Part II / Latest Posts

RE: Optimising Server-Side Paging - Part II

tim.sewell — Thu, 18 Nov 2010 02:52:38 GMT

Anyone got any thoughts on this?

RE: Optimising Server-Side Paging - Part II

tim.sewell — Tue, 09 Nov 2010 09:58:26 GMT

ha I knew that was coming. This is a very rudimentry example, but it gives you an idea of the issue--create table #test (clientID int,contactID int,firstname varchar(255),lastname varchar(255),linkID int)--insert into #test VALUES (1,1,'tim','s',1)--insert into #test VALUES (1,1,'tim','s',2)--insert into #test VALUES (1,1,'tim','s',2)--select * FROM #test;WITHkeys AS( SELECT DISTINCT contactID,firstname,lastname,linkID ,(SELECt count(*) FROM #test) as allrecords FROM #test)SELECT * FROM keysis that enough to make more sense of what I was saying?CheersTim

RE: Optimising Server-Side Paging - Part II

Paul White — Tue, 09 Nov 2010 09:37:56 GMT

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

RE: Optimising Server-Side Paging - Part II

tim.sewell — Tue, 09 Nov 2010 08:26:32 GMT

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. iewith 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.cheersTim

RE: Optimising Server-Side Paging - Part II

jcaradocdavies — Tue, 25 May 2010 00:48:36 GMT

Thanks, you've won me over.

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 13:44:14 GMT

[quote][b]CirquedeSQLeil (5/24/2010)[/b][hr]Great article Paul. This is useful stuff.[/quote]Appreciated Jason, thanks.

RE: Optimising Server-Side Paging - Part II

SQLRNNR — Mon, 24 May 2010 13:42:00 GMT

Great article Paul. This is useful stuff.

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 13:08:46 GMT

[quote][b]Paul Muharsky-474732 (5/24/2010)[/b][hr]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.[/quote]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

RE: Optimising Server-Side Paging - Part II

Paul Muharsky-474732 — Mon, 24 May 2010 12:56:07 GMT

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.

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 11:20:43 GMT

[quote][b]Anthony Perkins (5/24/2010)[/b][hr]You are of course absolutely right. I stand corrected.[/quote]No worries. It was a reasonable question, and I would be very surprised if you were the only one to wonder about it.

RE: Optimising Server-Side Paging - Part II

Anthony Perkins — Mon, 24 May 2010 10:53:44 GMT

[quote]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.[/quote]You are of course absolutely right. I stand corrected.Thanks again.

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 09:52:12 GMT

[quote][b]Anthony Perkins (5/24/2010)[/b][hr]Thank you for the very good article.[/quote]Thanks for reading it :-)[quote]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?[/quote]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:[url]http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx[/url]Paul

RE: Optimising Server-Side Paging - Part II

Anthony Perkins — Mon, 24 May 2010 08:21:53 GMT

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?

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 08:09:31 GMT

[quote][b]sbrownell 30624 (5/24/2010)[/b][hr]To what extent can your results be applied to SQL Azure? Will the indexed view method work there?[/quote]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:[url=http://msdn.microsoft.com/en-us/library/ee336275(v=MSDN.10).aspx]CREATE INDEX (SQL Azure Database)[/url]

RE: Optimising Server-Side Paging - Part II

sbrownell 30624 — Mon, 24 May 2010 07:43:19 GMT

To what extent can your results be applied to SQL Azure? Will the indexed view method work there?

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 07:20:57 GMT

[quote][b]Ioannis Tsakpinis (5/24/2010)[/b][hr]Hey Paul and thanks for this series, both articles were great.[/quote]Thanks, I'm glad you enjoyed them. There will be at least one more in this series.[quote]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.[/quote]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

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 07:12:29 GMT

[quote][b]jcaradocdavies (5/24/2010)[/b][hr]Fantastic work, great analysis.[/quote]Thank you.[quote]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.[/quote]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 [url=http://technet.microsoft.com/en-us/library/ms187864.aspx]Designing Indexed Views [/url] for more details on that feature.[quote]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.[/quote]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

RE: Optimising Server-Side Paging - Part II

Paul White — Mon, 24 May 2010 06:48:40 GMT

[quote][b]Florian Reischl (5/23/2010)[/b][hr]Hi PaulHard stuff for sunday, but a really great article! :-)[/quote]Thanks Flo - I appreciate it.

RE: Optimising Server-Side Paging - Part II

Ioannis Tsakpinis — Mon, 24 May 2010 02:28:55 GMT

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.

RE: Optimising Server-Side Paging - Part II

jcaradocdavies — Mon, 24 May 2010 01:20:29 GMT

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.

RE: Optimising Server-Side Paging - Part II

Florian Reischl — Sun, 23 May 2010 10:24:30 GMT

Hi PaulHard stuff for sunday, but a really great article! :-)GreetsFlo

Optimising Server-Side Paging - Part II

Paul White — Sat, 22 May 2010 12:53:35 GMT

Comments posted to this topic are about the item [B]Optimising Server-Side Paging - Part II[/B]