Important Techniques -SQL Query Optimization
SQL is a very important tool for managing and manipulating data within the relational databases. With the volume of data increasing, SQL query optimization plays a crucial role in addressing the challenge of writing complex queries to retrieve the data.
Now, we can look at some of the important steps we need to follow while performing SQL Query optimization
Use Proper Indexes:
Indexes are very important data structures that help to improve the speed at which data can be retrieved. They create a sorted copy of the indexed columns and allows the database to quickly identify the rows that match with the query. This saves a lot of time.
The syntax for creating an index is
CREATE INDEX <index name> ON <TABLE NAME><column name>
Ex: CREATE INDEX index_product_id ON PRODUCTS<product_id>
Avoid using SELECT *
We should generally avoid using SELECT* for every query especially when we do not need columns that are not relevant for our analysis. Using SELECT* every time slows down performance and also leads to very inefficient queries.
As a general practice, it is always better to select only the specific columns we need for our analysis.
Avoid redundant or unnecessary data retrieval:
It is also very important to reduce/limit the number of rows we retrieve from a table. The SQL query usually slows down when a greater number of rows are being retrieved. So, it is always good to use LIMIT function to reduce the number of rows returned. This is especially very useful for validating queries or the output of the transformations we perform.
Use JOINS effectively:
JOINS help us to retieve data from two or more tables based on a related column in a single query making it possible to perform more complex analysis.
a. Order joins logically: Generally, we should start with the tables that return the fewest rows. This helps in reducing the volume of data that needs to process in the subsequent JOINS.
b. Use CTE's to reduce the complexity of the queries.
The below query is to find the overall average heart rate for a patient whose HR values are measured for each min in a particular day. This is a good example for a CTE.
with avg_heartrate as
(select g.patientid ,d.gender ,
DATE_TRUNC('day',g.timeof) as DAY,
ROUND(avg(g.hr),2) as avg_hr_day_wise
from gv_heartrate g
INNER JOIN
gv_demography d
ON g.patientid=d.patientid
group by g.patientid,DAY,d.gender
ORDER BY g.patientid)
select a.patientid,a.gender,round(avg(a.avg_hr_day_wise),2) as overall_avg_heartrate
from avg_heartrate a
group by a.patientid,a.gender
order by a.patientid
The above query could become very complicated if we have to write only using joins/subqueries. We are able to use CTE's effectively here to reduce the complexity
Optimize Subqueries:
Whenever, we need to dynamically perform some aggregation, filtering or joins of data, we prefer to use subqueries as subqueries help to keep all the operations in a single query instead of writing separate query for each of the above. But sometimes, usage of subqueries may also cause performance issues if they are not used
carefully. So, the best practice is to minimize the use of subqueries wherever possible replace it with JOINS/CTE's instead.
These are some of the important SQL optimization techniques we frequently practice in most of the projects in our organization. By applying these techniques, one can effectively improve the performance of the SQL queries to ensure they are running at an optimal performance.
