Optimising Server-Side Paging - Part I

Question

Optimising Server-Side Paging - Part I

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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

pbarbin Old Hand Points: 362 More actions · Answer 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

Paul White SSC Guru Points: 150468 More actions · Answer 3

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 4

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

Paul White SSC Guru Points: 150468 More actions · Answer 5

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!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

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!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

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

Paul White SSC Guru Points: 150468 More actions · Answer 8

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
All articles available on SQL.kiwi
@SQL_Kiwi

jeremy.hutchinson SSC Enthusiast Points: 117 More actions · Answer 9

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 🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

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

Paul White SSC Guru Points: 150468 More actions · Answer 11

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Mark Stacey SSC-Addicted Points: 454 More actions · Answer 12

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

Paul White SSC Guru Points: 150468 More actions · Answer 13

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?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 14

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)

LutzM SSC Guru Points: 107049 More actions · Answer 15

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]