April 26, 2010 at 8:02 am
Paul - very nice article. Looking forward to the next ones...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 26, 2010 at 8:31 am
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
April 26, 2010 at 8:59 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 9:10 am
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
April 26, 2010 at 9:14 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 9:15 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 9:22 am
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
April 26, 2010 at 9:29 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 9:56 am
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 explanationThere'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 🙂
April 26, 2010 at 9:56 am
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
April 26, 2010 at 10:05 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 10:25 am
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
April 26, 2010 at 10:29 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 26, 2010 at 11:44 am
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
April 26, 2010 at 1:16 pm
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!
Viewing 15 posts - 16 through 30 (of 55 total)
You must be logged in to reply to this topic. Login to reply