How to improve SQL Query Performance?

SQL statements are basically used for manipulation of data or data retrieval.When we consider the large software,industrial software it has very high volume of data.So there is huge need of tuning the query which will improves the performance of data and application.There are lot of factors we need to consider in Performance tuning like the Server hardware,Speed of Server,Database using,Volume of data e.t.c.In this article i am giving you the basic idea of SQL Performance Tuning Techniques.We will get same result using multiple type of queries.But by considering the cost and execution of the query we will able to tune our sql and improve the performance.

CLICK HERE TO GET INFORMATION ON INDEXING OF SQL..

SQL Performance Tuning Techniques is the process of ensuring that the SQL statements  will run in the fastest possible time.

  • Tune your SQL here:

Following are some basic tips using which you will able to optimize your query and improve SQL Performance Tuning Techniques .The first step to check is cost of your query.For checking the cost of query use following simple command.I will explain theSQL Performance Tuning Techniques ‘Explain’ statement in detail in my other advanced tuning articles.As of now just refer following statement to find out the cost of query.More the cost less the performance of query.

Explain  [Query]

Explain Select * from Employee;

How to improve SQL Query Performance? – Tips and Examples

Check 1 : Need to Avoid Multiple number of Joins

1. There is common practice  where we are using the unnecessary multiple joins.
2.If you see the explain plan the joins cost is very high.
3.We require to check all the joins and remove the unnecessary joins by restructuring query.

Check 2 : Cursors Illumination

1.We require to revisit the outputs of multiple queries and  need to remove the unnecessary cursors.

2.Dynamic cursors limits the optimizer to use nested loop joins.

3.We require to avoid cursors and use set-based queries instead of cursors.

Check 3 : Avoid non-correlated sub queries

1. There are so many times due to bad SQL techniques the people are using the non correlated sub-queries.
2.We require to use correlated sub queries.

Check 4 : Avoid the costly functions and statements

1.We require to check the system functions and its cost .

2.We require to avoid distinct or count like functions wherever possible. These functions are costly and will reduce the cost.

Check 5 : Need to create correct index on correct places

1.We require to create the indexes on correct places.

2.We require to reduce the data retrieval time for queries.

3.We require to create highly selective indexes by checking and analyzing data.

Check 6 :  Check the data volume

1.We require to check the data volume of statement.

2.We need to review the data volume and apply the partitioning techniques accordingly.

check 7 : Drop unused indexes

1. We require to check the low performing indexes and unused indexes.

2.We need to recreate the good indexes and remove the unused indexes.

check 8 : Create Stats with update stats :

1.There are so many times when we forget to gather stats of tables,indexes or schemas.

2.We need to gather stats and set some jobs to gather stats on regular basis.

Check 9 :  Need to check the constraints

1.There are so many times when the performance will decrease because of the constraints

2.We need too revisit the design and check the primary key,foreign key and not null and check constraints.

Some tips to improve the SQL Performance :

Tip 1:   Never use *(Star) to fetch all records from table

Sql query become fast if you use actual columns instead of * to fetch all the records from the table.

Not Recommended:

Select * from Employee;

Recommended:

Select Eno,Ename,Address from Employee;

  • Tip 2: Try to avoid distinct keyword from the query

Try to avoid distinct keyword from select statements.Distinct keyword has high cost and low performance.When anyone uses distinct keyword it first sorts the data from column and then fetches the distinct values.Use exist operator inspite of distinct keyword.

Not Recommendedd:

SELECT DISTINCT d.dept_no, d.department_name
FROM Department d,Employee e
WHERE d.dept_no= e.dept_no;

Recommended:

SELECT d.dept_no d.department_name
FROM Department d
WHERE EXISTS ( SELECT ‘X’ FROM Employee e WHERE d.dept_no= e.dept_no);

CLICK HERE TO GET INTERVIEW QUESTIONS ON PERFORMANCE TUNING..

Performance Tuning

  • Tip 3:Carefully use where conditions in sql

Try to use correct operator as per requirement given.

Not Recommended:

Select * from Employee where salary != 65000;

Recommended:

Select * from Employee where salary > 65000 and salary < 65000;

  • Tip 4: Use Like operator in spite of equal to (=)

Not Recommended:

Select * from Employee where name=’Amit’;

Recommended:

Select * from Employee where name like ‘Amit%’;

  • Tip 5: Avoid having clause/group by statements

Having clause and group by statements have high cost.So try to avoid it in sql query.

Not Recommended:

Select * from Employee where name=’Amit’  group by department having salary=45000;

Recommended:

Select * from Employee where name=’Amit’ and salary=45000;

Tip 6: Use of Exist and In Operators

Basically Operator IN has lowest performance.IN operator is used when Filter criteria is in subquery whereas Exist operator is used when filter criteria is in main query.

Example:

In Operator:

Select * from Employee where Department_name In(Select Department_name from Department where Dno=10);

Exist operator:

Select * from Employee where Exist(Select Department_name from Department where Dno=10);

Try to use union all inspite of union.As union scans all data first and then eliminate duplicate so it has slow performance.

Not Recommended:

Select * from Employee where dept_no=10

Union

Select * from Employee where dept_no=20;

Recommended:

Select * from Employee where dept_no=10

Union  All

Select * from Employee where dept_no=20;

  • Tip 8: Avoid use of Functions in Where condition.

Not Recommended:

Select * from Employee where Substr(name,1,3)=’Ami’;

Recommended:

Select * from Employee where name like ‘Ami%’;

CLICK HERE TO GET INFORMATION ON SQL OPTIMIZER AND PARSER..

I have explained the Basic tips which is used to improve and optimize performance of SQL Queries.Hope this article is helpful to you to improve the performance of your sql queries.

HOME

4 Replies to “How to improve SQL Query Performance?”

  1. Hi,

    It would be helpful if you provide reasons as well for recommendation and non recommendation e.g. in third Tip – you asked not to use ‘!=’ and instead use ‘>”<' …any reason. Explain for all if possible.

Comments are closed.