SQL Server Queries: Writing queries for performance – Part 2

Writing an efficient query

An efficient query is the result of following a few simple rules and patterns. Part 1 of the series outlined general goals when creating a query. This part of the series gives concrete rules for writing efficient queries.

The Rules

  1. Use the minimum number of tables while meeting business requirements
  2. Only return the columns needed / specify the columns to be used
    1. Never use SELECT *
    2. This reduces network traffic, client processing and ensures you won’t get extra columns returned in your query if the base tables are modified
  3. Add a WHERE clause if possible
    1. Directly reduces I/O and network traffic
    2. Use sargable arguments (arguments that can effectively use an index)
      • =
      • >
      • <
      • <=
      • >=
      • BETWEEN
      • IN
      • LIKE ‘Text%’
    3. Avoid non-sargable arguments
      • <>
      • NOT IN
      • NOT LIKE
      • LIKE ‘%text’
      • LIKE ‘%text%’
      • OR
      • Functions in WHERE clause or JOIN
      • Avoid use of functions in clause
  4. Use “SET NOCOUNT ON” for all stored procedures
  5. Use stored procedures or parameterized queries
    1. Allows your execution plan to be re-used
    2. Especially important when the query is executed multiple times
  6. Use consistent formatting
    1. Create guidelines and be sure your developers follow them
  7. Verify the query plan
    1. Indexes
    2. Seeks versus scans
    3. Implicit conversions
    4. Join types
  8. Avoid cursors / RBAR
  9. Avoid query hints and index hints (let the query engine do its’ job and adjust the plan as the data changes over time)

 

Leave a Reply

Your email address will not be published.