SQL Query optimization is a process where database system allows comparison of the different queries for determining which might be “best”.
Best Practice of SQL query Optimization:
1. Avoid unnecessary columns in SELECT clause.
Select only those columns that you are really going to use.
Best Practice: SELECT Column1, Column2 FROM dbo.table_A; Avoid: SELECT * FROM dbo.table_A ;
2. Try to avoid using WILCARD (%):
The wildcard (%) slow down the performance, use it only if it is strictly necessary.
Best Practice: SELECT id, user, address FROM table_A WHERE name= 'bulldog'; Avoid: SELECT id, user, address FROM table_A WHERE name LIKE '% bulldog%';
3. EXISTS instead of IN
EXISTS operator is faster than IN, EXISTS stops searching when it finds a match.
Best Practice: Select * from Products where Product_Id EXISTS (Select product_id from Orders) Avoid: Select * from Products where Product_Id IN (Select product_id from Orders)
4. Use table-valued functions instead of scalar functions
Table-valued function is faster than scalar function, and it reduce the time used.
5. Use Temp tables
Used Temporary tables when you are joining a small table to a larger one. Transfer large table data into temp table and join with that. This reduces the time required in processing.
6. Use Schema name before SQL objects name
Using schema name before SQL object name, It helps the SQL Server for finding that object in a specific schema. As a result, performance is best.
Best Practice: SELECT Column1,Column2 from dbo.table_A Avoid:SELECT Column1,Column2 from table_A
7. Avoid prefix “sp_” with the user-defined stored procedure name
System defined stored procedure name starts with prefix “sp_”. Hence SQL server first searches the user-defined procedure in the master database and after that in the current session database, so avoid prefix “sp_” from user defined store procedure name.
Best Practice: Create procedure USP_Test Avoid:Create procedure SP_Test
8. Avoid cursors, if possible
It is always best to avoid cursors, because they slow down the query performance. Instead of this you can use alternative of cursors like Table variables & Temp tables both help you to save time and increase speed.
9. Use UNION ALL in place of UNION
The UNION combines the result set of two or more SELECT statements and returns distinct values.
The UNION ALL combines the result set of two or more SELECT statements and allows duplicate values.
Best Practice: SELECT Column1, Column2 FROM table_A UNION SELECT Column1, Column2 FROM table_B Avoid: SELECT Column1, Column2 FROM table_A UNION SELECT Column1, Column2 FROM table_B
SQL Query optimization is a process where database system allows comparison of the different queries for determining which might be “best”
10. Proper usage of Having clause
Using ‘Having’ clause you can use Aggregate function with ‘Where’ clause. So do not use ‘HAVING’ clause for any other purposes.
Best Practice: SELECT Product_Name, Count(Product_Name) FROM Products WHERE Product_Name != 'TV' GROUP BY Product_Name; Avoid: SELECT Product_Name, Count(Product_Name) FROM Products GROUP BY Product_Name HAVING Product_Name!= 'TV'
11. Use TRY-CATCH
Always use TRY-Catch to handle the SQL Statement error and sometimes long running transaction may cause deadlock, if you have no handle error by using TRY-CATCH.
12. Try to avoid more than one Subqueries
Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query.
Best Practice: SELECT Product_name FROM Products WHERE (Qty, Price ) = (SELECT MAX (Qty), MAX (Price) FROM Product_master) AND Product_name = 'TV' Avoid: SELECT Product_name FROM Products WHERE Qty = (SELECT MAX(Qty) FROM Product_master) AND Price = (SELECT MAX(Price) FROM Product_master) AND Product_name = 'TV'
Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.
Recommended SQL Server Post: