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.
Subscribe
I write on various topics such as productivity, tech, books, personal finance, and more. To stay updated, choose either (or both) of the options below:
Get Real-Time Alerts
Stay in the loop with real-time alerts. Join my WhatsApp channel for instant notifications on new articles, captivating book snippets, noteworthy web finds, and more. Experience it as it happens.
Monthly Email Digest
Prefer a monthly roundup? Subscribe to the Email Newsletter to receive a neatly organized digest at the end of each month. Explore everything I wrote, handpicked web gems, and more – delivered to your inbox in a well-packaged newsletter format.
If you are feeling generous, you can also support the blog with any amount you like to help cover server and domain costs, keeping the blog running.
Photo Credits:
- Feature Image: Photo by Christopher Gower on Unsplash