SQL Query optimization techniques in SQL Server

Sql Statements (queries) are used to retrieve data from the database. The best query is important when performance is considered for that SQL query tuning is required. 

Here are the few techniques which we can use regularly use to optimize the query,

1. The sql query becomes faster if you specify columns names in SELECT statement instead of than '*'.

Eg:

SELECT [ProductID]

      ,[Title]

      ,[ProductName]

      ,[ProductDesp]

      ,[ProdImage]

FROM [dbo].[ProductDetails]

Instead of:
SELECT *

FROM [dbo].[ProductDetails]

2. HAVING clause is used to filter the rows after all the rows are selected. Do not use HAVING clause for any other purposes.

Eg: 
SELECT ProductName, count(ProductName)
FROM ProductDetails
WHERE Title != 'Copper'
AND Title != 'Silver'
GROUP BY Title;

Instead of:
SELECT ProductName, count(ProductName)
FROM ProductDetails
GROUP BY Title

HAVING Title!= 'Copper' AND Title!= 'Silver';

3. There can be more than one subqueries in your main query. Try to minimize the number of subquery block in your query.

For Eg:
SELECT Name
FROM Students
WHERE (score, age ) = (SELECT MAX (score), MAX (age)
FROM StudentsDetails)
AND dept = 'Electronics';

Instead of:
SELECT Name
FROM Students
WHERE score = (SELECT MAX(score) FROM StudentsDetails)
AND age = (SELECT MAX(age) FROM StudentsDetails)
AND dept = 'Electronics';

4. Use operator EXISTS, IN and table joins in a query.
a) Usually IN has the slowest performance.
b) IN is efficient when most of the filter criteria is in the sub-query.
c) EXISTS is efficient when most of the filter criteria is in the main query.

For Eg

Select * from ProductDetails p
where EXISTS (select * from Items o
where o.ProductId = p.ProductId)

Instead of:

Select * from ProductDetails p
where ProductId IN
(select ProductId from Items)

5. Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship.

For Eg:
SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

Instead of:
SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;

6. Try to use UNION ALL in place of UNION.

For Eg:
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team;

Instead of:
SELECT id, first_name, subject
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team;

7. Be careful while using conditions in WHERE clause.

For Eg:
SELECT id, first_name, age FROM student_details WHERE age > 10;

Instead of:

SELECT id, first_name, age FROM student_details WHERE age != 10;

8. Instead of using Temp Table using Table Variable to store data temporary if you don't have record more than 1000 to read the data.

Eg:
SELECT ProductName,Category,Description FROM @TempProduct 

Instead

SELECT ProductName,Category,Description FROM #TempProduct 

9. Avoid LIKE searches with prefix wildcards

Eg:
SELECT
    ProductName,
    Category
FROM
    ProductDetails
WHERE
    ProductName LIKE '%Mar%'

Having a wildcard '%' at the beginning of the pattern will prevent the database from using an index for this column's search. Such searches can take a while.

10. Apply Indexes on tables wherever required.
1. Clustered Index (By Default on Primary Key Column is applied)
2. Non Clustered Index 

Please post your comments and Queries if you have any queries in SQL Server.

Thank You!

Connect me on: Facebook, Twitter, Instagram  

You can find more blogs on our website: Trigya Technologies