Post reply

The Most Common Query Blunders...

  • December 25, 2015 at 7:31 am

    #323533

    I've received a request from our VP of Enterprise Application Design (aka my boss) to do an hour-ish long presentation on SQL Server performance tuning. Rather than trying to distill a 500+ page book down to an a hour of me stammering, I figured it would be best to simply pick out a few of the most common performance killers and focus on those.

    Due to a recent debacle involving a proc that gets called several thousand times a day and a nested scalar udf... iTVF vs scalar & mTVFs is definitely on the agenda...

    I'm interested in knowing what other "Common Query Blunders / Performance Killers / Bad Habbits" the rest of you would talk about, if you were given this assignment.

    Basically, I'm looking for the low hanging fruit. The types of improvements that yield the best bang for the buck, so to speak.

    Any supporting articles and/or test procedures would also be appreciated... I'm not expecting anyone to take my word on anything... I'll need to be able to demonstrate the difference between the "right way" & "wrong way" with actual numbers.

    Thank you to everyone in advance. 🙂

  • December 25, 2015 at 9:55 am

    #1848036

    This certainy isn't a comprehensive list, but I think it's a decent start.

    While loops or cursors instead of set-based alternatives

    Non-SARGable predicates

    Multi statement table valued functions

    Using SELECT * everywhere

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • December 25, 2015 at 10:02 am

    #1848038

    How about I do a remote presentation of my Common TSQL Mistakes session? I have presented it somewhere around 100 times now for companies, User Groups, SQL Saturdays and other conferences. It is a AMAZING collection of magic bullets and bad-data causers, with lots of perf tuning hints thrown in along the way. Drop me a PM if you are interested. Depending on where you are I could even pop in for an onsite presentation of that and other sessions if your boss is interested. I love to travel and pick up Delta Qualification Miles! 🙂

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

  • December 25, 2015 at 10:05 am

    #1848039

    Kevin, just the way you phrased "bad-data causers" made me think of the horrible offense of storing dates and times in string columns.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • December 25, 2015 at 11:08 am

    #1848041

    Ed Wagner (12/25/2015)

    Kevin, just the way you phrased "bad-data causers" made me think of the horrible offense of storing dates and times in string columns.

    Or, like MS did in MSDB, storing them as integers.

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

  • December 25, 2015 at 12:19 pm

    #1848049

    Ed Wagner (12/25/2015)

    This certainy isn't a comprehensive list, but I think it's a decent start.

    While loops or cursors instead of set-based alternatives

    Non-SARGable predicates

    Multi statement table valued functions

    Using SELECT * everywhere

    Fortunately, "SELECT *" has been banned since before I showed up and I think we've done a decent job of of limiting the use of loops and cursors...

    Non-SARGable predicates is a huge one though. Definitely a keeper!

  • December 25, 2015 at 12:25 pm

    #1848051

    TheSQLGuru (12/25/2015)

    How about I do a remote presentation of my Common TSQL Mistakes session? I have presented it somewhere around 100 times now for companies, User Groups, SQL Saturdays and other conferences. It is a AMAZING collection of magic bullets and bad-data causers, with lots of perf tuning hints thrown in along the way. Drop me a PM if you are interested. Depending on where you are I could even pop in for an onsite presentation of that and other sessions if your boss is interested. I love to travel and pick up Delta Qualification Miles! 🙂

    I'll talk to the boss man and see if he's willing to part with some of the training budget. If he's interested, I'l touch base and find out what you'd charge.

  • December 25, 2015 at 12:41 pm

    #1848055

    Jason A. Long (12/25/2015)

    Ed Wagner (12/25/2015)

    This certainy isn't a comprehensive list, but I think it's a decent start.

    While loops or cursors instead of set-based alternatives

    Non-SARGable predicates

    Multi statement table valued functions

    Using SELECT * everywhere

    Fortunately, "SELECT *" has been banned since before I showed up and I think we've done a decent job of of limiting the use of loops and cursors...

    Non-SARGable predicates is a huge one though. Definitely a keeper!

    Here's one thing that you should talk about... the absolute belief in "best practices" even if millions of people subscribe to a given "best practice". "SELECT *" actually does have appropriate uses and it actually can provide better performance than discreet select-lists in many cases without causing the dangers that most people associate with "SELECT *".

    "Just because a million people are all doing/saying the same thing, doesn't necessarily mean it's the right thing". "Never mistake the wailing of the crowd for the wisdom of the group".

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

  • December 25, 2015 at 12:43 pm

    #1848056

    Jeff Moden (12/25/2015)

    Ed Wagner (12/25/2015)

    Kevin, just the way you phrased "bad-data causers" made me think of the horrible offense of storing dates and times in string columns.

    Or, like MS did in MSDB, storing them as integers.

    Fortunately, I was there to throw a conniption fit, when the warehouse guy started building the DW using integer "date keys" instead of dates... Apparently the Kimbal method recommends it...

    In any case, I was able to prove that there was no performance upside and no shortage of downsides... and our "Kimbal purest" got over ruled...

    We do have a few varchar dates but not many. Our former DBA, Don[/url], did a great job of drilling in the importance of using the correct data types.

  • December 25, 2015 at 12:44 pm

    #1848057

    Jason A. Long (12/25/2015)

    Ed Wagner (12/25/2015)

    This certainy isn't a comprehensive list, but I think it's a decent start.

    While loops or cursors instead of set-based alternatives

    Non-SARGable predicates

    Multi statement table valued functions

    Using SELECT * everywhere

    Fortunately, "SELECT *" has been banned since before I showed up and I think we've done a decent job of of limiting the use of loops and cursors...

    Non-SARGable predicates is a huge one though. Definitely a keeper!

    Here's one thing that you should talk about... not subscribing to the absolute belief in "best practices" even if millions of people subscribe to a given "best practice". "SELECT *" actually does have appropriate uses and it actually can provide better performance than discreet select-lists in many cases without causing the dangers that most people associate with "SELECT *".

    To wit...

    "Just because a million people are all doing/saying the same thing, doesn't necessarily mean it's the right thing".

    "Never mistake the consensus of the crowd for the wisdom of the group".

    And, my favorite... "Before you can think outside the box, you must first realize... you're in a box". --Jeff Moden (date unknown but somewhere in the early 2000's)

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

  • December 25, 2015 at 1:07 pm

    #1848060

    Jeff Moden (12/25/2015)

    Here's one thing that you should talk about... not subscribing to the absolute belief in "best practices" even if millions of people subscribe to a given "best practice". "SELECT *" actually does have appropriate uses and it actually can provide better performance than discreet select-lists in many cases without causing the dangers that most people associate with "SELECT *".

    To wit...

    "Just because a million people are all doing/saying the same thing, doesn't necessarily mean it's the right thing".

    "Never mistake the consensus of the crowd for the wisdom of the group".

    And, my favorite... "Before you can think outside the box, you must first realize... you're in a box". --Jeff Moden (date unknown but somewhere in the early 2000's)

    Kinda feels like a Pandora's box... I probably misspoke when I said "banned"... Nothing is actually banned per se...

    "The right tool for the job" argument, beats the "best practices" argument, every time...

    That said, I do think that a blatant divergence from known best practices, needs to be accompanied with an explanation in the comments.

  • December 25, 2015 at 1:15 pm

    #1848063

    Jason A. Long (12/25/2015)

    Jeff Moden (12/25/2015)

    Here's one thing that you should talk about... not subscribing to the absolute belief in "best practices" even if millions of people subscribe to a given "best practice". "SELECT *" actually does have appropriate uses and it actually can provide better performance than discreet select-lists in many cases without causing the dangers that most people associate with "SELECT *".

    To wit...

    "Just because a million people are all doing/saying the same thing, doesn't necessarily mean it's the right thing".

    "Never mistake the consensus of the crowd for the wisdom of the group".

    And, my favorite... "Before you can think outside the box, you must first realize... you're in a box". --Jeff Moden (date unknown but somewhere in the early 2000's)

    Kinda feels like a Pandora's box... I probably misspoke when I said "banned"... Nothing is actually banned per se...

    "The right tool for the job" argument, beats the "best practices" argument, every time...

    That said, I do think that a blatant divergence from known best practices, needs to be accompanied with an explanation in the comments.

    On your previous post, I loved DC's article on XML. And, on this post, I absolutely agree (you continue to hit favorite aggravations of mine)... comments are nearly as important as the code itself. I tell the folks that I work with that if you remove all of the code from a stored procedure, you should be able to at least draw a functional flowchart from the comments that remain. I'm a strong believer in "Write code for humans and data for computers".

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

  • December 25, 2015 at 1:31 pm

    #1848068

    I see these sooo many times: https://www.simple-talk.com/content/article.aspx?article=2280, plus scalar UDFs, multi-statement table-valued UDFs, nested views, non-SARGable predicates

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 25, 2015 at 1:37 pm

    #1848071

    I believe my favorite non-SARGable problems are due to the inappropriate use of "OR" and things like the following...

    WHERE ISNULL(SomeNumericColumn,0) > 0

    ...

    WHERE ISNULL(SomeCharacterColumn,' ') > ' '

    ... which, of course, is due to a simple lack of knowledge concerning NULLs and the fact that front-end code typically doesn't follow the same rules for NULLs as the soon-to-be-not-optional default rules that SQL Server follows.

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

  • December 25, 2015 at 2:00 pm

    #1848075

    Mine would have to be UPPER(LTRIM(RTRIM(<SomeColumn>) = @Parameter, in a case-insensitive database where none of the rows have leading whitespace.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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