How to optimize SQL Queries | Optimize SQL Query

In previous articles I have given different steps for optimizing SQL queries with multiple examples. I have given some tricks to tune SQL queries as well.In this article I would like to explain you simple steps of Optimizing SQL Queries with giving simple examples and check how to optimize SQL Query with examples.

Optimize SQL Query with examples :

In this section I would like to give you multiple tricks and techniques to optimize queries written in SQL.

1.Use of Proper Indexing :

The use of proper indexing is first step to optimise the query. There are some important steps to use or create index .

a.Create the proper indexes on columns in queries where following Clauses – Where Clause -Group By Clause – Order By Clause

Example :

Select Customer_Id,Customer_Name from Customer where Customer_Id in(‘101′,’102’) group by Department order by Customer_Name;

In above query user must have to create index on Customer_Id because that column is in where clause.

Create index SI_Customer_ID on Customer(Customer_Id);

Department column – Because the grouping is done overthere.

Create index SI_Department on Customer(Department);

Customer_Name column – because this column in in Order by clause

Create index SI_Cust_Name on Customer(Customer_Name);

2. Try to recreate the complex query with using temporary tables :

It is always a good practice to use the temporary tables where complex subqueries are there .

3.To avoid the functions in Predicates :

It is always a good practice to avoid the functions in predicates. I would like to give you one simple example related to the same. The programmers are using the Upper function in most of the cases to avoid the check with Case-sensitiveness.

4.Use Minus instead of Exists subqueries :

We find out some cases where there is good performance while using minus operator than using exists and non-exist operators to filter the data.

5.Avoid using OR clause :

We need to check whether OR operator is really needed or not. The OR operator always reduces query performance.We need to avoid OR operators within SQL query.

6.Avoid the Like Predicate :

There are so many situations where user can replace LIKE operator with eqal to Operator.

Example :

If user wants to find out the Customers whose name is amit.

Select * from Customer where Name like ‘Amit%’;

But in spite of that we need to use,

Select * from Customer where Name=’Amit’;

The equal to query is always better than LIKE query.

7.Use Proper indexing on NULL values :

If you are frequently using the columns with null values then user needs to create the index on null value column.

Real Example :

Lets say if column named Department has multiple null values you need to create index on that specified column,

    Create index
    I_Null_Department
on
   Customer
   (nvl(Department,’null’));

8.Avoid Select * and select only Necessary columns :

It is always a good practice to use or to select necessary columns in spite of using Select * statement.

Example :

If user wants to fetch Customer_No and Customer name from Customer table then,

Avoid :

Select * from Customer;

Use :

Select Customer_Number,Customer_Name from Customer;

The performance of second query is always better than first query.

9.Use Inner Join whereever Possible :

User needs to use inner join rather than outer join or union like statements to improve the performance of SQL Query.Outer Joins and Union statements will always do the full scanning of either table so it is not recommended to use it.

10.Use of Distinct statement properly :

The Distinct keyword always do the full scanning of table to fetch distinct values from table. It is recommended to use Distinct keyword if necessary.

11.Calculations on Indexed columns :

User needs to carefully use the calculations on indexed columns. The calculations will slow down the performance.To avoid such kind of situation we need to use functional based index on such columns.

12.Analyse the Business requirement properly to avoid Full Table Scan

The full table scan slows down the performance of query. To avoid such cases user needs to understand the business requirement correctly and then fetch/filter the dataset which is necessary.

13.Stats Gathering :

The stats gathering for every table is most important activity to improve the performance of SQL query. Just check with business requirements and do the stat gathering activity properly.

14.Use of SQL Hints wherever needed :

Using the correct hints at correct place will always improve the performance of SQL query. So use correct SQL hints to correct columns.

These are some most important tips which is useful for Optimizing SQL Queries.