Performance Tuning interview questions
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.
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 [Query]
Explain Select * from Employee;
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.
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.
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.
1.We require to check the data volume of statement.
2.We need to review the data volume and apply the partitioning techniques accordingly.
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.
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.
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 :
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;
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..
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;
Not Recommended:
Select * from Employee where name=’Amit’;
Recommended:
Select * from Employee where name like ‘Amit%’;
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;
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.
Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…
In my previous article I have given Top 20 technical support interview questions with its…
In my previous articles I have given 15 most asked desktop support interview questions with…
A business analyst is someone who is versed in processes of data analysis used for…
In my previous article I have already given top questions and answers for Desktop support…
In my previous article I have given unix production support interview questions and answers. In…