SQL Query Tuning :

In my previous articles i have given the basic idea about the SQL performance Tuning techniques. I have also explained about the SQL indexing and partitioning the tables which will be useful for SQL query Tuning. In this article i will give the brief idea about SQL Query tuning with real life case study so that user will get idea about how to do the SQL Query tuning. There are multiple steps to do the SQL Query tuning. In this article i will give you brief idea about SQL Query Tuning step-by-step.

SQL Query tuning is nothing but the different ways to run the query faster that its actual execution time

There are so many different factors which we needs to take care while doing SQL Query tuning. Only writing query in efficient way is not called as tuning.We need to consider following factors before doing any SQL Query Tuning :

1.Hardware of Server :

The Hardware of Server always plays main role in SQL performance tuning.The hardware of server is always according to the Business need by considering the data volume.

2.Server Speed :

The higher the configuration of server the speed is higher. The Second factor we need to consider is Server speed.

3.Database Management System Used :

The key factor of performance tuning is the database management system. There are so many good database management systems which will give the user fast access of the database like Oracle or SQL Server.

4.Volume Of Data :

The most important factor in SQL Query tuning is Volume of the data and the mechanism to divide that volume of data with using partitioning techniques or other techniques.

Before Starting SQL Query tuning :

Before starting with example and multiple tips of tuning your SQL query let us check how the SQL query execution is done. Any SQL query executes with using following steps :

1.From Clause

2.Where Clause

3.Group by Clause

4.Having Clause

5.Aggregate functions and select statement.

We need to consider the execution of specific sql statement. Then user needs to know about what exactly the full table scan means and what is index scan in SQL.I will give you the simple one liner definition for Full table scan as well as index scan in SQL.

Full Table Scan :

The full table scan reads all the records from the table and filter out those which records does not meet the criteria of selection.Each row from the column is examined and determined which is used in where clause.

Index Scan :

When optimizer uses the indexes to fetch the data then the scanning technique is known as Index scan.It retrieves the data specified by index.

Tips of Doing perfect SQL Query Tuning :

I have already explained you different tips to do performance tuning of SQL query in my previous article.In this section i will try to give you different tips which helps to improve the query performance apart from the tips given in my previous article.

Step 1 : Check cost with Explain Plan 

The first step to tune any query is to check its explain plan. The SQL Query Tuning is nothing but the reducing cost of the query and improving the query speed.Explain plan is nothing but the plan of query execution step by step.To Check the explain plan of the SQL query following syntax is used.

Syntax :

Explain [SQL Query]

Or

Press F10 short cut key (Cursor on query)

Step 2 : Check Query first before tune it

The second step to tune the SQL is Query needs to be written by considering some factors.following are some simple tips to write the SQL query in perfect manner

1.Use column names in the Query in-spite of using * operator to select all records

2.Avoid use of Distinct clause in query as it increases the sorts.

3.Avoid Use of not equal to in where clause in-spite of that use greater than and less than operator

4.Avoid Having clause or group by clause if possible.

5.Avoid use of Union statement in the query in-spite of that try to use union all statement.

These are above some important checkpoints to check before starting the tuning of SQL Statement.

Step 3 : Use of Proper Indexing Techniques

Indexing is best way to improve the query performance. But it also decreases the performance if it is not used in  proper manner.Best indexes will help user to find out the specified data with less disk i/o operations.To create a proper indexing on different columns user should have idea about the different indexing techniques in SQL as well as its types.

Real Life Example :

Suppose user wants to create index for following query. Let us consider that Employee table have 1 k  data and only 3 Employees are in ‘Active’ status.

Select Employee_name,Employee_salary from Employee where Status= ‘Active’;

If the statement table does not have any index then it will be execute slow. Here the status column needs indexing.Lets consider that it has distinct values ‘Active’ and ‘Inactive’. In that case simple index will not work. User needs to go for Bit-map index to improve the performance of the query.So dont go for direct indexing.Creating the selective indexes is a skill which will develop after some experience.

Tip : Avoid indexing on small tables

User needs to avoid the indexes on small tables as the processing of direct table scan faster than index scan in that case.

Step 4 : Create Composite indexes 

Creating composite indexes is best part of doing the performance tuning of specific query. There are so many queries where the conditions like and and or are used in where clause.In that case in-spite of using different indexes on different columns create a composite index which will definitely reduce cost of the query and improves the performance of the query.

Real Life Example :

Suppose user wants to optimize the following query with proper indexing.

Select Emp_name,Phone from Employee where Fname=’Amit’ And Lname=’Shiravadekar’;

The above query has where condition on fname and lname column. In that case user needs to create the composite index. The composite index will retrieve the data as fast as possible.

Step 5: Response Time and Total Execution time

Response time is the time which takes for a query to return the first record. Total Execution time is the time it takes for the query to return all records.Understanding of response time and total execution time is important.Lets take example of interactive reports.For interactive reports user needs to fetch all the data so Total execution time is really very important.

Real Life Example :

If some query returns 900 records and which used to populate a list with the first ten records. In this case, you are not concerned with how long it takes to return all 900 records. Instead, you want the query to return the first few records quickly, so that you can populate the list.

Step 6 : Use SQL Tuner Advisory or SQL Optimizer

The proper use of SQL Tuner advisory or SQL Optimizer will give user different suggestions about the SQL Query tuning. If the indexing is not proper then SQL tuner advisory will suggest user to remove those indexes to improve the performance of the query.The SQL Tuner advisory will give you result according to following inforamation:

1. Statistical Analysis : 

The SQL tuner advisory will provide you report of statistical analysis of the data.

2.SQL Profiling:

The SQL Tuner advisory will give you suggestions to do some kind of SQL profiling.

3.Index Analysis:

The SQL Tuner advisory will give you different suggestions to remove and add new indexes according to the execution of query.

Using the SQL tuner advisory results user needs to do the indexing and profiling accordingly to improve the query performance.

Step 7 : Create indexes on group by and order by columns 

The group by column always sorts the data and then it groups the data according to the column data.Creating indexes on group by column will improve the SQL Query performance,so that for SQL Query tuning user needs to create the indexes on group by columns.

Real life example :

Select * from Employee group by department;

In above example user needs to create index on department column to improve the performance of the query.

Select * from Employee order by salary desc;

In above example user needs to create index on salary column to improve performance of Query.

Step 8 : Distinct column Indexing

I have already suggested not to use the distinct column in query. There are some situations where user needs to use the distinct column.Make sure that the distinct column is properly indexed. The proper use of indexing the distinct column is important. If only one column is using distinct then the indexing will not work.If more than two columns are distinct ed then use the composite index to tune the query.

Real Life example:

Select distinct fname,lname from Employee;

Here user wants fname and lname distinct Employees from Employees table.In that case user needs to create composite index on fname and lname to improve the performance of the query.

Step 9 : Avoid using sub-queries

There are some situations where user needs to write sub-queries to achieve specific functionality.It is not recommended to use sub-queries to tune the query,Avoid writing sub-queries and try to convert that sub-queries to queries using joins.

Step 10 : Use Table Partitioning

Using table partitioning correctly according to the requirement is the key part of performance tuning.If the table is very huge and user needs to divide that table.User needs to use table partitioning in that case.

Real Life Example :

If there Employee table divided in to 4 different zones. Then user needs to create partition on the table according to different zones. When query retrieves the data it will hit partition of the table not exact table.It will definitely improves query performance drastically.

 

Hope these SQL Query tuning tips will be helpful to user . If you like this article on SQL Query Tuning or if you have any suggestions regarding SQL Query Tuning article kindly write in comment section.