SQL Tuning Tips

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 etc. In this article I am giving you the basic idea of SQL  Tuning Tips in 2022. 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. We will see How to improve SQL Query Performance? in this article. In this article  we will give you SQL Tuning tips in detailed level.

CLICK HERE TO GET INFORMATION ON INDEXING OF SQL..

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

• How to do SQL Tuning ? 

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 the ‘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? – SQL Tuning Tips and Examples

Check 1 : Need to Avoid Multiple number of Joins

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.

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 Recommended:

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..

• 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