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.
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
FROM TABLE1 WITH(NOLOCK) /*Instead of This*/
INSERT INTO @TABLE
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
FROM TABLE1 a
TABLE2 b ON
((year(a.COL1)* 100) + month(a.COL2)) = b.COL3
- Follow proper indexing method and remove unwanted index