A detailed Guide on sql query Optimization This article was published as a part of the

Avoid using SELECT DISTINCT
SELECT DISTINCT command in SQL is used for fetching unique results and remove duplicate rows in the
relation. To achieve this task, it basically groups together related rows and then removes them. GROUP BY
operation is a costly operation. So to fetch distinct rows and remove duplicate rows, one might use more
attributes in the SELECT operation.
Let us take an example,
SET STATISTICS TIME ON SELECT DISTINCT Name, Color, StandardCost, Weight FROM SalesLT.Product
SET STATISTICS TIME ON SELECT Name, Color, StandardCost, Weight, SellEndDate, SellEndDate FROM
SalesLT.Product
As we can see from the execution of the above two queries, the DISTINCT operation takes more time to
fetch the unique rows. So, it is better to add more attributes in the SELECT query to improve the
performance and get unique rows.
Inner joins vs WHERE clause
We should use inner join for merging two or more tables rather than using the WHERE clause. WHERE
clause creates the CROSS join/ CARTESIAN product for merging tables. CARTESIAN product of two tables
takes a lot of time.
SET STATISTICS IO ON SELECT p.Name, Color, ListPrice FROM SalesLT.Product p, SalesLT.ProductCategory pc WHERE
P.ProductCategoryID = pc.ProductCategoryID

---

SET 
STATISTICS 
TIME 
ON 
SELECT 
p.Name, 
Color, 
ListPrice 
FROM 
SalesLT.Product 
p 
INNER 
JOIN
SalesLT.ProductCategory pc ON P.ProductCategoryID = pc.ProductCategoryID
So, we can see from the above outputs that inner join takes almost half time as compared to join using
WHERE clause.
LIMIT command
The limit command is used to control the number of rows to be displayed from the result set. The result
set needs to display only those rows that are required. Therefore, one must use limit with the production
dataset and provide an on-demand computation of rows for the production purpose
SET STATISTICS IO ON SELECT Name, Color, ListPrice FROM SalesLT.Product LIMIT 10

---

The above query prints the top 10 rows of the resultset. This drastically improves the performance of the
query.