1 / 24

Top Tips for Better TSQL Stored Procedures

Top Tips for Better TSQL Stored Procedures. Grant Fritchey – Red Gate Software. Goal. Learn methods for writing TSQL for readability Understand how to write TSQL for performance. Grant Fritchey. Product Evangelist for Red Gate Software Twitter: @ gfritchey Blog: scarydba.com

oakley
Download Presentation

Top Tips for Better TSQL Stored Procedures

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Top Tips for Better TSQL Stored Procedures Grant Fritchey – Red Gate Software

  2. Goal • Learn methods for writing TSQL for readability • Understand how to write TSQL for performance

  3. Grant Fritchey • Product Evangelist for Red Gate Software • Twitter: @gfritchey • Blog: scarydba.com • Email: grant@scarydba.com

  4. Agenda • Writing for readability • Object Names & Format • Comments • Error Handling • Personal Preferences • Writing for performance • Data Types • Functions in Comparisons • Improper Use of Functions • The “Run Faster” Switch • Inappropriate Use of Query Hints • Recompiles • Null Values • Row by Agonizing Row • Nesting Views

  5. Writing for Readability • Define a local standard • Object names • Comments • Format • Error handling • Enforce the standard • Document it • Code reviews

  6. Object Names & Format • Names should be descriptive • Procedures should be a phrase • Abbreviations should be common (no Ddltbl) • Use aliases • Clear • Common • Be consistent • A foolish consistency is the hobgoblin of little minds • Keyword in that sentence is “foolish”

  7. Comments • Do something • Most important is describing the action, not documentation • Use source control for documentation • Self-documenting code is a lie

  8. Error Handling • TRY/CATCH • Deadlocks • Establish local best practices

  9. Personal Preferences • CamelCase • Uppercase for reserve words and key words • Line breaks • On SELECT list • Between JOIN criteria • Between WHERE criteria • Use the semicolon • Indent • After initial SELECT • With ON clause • After WHERE clause

  10. Writing For Performance • Write for your data structures • Write for your indexes • Write for the query optimizer • Avoid common issues: • Data types • Functions in comparisons • Improper use of functions • The “run faster” switch • Inappropriate Query Hints • Recompiles • Null Values • Row By Agonizing Row • Nested Views

  11. Data Types • Problem • Implicit or explicit data type conversion • Indications • Scans • Slow performance • Solution • Use appropriate data types

  12. Functions in Comparisons • Problem • Function on column in WHERE or JOIN criteria • Indications • Scans • Slow performance • Solution • Don’t run functions on columns • Use sargeable functions

  13. Improper Use of Functions • Problem • Multi-statement user defined functions cause poor performance • Indications • Zero cost scan operator in exec plan • Very slow performance • Solution • When working with more than a few (~50?) rows, don’t use them • When using operations such as JOIN that require statistics, don’t use them

  14. The “Run Faster” Switch • Problem • Use of the NO_LOCK query hint everywhere, or setting transaction isolation level to READ_UNCOMMITTED • Indications • Extra rows in result set • Missing rows in result set • Solution • Tune the queries • Tune the structures • Use snapshot isolation levels

  15. Query Hints • Problem • Query hints in the code such as FAST n, index hints, JOIN hints • Indications • Inconsistent performance behavior • Bad performance • Odd looking execution plans • Solution • Use of query hints should be exceptional • Exceptions are rare, not standard practice

  16. Recompiles • Problem • Excessive blocking and CPU contention caused by constant or long statement recompiles • Indications • Recompile events in extended events or trace • Blocking • High CPU usage • Solutions • Avoid interleaving DDL & DML • Where viable, use table variables

  17. NULL Values • Problem • Incorrect data returned due to improper use of NULL • Indications • = and <> instead of IS NULL and IS NOT NULL • Solutions • Learn to use NULL properly

  18. Row by Agonizing Row • Problem • Cursors instead of set-based operations • WHILE loops instead of set-based operations • Multiple statements where 1 will do • Indications • Extremely slow performance • Solutions • Eliminate unnecessary row-by-row processing

  19. Nested Views • Problem • Views within views causes optimizer timeout • Indications • Incredibly complex query plans for simple queries • Very poor performance • Solutions • Don’t nest views • Materialize views

  20. Goal • Learn methods for writing TSQL for readability • Understand how to write TSQL for performance

  21. Writing for Readability • Define a local standard • Object names • Comments • Format • Error handling • Enforce the standard • Document it • Code reviews

  22. Writing For Performance • Write for your data structures • Write for your indexes • Write for the query optimizer • Avoid common issues: • Data types • Functions in comparisons • Improper use of functions • The “run faster” switch • Inappropriate Query Hints • Recompiles • Null Values • Row By Agonizing Row • Nested Views

  23. Grant Fritchey • Product Evangelist for Red Gate Software • Twitter: @gfritchey • Blog: scarydba.com • Email: grant@scarydba.com

  24. Questions? How would you… ? What happens when… ? Why does… ? When do I… ?

More Related