//
you're reading...
SQL Server

T-SQL Performance Anti-Patterns

What you don’t know could hurt your query performance.

I will present a seven part series on the most common T-SQL performance anti-patterns. Part 1 – Data Types, Part 2 – Using Functions, Part 3 – Multi-Statement UDFs, Part 4 – Dirty Reads, Part 5 – Query Hints, Part 6 – The Dreaded Cursor, Part 7- Nested Views

Introduction:

There are three very basic idioms for writing well performing T-SQL. (1) They are cumulative. (2) Doing all of them will have a positive impact on performance. (3) Skipping or changing any of these is likely to have a negative impact on performance.

  • Data Structure Consistency: If you are storing a datetime data type, use datetime, not a varchar or something else. Also take advantage of the foreign key constraints and other structures that you have in place when you’re writing your T-SQL code
  • Take Advantage of Your Indexes: Write your code so that it can take advantage of any indexing on your table. If proper indexing does not exist then explore adding the appropriate indexes.
  • Write for the query optimizer: Take the time to understand how the optimizer works and write your code in such a way that you help it make the best optimization decisions.
  • Advertisements

    About ldgaller

    Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

    Discussion

    No comments yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    %d bloggers like this: