T-SQL: Why “It Depends”

Question

T-SQL: Why “It Depends”

The Dixie Flatline

SSC Guru

Points: 53354
More actions
March 16, 2010 at 12:00 am

#230873

Comments posted to this topic are about the item T-SQL: Why “It Depends”
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 15 posts - 1 through 15 (of 98 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Nicely done. Lots of people just don't understand that data volumes can REALLY alter performance of existing code, and can also make the optimizer take different approaches to queries that can dramatically affect performance as well - even in a NEGATIVE manner.

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

Hi Bob

I'm a little bit in a hurry at the moment, so only a very short feedback:

Great job!

Thanks

Flo

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 3

Bob

Really good article - like it. Now where's the +1 button on this forum? 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Paul White SSC Guru Points: 150467 More actions · Answer 4

Excellent stuff Bob. The first article on SSC I have really enjoyed reading for quite some time. Well done!

It is a shame about some of the code formatting problems (missing spaces, mostly). I am sure those errors were not in the article you submitted.

The other layout thing is that the "Retrieving Detail of Last Sale(s) Of All Products" section is duplicated, and the section inserting the sample data is missing.

Hopefully these things can be corrected quickly, to avoid detracting from the otherwise excellent-ness of the thing.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Nice job Bob, very thorough as would be expected. Glad to see yo back in the saddle so to speak. I know the article was probably written awhile ago, but you've been missed on the forums.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 6

Excellent article. You made several points and made them well, not the least illustrating why "it depends" is such a common statement.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Thanks, guys.

Your comments did me more good this morning than my first cup of coffee (which I have yet to pour).

Flo: I'm pleased you even found time to read this. I remember how busy you are this year.

Matt: Forgive my absence from ASK SSC[/url]. Paul could tell you that I've barely been participating in The Thread for the past few months. However I still wear my "Enlightened" badge proudly.

Paul: I will read over the article and see if Steve can make the changes. It's also been pointed out to me that a script to generate the sample data table really should have been included. Thanks, as always, for keeping me straight.

Jack: You're right. This was written back in November. Things have been hectic since. When is the next SQL Saturday in Orlando? I need a vacation.

Joe: We've never met, but I sat in on your presentation at the PASS Summit. I suspect the MAX() technique will do well in the time trials.

Grant: Thanks as well. I'm busting through your book on execution plans right now and can't wait to crack open the one on performance tuning.

Jeff: Simply thanks. I've told you before at length how much I owe you. It's good not to be savaged with a pork chop first thing in the morning. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

BlackHawk-17 SSChasing Mays Points: 628 More actions · Answer 8

Excellent.

Clear. Concise. To the point.

Great examples and presentation. All you needed to add was the infamous developer quote: "It worked faster in test."

Thanks for sharing!

Paul White SSC Guru Points: 150467 More actions · Answer 9

The Dixie Flatline (3/16/2010)
Paul: I will read over the article and see if Steve can make the changes. It's also been pointed out to me that a script to generate the sample data table really should have been included. Thanks, as always, for keeping me straight.

Thank you. I am as keen to try a recursive CTE as Joe is to try a MAX (PARTITION BY). Just for fun, y'know 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Scott Abrants SSCommitted Points: 1503 More actions · Answer 10

Scott Abrants

SSCommitted

Points: 1503

March 16, 2010 at 6:38 am

#1134381

Very nice work. Thank you for a well done article.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Excellent article, Bob. It should be required reading for newbies so that the understand why we tell them "It Depends" so often.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

The Dixie Flatline (3/16/2010)
Thanks, guys.
Jack: You're right. This was written back in November. Things have been hectic since. When is the next SQL Saturday in Orlando? I need a vacation.

Well, Orlando is always in late September or early October, but Jacksonville is May 8th. I can't make that one. I am probably going to go to the Atlanta SQLSaturday on April 27th. Be great to see you there.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

AndrewJacksonZA SSCarpal Tunnel Points: 4143 More actions · Answer 13

Very useful article concept/theme - especially for the newbies, illustrative and effective example, great username (although I trust that you're not a construct :-))

Dave62 SSCertifiable Points: 7459 More actions · Answer 14

Great job with the article and an excellent example of how the same code will perform differently under various data loads.

Clearly, the answer "it depends" can be justified as you have so well done. If the answer is expanded on, as well as you have just done, it would be much more appreciated by T-SQL new comers.

Example:

___It depends, under conditions a... answer 1.

___Under conditions b... answer 2.

___...

This way the new comer will actually get the "best" answer they are looking for if one of the conditions and corresponding answer relates to their situation.

Dave