InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 23:52:18 GMT20http://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxHey Paul,Just wanted to let you know the App Development team came back to me saying the pagination is lightning fast using the TOP (@var) approach. They are going to go ahead and start implementing this as a pilot project.Thanks for the informative article and eagerly waiting for the next one's :-)Tue, 18 May 2010 11:37:48 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxPaul,Thanks for your response :-)TonyFri, 14 May 2010 05:48:16 GMTtony.turnerhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxTony,The article presents a general model for paging, in particular one which makes it fast to return [i]any[/i] page from a set of any size. If you have a system which requires access only to the first/last/previous/next page, you may find the 'anchor' model works just fine.PaulThu, 13 May 2010 23:45:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxAs I understand the original article, and please correct me if I am wrong* It addresses a particular paging model, where the number of items in the paging set is pre-determined, and you can position to any page within the paging set. There are other paging models (first, last, next, previous) where next is determined as being higher than the highest key on the current page (where the clustered index may work just fine), previous is determined as being the highest keys lower than the lowest key on the current page (you probably need a descending key to optimise this), etc* The performance issue can be generalised as a wide index (where a clustered index can be seen as an index with all non-key columns included as included columns) being unsuitable for full index scans, or partial index scans where the range is large. The paging model discussed in the article requires a relatively large scan for each page position* The essence of your article is to describe a paging model implementation that works significantly more effectively if you have a clustered index over a wide table; it does not specifically advocate unclustered tablesWhy is it important to me to get clarity on this? The original article is being read in some circles as indicating that there is a severe general performance penalty on clustered indices; and that existing (up to 600GB) unclustered tables should remain so. The application accessing these tables has no requirement for paging in the sense described in the article Whether I like it or not, I am involved in the clustered/unclustered religious war. However, in this case, I am asking whether I understood the import of the article as the author intended it to be understood Thu, 13 May 2010 03:45:43 GMTtony.turnerhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxThanks Paul, I'll give it a try and test it out.Wed, 12 May 2010 22:02:47 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Amol Naik-681410 (5/12/2010)[/b][hr]I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imaging if there are several thousand records, SQL Server still has to order those records using ROW_NUMBER(). Wouldn't the performance suffer at some point of time? What is your opinion? Right now there is a debate within our DBA team to implement this or not because of performance issues.[/quote]You will probably have noticed that SQL Server does not have to do any ordering - there are no Sort operators in the query plans shown - the index provides the order.The Key Seek method, properly applied, is one of the fastest ways I know of to page a result set. I have used this method on large sets of data on massively concurrent systems. Properly configured, it is extremely hard to beat.Wed, 12 May 2010 21:53:50 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxPaul,I always had this question in mind, isn't there a performance penalty in using this approach on the DB side. Imagine if there are several thousand records, SQL Server still has to order those records using ROW_NUMBER(). Wouldn't the performance suffer at some point of time? What is your opinion? Right now there is a debate within our DBA team to implement this or not because of performance issues.Thanks in advance,Wed, 12 May 2010 11:07:09 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Amol Naik-681410 (5/11/2010)[/b][hr]Brilliant article and the timing is so right. I have had code being sent by App Developer today to develop a paged recordset using stored procedures. Just began to work on this project when i got an opportunity of going over your article, tested out the pagination approach using your code vs the one provided by the App developers and there is huge performance gain in using your code. Thanks for sharing this approach.[/quote]Thank you, I'm glad you found it useful.[quote]One question is do we need to really use the second CTE 'Selected Keys', coz even w/o using that i got the same results since we are already doing a TOP in the first CTE?[/quote]It is always worth giving the SQL Server optimiser as much information as possible, since this can only help it to come up with a good execution plan in a wide set of circumstances. A similar question was asked a little while ago in this discussion thread, so I'll refer you to the answer I gave there: [u][url]http://www.sqlservercentral.com/Forums/FindPost915047.aspx[/url][/u]PaulWed, 12 May 2010 01:25:34 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxBrilliant article and the timing is so right. I have had code being sent by App Developer today to develop a paged recordset using stored procedures. Just began to work on this project when i got an opportunity of going over your article, tested out the pagination approach using your code vs the one provided by the App developers and there is huge performance gain in using your code.Thanks for sharing this approach.One question is do we need to really use the second CTE 'Selected Keys', coz even w/o using that i got the same results since we are already doing a TOP in the first CTE?Thanks,Tue, 11 May 2010 13:42:20 GMTAmolNaikhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][quote]Strangely, when executing your FetchPageKeySeek and my version above, whichever runs second runs the fastest. I'm running on SQL Server 2008 (10.0.2531) from SSMS.[/quote]This is the effect of cached data pages. The downloadable code in the Resources section provides a full test rig which eliminates this problem and makes a fairer comparison.[/quote]I was using the test rig, and running your ClearCaches sp after each test as well. I may bust out the profiler and try to see what's going on. I should note the difference was in the 10ms range, so it's perhaps not too significant.Tue, 04 May 2010 09:46:55 GMTtobyteelhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Florian Reischl (5/4/2010)[/b][hr]Thanks a bunch! Sadly I had no time over the last three month to publish some new things.(Just started in a new company - yesterday)[/quote]That's ok - quality over quantity :-)Congratulations on the new position!Tue, 04 May 2010 08:48:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Paul White NZ (5/3/2010)[/b][hr][quote][b]Florian Reischl (5/1/2010)[/b][hr]Hey PaulThanks for sharing this great article!Best wishes,Flo[/quote]Hey Flo, thanks very much! I'm a big fan of your blog by the way.[/quote]Hey PaulThanks a bunch! Sadly I had no time over the last three month to publish some new things.(Just started in a new company - yesterday).GreetsFloTue, 04 May 2010 08:25:30 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]TheSQLGuru (5/4/2010)[/b][hr]Can you give a link to Florian's blog please? That's gotta be a keeper![/quote]It's [url]http://florianreischl.blogspot.com/[/url] but I apologise in advance - I have a amateur interest in SQLCLR and C# in particular, and Flo writes some good stuff bridging the gap between .NET and SQL Server. May not be for everyone, but please do check it out.PaulTue, 04 May 2010 08:00:03 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Paul White NZ (5/3/2010)[/b][hr][quote][b]Florian Reischl (5/1/2010)[/b][hr]Hey PaulThanks for sharing this great article!Best wishes,Flo[/quote]Hey Flo, thanks very much! I'm a big fan of your blog by the way.[/quote]Can you give a link to Florian's blog please? That's gotta be a keeper!Tue, 04 May 2010 07:44:03 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]tobyteel (5/3/2010)[/b][hr]What's the point of the second CTE in your Key Seek solution? It seems like that could easily be incorporated into the final select statement. Seems to run with the exact same IO. Query execution plan simply moves the Top to the end of the plan from the middle. Why the added complexity? I tried 500 results on page 10, and 50 results on page 100.[/quote]Three reasons:1. I intended it to be clearer for those new to the idea to follow what was happening and why.2. Separating the steps out might make for easier maintenance. Compacting the code makes it less readable in my view, without benefiting the execution plan.3. Having the TOP operator before the look-ups tells the optimiser that a maximum of @PageSize look-ups will be performed. The query may optimise differently because of this. There was a small discussion previously concerning why it is better to give the optimiser all the information you can. With the TOP at the end, if there were other iterators (such as a filter) after the look-ups, the optimiser might choose a sub-optimal plan for the look-ups based on an incorrect cardinality estimation. The difference between a @PageSize row goal (TOP at the end) and a guaranteed maximum (TOP before the look-ups) is fairly subtle, but can be important.[quote]Strangely, when executing your FetchPageKeySeek and my version above, whichever runs second runs the fastest. I'm running on SQL Server 2008 (10.0.2531) from SSMS.[/quote]This is the effect of cached data pages. The downloadable code in the Resources section provides a full test rig which eliminates this problem and makes a fairer comparison.[quote]Also, for those doing their own benchmarks: I find that SSMS's rendering of the resultsets take a significant amount of time. To control against it, I insert the results into a temp table instead.[/quote]True. For a larger number of rows I tend to do the same myself. Feel free to modify the test rig to do this if you wish.[quote]Great article though. Can't wait for the next installments. Any ETA?[/quote]Part II was accepted for publication a week or so ago. Just waiting for Steve to schedule it. Are you listening, Steve? :-PMon, 03 May 2010 20:32:54 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Florian Reischl (5/1/2010)[/b][hr]Hey PaulThanks for sharing this great article!Best wishes,Flo[/quote]Hey Flo, thanks very much! I'm a big fan of your blog by the way.Mon, 03 May 2010 20:19:24 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxWhat's the point of the second CTE in your Key Seek solution? It seems like that could easily be incorporated into the final select statement.[code="sql"]WITH Keys AS ( -- Step 1 : Number the rows from the non-clustered index -- Maximum number of rows = @PageNumber * @PageSize SELECT TOP (@PageNumber * @PageSize) rn = ROW_NUMBER() OVER (ORDER BY P1.post_id ASC), P1.post_id FROM dbo.Post P1 ORDER BY P1.post_id ASC ) SELECT TOP (@PageSize)-- Step 3 : Retrieve the off-index data -- We will only have @PageSize rows by this stage SK.rn, P2.post_id, P2.thread_id, P2.member_id, P2.create_dt, P2.title, P2.body FROM Keys SK JOIN dbo.Post P2 ON P2.post_id = SK.post_id WHERE SK.rn > ((@PageNumber - 1) * @PageSize) ORDER BY SK.post_id ASC;[/code]Seems to run with the exact same IO. Query execution plan simply moves the Top to the end of the plan from the middle. Why the added complexity? I tried 500 results on page 10, and 50 results on page 100. Strangely, when executing your FetchPageKeySeek and my version above, whichever runs second runs the fastest. I'm running on SQL Server 2008 (10.0.2531) from SSMS.Also, for those doing their own benchmarks: I find that SSMS's rendering of the resultsets take a significant amount of time. To control against it, I insert the results into a temp table instead.Great article though. Can't wait for the next installments. Any ETA?Mon, 03 May 2010 15:56:17 GMTtobyteelhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxHey PaulThanks for sharing this great article!Best wishes,FloSat, 01 May 2010 09:20:13 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxNice article Paul. Thanks for sharing.Wed, 28 Apr 2010 10:40:38 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Paul White NZ (4/27/2010)[/b][hr][quote][b]ondrej.bouda (4/27/2010)[/b][hr]Thats a good point of view: Tell to the optimizer all that you can know.[/quote]Absolutely right![/quote]+2!!Tue, 27 Apr 2010 07:29:44 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]ondrej.bouda (4/27/2010)[/b][hr]Thats a good point of view: Tell to the optimizer all that you can know.[/quote]Absolutely right!Tue, 27 Apr 2010 06:19:16 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxThanks!I checked my execution plans and it already makes the difference - the plan with TOP is much more accurate in the estimated number of rows. Thats a good point of view: Tell to the optimizer all that you can know. There's no need to care whether some instruction might be useless (because the optimizer might be smart enough to know it too).Tue, 27 Apr 2010 06:13:46 GMTondrej.boudahttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]ondrej.bouda (4/27/2010)[/b][hr]I wonder if the TOP specification has any special impact (as claimed) on the query execution - I tried to omit it (replacing it with WHERE PG.rn BETWEEN lowRn AND hignRn) and the IO statistics were the same. Query plans are nearly identical: both contain Filter and Top side by side, just in reversed order. Tried in 2005 and 2008 with the same results.[/quote]I did make that claim, so I had better back it up :-)My preference here is to always use an extra TOP, since it gives the query optimiser [i]explicit[/i] information about the maximum number of rows than can flow past that iterator. Giving the QO more and better information is pretty much always a good thing.Using BETWEEN is not quite the same thing. It might be obvious to you, as a human, that BETWEEN 1 AND 10 (for example) will return 10 rows at most - but the QO can't currently make that logical leap. It is not able to trace the 'rn' column back to the ROW_NUMBER ranking function, and infer the same guarantee you do.So, your change subtly introduces scope for a cardinality estimation error: compare the execution plans - the QO has to make a guess (currently 9% selectivity) for the BETWEEN expression. This results in an estimate of 900 rows for the last page of the 10,000-row sample. (For larger data sets, the error would obviously be much larger.)Clearly this is wrong: we know that a page can have at most @PageSize rows. Using the extra TOP lets the optimiser have that information too.Using BETWEEN therefore introduces scope for a sub-optimal plan choice after the Filter iterator - something that we can easily avoid, with some good old defensive programming.Hope that helps.Tue, 27 Apr 2010 05:43:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxI wonder if the TOP specification has any special impact (as claimed) on the query execution - I tried to omit it (replacing it with WHERE PG.rn BETWEEN lowRn AND hignRn) and the IO statistics were the same.Query plans are nearly identical: both contain Filter and Top side by side, just in reversed order.Tried in 2005 and 2008 with the same results.Tue, 27 Apr 2010 04:22:01 GMTondrej.boudahttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxThank you Jeff, Lutz, and Tom.No pressure then ;-)Mon, 26 Apr 2010 22:21:48 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxA brilliant article. Keep it up!Mon, 26 Apr 2010 19:08:42 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxWow! You didn't only set a very high standard with your first article (CROSS APPLY) - you continue to hold that level with each and every article since! Once again: excellent job!Mon, 26 Apr 2010 13:16:04 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxAnother stunning example of how an article should be written as well as how to back up all claims with code. Very well done, Paul.Mon, 26 Apr 2010 11:44:09 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Mark Stacey (4/26/2010)[/b][hr]I'd be interested to see the results of the covering index in parallel, just to set a benchmark. One day when I'm bored I'll take a stab at it[/quote]Do you mean the covering index I refer to in the article? The one that would be a complete copy of the table?Mon, 26 Apr 2010 10:29:01 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxI'd be interested to see the results of the covering index in parallel, just to set a benchmarkOne day when I'm bored I'll take a stab at itMon, 26 Apr 2010 10:25:49 GMTMark Staceyhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]jeremy.hutchinson (4/26/2010)[/b][hr]Whenever I'm testing the performance of something I will always try to do a number of test runs, as many as possible in the allowable time, and use the average +/- the standard deviation to determine if my "improvements" have actually improved performance, especially when measuring CPU and elapsed time which are subject to outside influence.[/quote]Yes, I agree - and I did. To be as fair as possible, I took all the readings from every category from the same batch of runs - but the figures came out as I show them. I was not prepared to 'adjust' the figures just to make the graph pretty![quote]Great article, looking forward to the next instalment :-)[/quote]Thanks.Mon, 26 Apr 2010 10:05:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxOopsie! Should have held my comments for another week or so - then it would have been considered a good segue into the next article. :hehe:Mon, 26 Apr 2010 09:56:54 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Paul White NZ (4/26/2010)[/b][hr][quote][b]desade (4/26/2010)[/b][hr]any idea why key seek method uses much more CPU for 10 pages then it uses for 100 or 200? It looks as some glitch in testing, but maybe there is logical explanation[/quote]There's no special reason that I am aware of - the test results are shown exactly as they appeared. I just put it down to the small numbers involved, the limited timing resolution available, and random chance...[/quote]Whenever I'm testing the performance of something I will always try to do a number of test runs, as many as possible in the allowable time, and use the average +/- the standard deviation to determine if my "improvements" have actually improved performance, especially when measuring CPU and elapsed time which are subject to outside influence.Great article, looking forward to the next instalment :-)Mon, 26 Apr 2010 09:56:30 GMTjeremy.hutchinsonhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]TheSQLGuru (4/26/2010)[/b][hr]I have done this type of thing for numerous clients, with stunning results as you show. Actually usually better, because they are almost always encountered in multi-table-open-ended search scenarios.[/quote]Absolutely - yes. I had to keep the example in the article simple (it's always a compromise) but I hope folks get the broader idea behind Key Seek and find it natural extend it to multi-table scenarios. As you say, it is essential to great paging performance.[quote]I use dynamic SQL to generate the necessary joins/filters based on actual inputs. This has the significant advantage of only touching the tables required for the given input parameters. I have achieved greater than 5 orders of magnitude improvement in performance with this technique![/quote]Don't give everything away! I have to keep some surprises for parts II and III![quote]BTW, another great article Paul![/quote]Cheers Kevin, that means a lot.Mon, 26 Apr 2010 09:29:43 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxI have done this type of thing for numerous clients, with stunning results as you show. Actually usually better, because they are almost always encountered in multi-table-open-ended search scenarios. I use dynamic SQL to generate the necessary joins/filters based on actual inputs. This has the significant advantage of only touching the tables required for the given input parameters. I have achieved greater than 5 orders of magnitude improvement in performance with this technique!BTW, another great article Paul!Mon, 26 Apr 2010 09:22:47 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]WayneS (4/26/2010)[/b][hr]Paul - very nice article. Looking forward to the next ones...[/quote]Thanks Wayne I appreciate it - especially since you know how much effort goes into writing these things!Mon, 26 Apr 2010 09:15:20 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]Trey Staker (4/26/2010)[/b][hr]Nice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.[/quote]Thanks Trey - I do try to keep the articles focussed, but I *do* love query plans!Mon, 26 Apr 2010 09:14:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxNice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.Mon, 26 Apr 2010 09:10:08 GMTTrey Stakerhttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspx[quote][b]pbarbin (4/26/2010)[/b][hr]Paul, great post and even better timing. We had an issue with paging on a larger table just last week and I've spent a fair amount of time trying to just understand the problem. I've read the article once and I'm looking forward to working with the scripts to learn more.[/quote]Cool. Thanks. [quote]I've got other questions, but I'll wait until researching further before posting them. In the meantime, I'm interested to know if you or anyone else has comments on Entity Framework in general or in this specific case.[/quote]I'm pretty sure some of the regulars on the Forums have some strong views on this - can I ask you to post any specific questions there - you'll get a better response too. Thank you.Mon, 26 Apr 2010 08:59:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic909997-2669-1.aspxPaul, great post and even better timing. We had an issue with paging on a larger table just last week and I've spent a fair amount of time trying to just understand the problem. I've read the article once and I'm looking forward to working with the scripts to learn more. Our paging strategy is being implemented by our development team using Entity Framework to create the sql. That means the DBAs don't have much control over the generated Sql. Our fallback is to force developers to use stored procs. I've got other questions, but I'll wait until researching further before posting them. In the meantime, I'm interested to know if you or anyone else has comments on Entity Framework in general or in this specific case.Thanks.PaulMon, 26 Apr 2010 08:31:43 GMTpbarbin