SQL Server : SQL Query Optimization

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Query Optimization

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'