10 ways to abuse t-sql
DESCRIPTION
Ten common performance mistakes, coding errors, bad practices, and stuff "not to do" with T-SQL.TRANSCRIPT
10 Ways To Abuse T-SQLCommon performance
mistakes, coding errors, and how to prevent them
Tracy McKibbenDBA Supervisor, Senior SQL Server DBA
Pearson VUE
Blog: realsqlguy.com
Twitter: @RealSQLGuy
I’m not saying I’m Batman, I’m just saying that nobody has
ever seen me and Batman in the same room together...
10 Ways To Abuse T-SQL
• Procedural coding• User-defined functions• Views• SELECT *• Non-SARGable queries• Bandaids• Mismatched Data Types• NULL• Incorrect or unnecessary ordering• Single-row triggers
Procedural Coding
Symptoms include:
• loops
• cursors
• repeated SQL statements
• temp tables
• if/else structures
Procedural Coding
Comparable to carrying groceries to the car one item at a time, when you could be using this instead.
Procedural Coding
Help your DBA out, learn set-based coding methods. Be a team player.
User-Defined Functions
Useful for
• code re-use
• packaging complex logic
• readability
User-Defined Functions
Useful for
• code re-use
• packaging complex logic
• Readability
• killing performance
• complicating query tuning
• inducing DBA nightmares
User-Defined Functions
Functions have their place, but they're not always the right tool for the job.
Views
Like user-defined functions, views can be used to
• re-use code
• hide complex queries
• make large queries more readable
Views
But what's lurking beneath the surface?
Views
Views can hide some scary performance issues. Make sure your view is clear.
SELECT *
Are you SURE you want the whole thing?
SELECT *
What's wrong with SELECT *?
• prone to table scans or lookups
• difficult to support with indexes
• widely considered sloppy and "lazy“
• dangerous in views
SELECT *
Be a hero.
Don't use SELECT * in code that counts.
Non-SARGable Queries
What is a SARGable query?
A query that is Search ARGument-able.
What does that mean?
Non-SARGable Queries
Which of these are SARGable expressions?
• SalesPersonID <> 10
• SalesPersonID = 99
• SalesPersonID >= 100
• SalesPersonID NOT IN (some subquery)
• SalesPersonID IN (some subquery)
• SalesPersonID IS NULL
• ISNULL(SalesPersonID, 0) = 0
Non-SARGable Queries
Which of these are SARGable expressions?
• SalesPersonID <> 10 *
• SalesPersonID = 99
• SalesPersonID >= 100
• SalesPersonID NOT IN (some subquery)
• SalesPersonID IN (some subquery)
• SalesPersonID IS NULL
• ISNULL(SalesPersonID, 0) = 0 **
* SQL 2008+ ** sargable if column is NOT NULLable
Non-SARGable Queries
SARG makes DBA's happy!
Bandaids
Don't use bandaids when corrective surgery is required
Bandaids
T-SQL makes it easy, too easy, to cover up coding mistakes, often with a price.
• DISTINCT (don't hide dupes, stop fetching them in the first place)
• NOLOCK (this is NOT the cure for blocking)
• UNION (figure out the logic for a proper WHERE clause)
Bandaids
Be tough - rip off the bandaids!
Ya big crybaby....
Mismatched Data Types
Wrong. So wrong.Some comparisons should never be made.
Mismatched Data Types
Some transformations conversions work just fine. Some create performance problems. Some just fail miserably.
Watch out for:
• NVARCHAR to VARCHAR
• character to numeric
• datetime manipulations to remove time, DATEDIFF
Mismatched Data Types
Keep your data types compatible
NULL
Improper handling of NULL values can lead to errors and/or incorrect results.
NULL
• NULL has NO VALUE
• NULL does not mean zero
• NULL does not mean blank
• NULL does not mean an empty string
• NULL means NULL
NULL
NULL values aren't difficult to work with, but be sure to read the fine print.
Incorrect/Unnecessary Ordering
Don't make assumptions about how data will be ordered.
If a specific order is required, specify it.
Incorrect/Unnecessary Ordering
Without ORDER BY, the query optimizer will surprise you with a "random" sort order, dictated by the fastest query plan it could find.
Specifying an ORDER BY can affect the query plan selected, thus affecting the performance of the query.
Incorrect/Unnecessary Ordering
Sometimes the order of things doesn't matter, sometimes it makes all the difference.
Single-row Triggers
This kind of thinking has no place in this movie. Nor does it belong inside a trigger.
Triggers fire per operation, not per row, and must be written to handle multi-row operations.
Single-row Triggers
Sometimes there should be only one, but not inside a trigger.
Lessons Learned
• Use set-based methods• Be careful with user-defined
functions and views• SELECT * - lazy and dangerous• Always obey SARG• Don’t use bandaids• Watch your data types• NULL – it’s not nothing• Order your results carefully• Triggers – many rows, not one
Any Questions?