T-SQL: Why “It Depends”

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    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

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    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

    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: 150442

    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.

  • Jack Corbett

    SSC Guru

    Points: 184381

    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: 396620

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    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

    Mr or Mrs. 500

    Points: 568

    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: 150442

    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 😉

  • Scott Abrants

    SSCommitted

    Points: 1503

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

  • Lynn Pettis

    SSC Guru

    Points: 442344

    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: 184381

    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

    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: 6670

    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

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

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