SQL Query Optimization Tips

In every interview you might ask the basic questions related to SQL Performance Because Query optimization is very essential factor for any project.In this article I will cover most important Performance Tuning tip.

  • Use WHERE expressions to limit the size of result tables that are created with joins.
  • Avoid * in SELECT statement. Give the name of columns which you require. 
            SELECT * FROM TABLE1 
                   SELECT COL1,COL2,COL3 FROM TABLE1 /*This Is better*/
  • Use joins instead of sub-queries.
  • Better to create indexes on columns that have integer values instead of characters. Integer values use less overhead than character values.
  • Use WITH (NOLOCK) while querying the data from any table.
                  SELECT COL1,COL2,COL3
                  FROM TABLE1 WITH(NOLOCK) /*This Is better*/

  • Use TRY- CATCH to avoid deadlock condition.
  • Avoid Cursors since cursor are very slow in performance.
  • Use Table variable in place of Temp table

                 INSERT INTO #TABLE
                 SELECT COL1,COL2,COL3
                 FROM TABLE1 WITH(NOLOCK) /*Instead of This*/

                INSERT INTO @TABLE
                SELECT COL1,COL2,COL3
                FROM TABLE1 WITH(NOLOCK) /* This is better handling with small row set*/

  • Use UNION ALL in place of UNION if possible.
    Try to avoid triggers if possible.
  • Avoid Using DISTINCT clause
  • Use EXISTS instead of IN to check existence of data.
  • Avoid nchar and nvarchar if possible since both the data types takes just double memory.
  • Avoid Having Clause. Having clause is required if you further wish to filter the result of an aggregations.
  • Use Schema name before SQL objects name
  • Use Stored Procedure for frequently used data and more complex queries.
  • Keep transaction as small as possible since transaction lock the processing tables data and may results into deadlocks.
  • Avoid prefix “sp_” with user defined stored procedure name because SQL server first search the user defined procedure in the master database and after that in the current session database.
  • Use set NOCOUNT ON at the top of each stored procedure.
  • Remove calculations in your JOIN and WHERE clauses , Avoid using OR in join conditions
                  SELECT ID
           FROM TABLE1 a
             TABLE2 b ON
             ((year(a.COL1)* 100) + month(a.COL2)) = b.COL3
  • Follow proper indexing method and remove unwanted index