Optimising Server-Side Paging - Part I

  • Paul - very nice article. Looking forward to the next ones...

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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.

    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.

    Paul

  • pbarbin (4/26/2010)


    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.

    Cool. Thanks.

    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.

    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.

  • Nice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • Trey Staker (4/26/2010)


    Nice article. I'm looking forward to the next 2 installments. I like how indepth you go with the query plans. Thanks.

    Thanks Trey - I do try to keep the articles focussed, but I *do* love query plans!

  • WayneS (4/26/2010)


    Paul - very nice article. Looking forward to the next ones...

    Thanks Wayne I appreciate it - especially since you know how much effort goes into writing these things!

  • 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. 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!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • TheSQLGuru (4/26/2010)


    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.

    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.

    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!

    Don't give everything away! I have to keep some surprises for parts II and III!

    BTW, another great article Paul!

    Cheers Kevin, that means a lot.

  • Paul White NZ (4/26/2010)


    desade (4/26/2010)


    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

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

    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 🙂

  • Oopsie! Should have held my comments for another week or so - then it would have been considered a good segue into the next article. :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • jeremy.hutchinson (4/26/2010)


    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.

    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!

    Great article, looking forward to the next instalment 🙂

    Thanks.

  • 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

  • Mark Stacey (4/26/2010)


    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

    Do you mean the covering index I refer to in the article? The one that would be a complete copy of the table?

  • Another stunning example of how an article should be written as well as how to back up all claims with code. Very well done, Paul.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Wow! 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!



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 15 posts - 16 through 30 (of 55 total)

You must be logged in to reply to this topic. Login to reply