How SQL queries are written can have a huge impact on how quickly and efficiently they execute. In this post I will explain some very basic methods that can be used to optimise SQL queries.

• When writing select queries, only select the columns you need. In general using “SELECT *” is very bad practice.
• Avoid using sub queries, rather use joins. What is meant by this is instead of using:
  SELECT COLUMNA FROM TABLEA WHERE COLUMNB IS IN (SELECT COLUMNB FROM TABLEB)
  Rather use:
  SELECT COLUMNA FROM TABLEA AS TA INNER JOIN TABLEB AS TB ON TA.COLUMNB = TB.COLUMNB
• Use WHERE to limit the result set to only what you need. There is no point in pulling 10000 records if you are only looking for records, for example in a certain date range.
• Use WITH NOLOCK with SELECT queries when feasible, this can prevent deadlocks which can cause serious performance issues. However note that using WITH NOLOCK can result in a non-accurate result set being returned which contains non committed records, so keep this in mind when using. But when querying a production system database this risk might be outweighed by the risk of deadlocks occuring.

One last point on stored procedures, this is not really an optimisation point as much as a best practice point. When developing stored procedures always write them to return result sets of the same dimensions. I have worked on numerous legacy systems were this was not the case and it results in a complete nightmare, especially with projects that utilise an ORM.