7 Tips to Optimize SQL Queries

Have you ever stared at your computer screen wondering why it is taking so long for your SQL query to run? You are not alone in this universe, we all have been through such situations quite often. Of those, a few have been lucky to get help from their colleagues and seniors in understanding the reason behind their query running slow.

I am one of those lucky guys. Having worked with some of the best minds, I have received tips from them on how to optimize SQL queries for faster execution. Today I will share some of the tips I have received from these smart folks and also a few from my own experience of working on data for the past few years.

Tips to Optimize SQL Queries

1. Use the correct JOIN

There are four kinds of joins that you can use based on your specific requirements, namely,

• Inner Join
• Outer Join
• Full Join
• Cartesian Join

Here is a diagram to show how these joins are different from each other:

Source: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins 

But not all the Joins are equally efficient. This is how they rank in terms of efficiency:

So, use JOINS wisely. If Inner Join can give your required result, don’t go for Outer Join.

2. While using JOIN, keep the smaller table on the left for faster execution

3. Avoid HAVING clause when possible

The HAVING clause applies the filter on the result of the query after aggregation has been done. Due to this second filtering round, the HAVING clause can significantly increase the time taken to run a query.

See this article to understand the order in which each clause of a query is executed.

4. Avoid subqueries in the WHERE clause

The reason behind this again lies in the order in which each clause is executed.

5. Filter out the data in JOIN instead of WHERE clause

As you must have read by now in the link that I shared above, SQL queries are executed in the following order:

FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY

Since FROM & JOIN executes before WHERE, narrowing down the data in the FROM clause can make the query efficient. So, whenever possible, filter out the data using the ON rules for JOIN. Thus, by the time we reach the WHERE clause, we already have a smaller set of data.

Also, remember that the use of WHERE with OUTER JOIN can lead to incorrect results. Check this article for details.

6) Include partition columns in the WHERE clause

Before you start writing the query check the table to see if it has been partitioned and use the partitioned columns in the WHERE clause. This will ensure that the database looks for the data only in that particular partition instead of scanning the whole table.

7) Use UNION ALL instead of UNION whenever possible

Both UNION and UNION ALL are used to combine the results of two different SELECT statements. The only difference between the two is that UNION removes duplicate records whereas UNION ALL doesn’t. While you might want to get unique records in most of the cases, and hence want to use UNION, it comes with a performance hit because the database server has to do extra work to remove the duplicates.

I hope these 7 tips will help you optimize the SQL queries that you write. If you have some more tips to offer please share them here using the comment section.

Photo Credits:

1. Feature Image: Photo by Christopher Gower on Unsplash