SQL Performance Tuning Techniques | How to improve Query Performance?

SQL Performance Tuning Techniques :

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;

  • 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 “SQL Performance Tuning Techniques | How to improve 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.