SQL Basic Performance Optimization

Although to optimize your query in SQL Server you should have deep understanding with different SQL constructs, It would be nice to have some quick tips in your kitty to improve your query performance. Here is a list of few tips which can be helpful to review and improve performance of your SQL queries.

  • Avoid Loops as much as feasibly.
  • Avoid Temp Tables.
  • Avoid correlated subqueries.
  • Avoid selecting all columns, instead you should write only those columns in select clause which are required.
  • Avoid using Count(*) to check record exists, Use Exists() clause instead.
  • Avoid using Having clause with column names to filter records, it should be used with Aggregate functions only and other filter conditions should be applied before Group by clause with where clause.
  • Use operator EXISTS and IN clause appropriately.
  • Avoid unnecessary joins and avoid Outer Join.
  • Try to use UNION ALL in place of UNION.
  • Use conditions in WHERE clause smartly. e.g. greater than operator is better than not equal to.
  • Remove calculated fields from where clause.
  • Create indexes on fields used in the WHERE and JOIN clauses.
  • Remove the unused indexes.

 

Leave a Reply

Please log in using one of these methods to post your comment:

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