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

SQL Query Optimization Techniques
Till now, we have seen how a query is executed and different measures to analyze the query performance.
Now we will learn the techniques to optimize the query performance in SQL. There are some useful
practices to reduce the cost. But, the process of optimization is iterative. One needs to write the query,
check query performance using io statistics or execution plan, and then optimize it. This cycle needs to be
followed iteratively for query optimization. The SQL Server itself also finds the optimal and minimal plan to
execute the query.
Indexing
An index is a data structure used to provide quick access to the table based on a search key. It helps in
minimizing the disk access to fetch the rows from the database. An indexing operation can be a scan or a
seek. An index scan is traversing the entire index for matching criteria whereas index seek is filtering rows
on a matching filter.
For example,
SELECT p.Name, Color, ListPrice FROM SalesLT.Product p INNER JOIN SalesLT.ProductCategory pc ON
P.ProductCategoryID = pc.ProductCategoryID INNER JOIN SalesLT.SalesOrderDetail sod ON p.ProductID =
sod.ProductID WHERE p.ProductID>1

---

In the above query, we can see that a total of 99% of the query execution time goes in index seek operation.
Therefore, it is an important part of the optimization process.
Guidelines for choosing index:
1. Indexes should be made on keys that frequently occur in WHERE clause and join statements.
2. Indexes should not be made on columns that are frequently modified i.e UPDATE command is applied on
these columns frequently.
3. Indexes should be made on Foreign keys where INSERT, UPDATE, and DELETE are concurrently
performed. This allows UPDATES on the master table without shared locking on the weak entity.
4. Indexes should be made on attributes that occur together commonly in WHERE clause using AND
operator.
5. Indexes should be made on ordering key values.
Selection
Selection of the rows that are required instead of selecting all the rows should be followed. SELECT * is
highly inefficient as it scans the entire database.
SET STATISTICS TIME ON SELECT * FROM SalesLT.Product
SET STATISTICS TIME ON SELECT ProductNumber, Name, Color,Weight FROM SalesLT.Product

---

As we can see from the above two outputs, the time is reduced to one-fourth when we use the SELECT
statement for selecting only those columns that are required.