There are many questions on the topic to improve SQL Performance, some of them are listed below, which are mostly asked in the interview.
How can I improve SQL query performance?
How can I improve my database performance?
How do I increase the performance of a selected query in MySQL?
What is SQL performance tuning?
How can you increase SQL performance?
How can SQL Server improve SQL performance?
Answer to all question:
Developer writes SQL Queries to fetch records as per client/user Requirements in proper way but Every User/Client wants a fast response to retrieve data.
So we need write query that provides best performance.
You can choose below methods to improve SQL query performance.
1. Avoid * in select clause:
required columns instead of using *.
Unused columns will take more spaces on
database and query will be slow down.
Write queries like below
sample, specifying only the required column.
: SELECT Column1, Column2 FROM Table
2. UNION should be used if it is required.:
UNION fetch unique records, which slows down the SQL Query performance.
Ex :SELECT Column1, Column2 FROM Table1
SELECT Column1, Column2 FROM Table2
3. Cluster and Non Cluster Index:
Cluster and Non Cluster Index should be creating to increase query performance.
Unnecessary use of indexes takes extra spaces and memory and creates load on the database. So index should only be used if it is required.
4. Joins should be used instead of Sub Queries:
Don't write sub-queries until there is no another away to retrieve data. Use available joins to filter your records.
This makes the query neat , clean , and easy to understand.
Ex :SELECT ID, Column1, Column2 FROM Table1 Join Table 2 on Table1.Id=Table2.Id
5. Avoid using cursor :
Always try to minimize the use of Cursor because cursor is used for looping, and looping causes load in memory and thus consumes more time, and the SQL Query will slow down, its performance will be decreased.
Keep these points in your mind while writing a SQL query, our aim should not be to get the result only, but it should be that to get results in the best way which increases the performance as well.