Writing Efficient SQL Queries

Introduction

Efficient SQL queries are critical for any data-driven organization, as they can improve query performance, maintainability, and readability. Whether you are querying datasets with lots of data or dealing with complex data relationships, knowing how to write efficient SQL queries is vital for optimizing database performance.

In this article, we'll explore some of the best practices for writing efficient SQL queries, including tips for making your queries readable, breaking down complex queries, utilizing JOINs efficiently, and using appropriate filtering techniques. By the end of this article, you'll have a better understanding of how to write efficient SQL queries that will help you get the maximum out of your data.

Make your queries readable

One of the best ways to enhance your query performance is to write efficient SQL queries in the first place. There are various solutions or techniques that can help you improve query performance, maintainability, and readability.

First of all, your queries should be readable. When you are writing a specific query, everything is clear to you in it. Occasionally, after a few months, you might have to work on it again, and then you can waste a certain amount of time just figuring out what's what in it.

If you have a lot of experience in writing SQL queries, you'll know all the common rules subconsciously. If not, we recommend you to get acquainted with this article. And if you don't have enough time, you can just use this.

Break down complex queries

Dividing complex SQL queries into smaller, more manageable parts is considered to be the best practice to enhance query performance.

Let's take a look at the example below:

You can use this data to create tables if you want to try this at home.

SELECT
    s.name as student_name,
    m.mark
FROM mark m
INNER JOIN students s ON s.id=m.student_id
INNER JOIN (SELECT id, name FROM faculty_name WHERE name='Ravenclaw') f ON f.id=s.faculty_id

You might've noticed an unnecessary subquery, that we used to filter data only by Ravenclaw. This query can be easily optimized if we just get rid of it:

SELECT
    s.name as student_name,
    m.mark
FROM mark m
INNER JOIN students s ON s.id=m.student_id
INNER JOIN faculty_name f ON f.id=s.faculty_id AND f.name='Ravenclaw'

Let's take a look at the next example:

WITH marks AS(
    SELECT student_id
    FROM mark
    WHERE mark=5
)
SELECT
    count(*),
    f.name
FROM students s
INNER JOIN marks m ON s.id=m.student_id
INNER JOIN faculty_name f ON f.id=s.faculty_id
GROUP BY f.name

In this query we used "with clause" (CTE) to get data on honors students, so that we could see which faculty has the largest number of them.

Common Table Expressions (CTEs) are a function in SQL that allows you to define a named temporary result set that can be used within the context of a single query. With CTEs, you can define a subquery and give it a name, which can then be referenced later in the query as if it were a table. It helps you write more complicated queries, without combining multiple subqueries into a less readable and understandable one.

Imagine that we often need to refer to the table of honors students. We can make our lives simpler by using Views. Views are virtual tables that are defined by a query and can be used like any other tables.

So, if we rewrite our previous query, it will look like this:

Firstly, we create the view:

CREATE OR REPLACE view marks_5 as
SELECT
    student_id
FROM mark
WHERE mark=5

Secondly, add it to the query:

SELECT
    COUNT(*),
    f.name
FROM students s
INNER JOIN marks_5 m ON s.id=m.student_id
INNER JOIN faculty_name f ON f.id=s.faculty_id
GROUP BY f.name

Utilize JOINs efficiently

In SQL, a JOIN operation is used to combine rows from two or more tables based on a related column between them. However, JOIN operations can be resource-intensive and time-consuming, especially when you deal with large datasets with numerous rows and columns. Therefore, it is important to utilize JOINs efficiently to improve query performance.

One way to do this is to minimize the number of JOIN operations needed. This can be achieved by optimizing the database schema and reducing the number of tables needed to answer a query. For example, denormalizing tables or using materialized views can help to reduce the number of JOIN operations.

A denormalized table is like a finished report.

Materialized view (mv) is something between a table and a view because you can see the query that creates it, but it storages as if it were a table. The pitfall with mv is that it has its own update schedule. Most often it is updated when it is selected, but there also can be troubles if one of the administrators has set them to be never updated. In short, if you are not sure — do not use it.

Another way to utilize JOINs efficiently is by using the appropriate type of JOIN. The most commonly used types of JOINs are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Each type of JOIN serves a different purpose and returns a different set of results. By understanding the data and the relationships between tables, you can choose the appropriate type of JOIN to minimize the number of JOIN operations needed and improve query performance.

If you have a hard time choosing the correct join, check out this awesome topic.

Use EXISTS instead of IN when appropriate

The EXISTS operator and the IN operator are used to filter query results based on a set of values, although, there are some differences between these two that can affect query performance.

The EXISTS operator returns true if the subquery returns at least one string, and the IN operator returns true if the column value is present in the list of values. The main difference between these operators is that the EXISTS operator is more efficient when a subquery returns a large amount of data. It is more useful than using IN or JOIN.

Let's see what is more efficient, EXISTS or JOIN:

EXPLAIN
SELECT
    m.*
FROM mark m
WHERE EXISTS (
    SELECT *
    FROM students s
    INNER JOIN faculty_name f ON f.id=s.faculty_id
    WHERE m.student_id=s.id);


EXPLAIN
SELECT
    m.*
FROM mark m
INNER JOIN students s ON m.student_id=s.id
INNER JOIN faculty_name f ON f.id=s.faculty_id;

As you can see, EXISTS costs much less than JOIN, although, it would be fair to say that it is also important to understand what result set you want to see. And from this point, consider using these or other features, operators, or constructions.

Apply filter conditions early in the query

Applying filter conditions early in the query is important because it reduces the amount of data that needs to be processed by the database engine. By applying filter conditions as early as possible, the database can decrease rows that don't meet the criteria, which reduces the amount of data that needs to be read and processed by the processor.

Look closely at this query:

SELECT
    m.*
FROM mark m
INNER JOIN students s ON m.student_id=s.id
WHERE s.name='Mandy Brocklehurst'

It joins two tables and only then filters them. In other words, if there are 1 million rows in the first table, and 1 million in the second, then (if it does not multiply) it creates a result table with a million rows and only then filters out 10-20 rows that we need. It will be much more efficient to use filtering already at the JOIN stage.

SELECT
    m.*
FROM mark m
INNER JOIN students s ON m.student_id=s.id AND s.name='Mandy Brocklehurst'

Use aggregate functions and GROUP BY

Using aggregate functions and GROUP BY efficiently will help to minimize the amount of data returned by a SQL query, which, in turn, can improve query performance. Aggregate functions such as SUM, AVG, MIN, MAX, and COUNT allow you to perform calculations on sets of data, and GROUP BY allows you to group those calculations by one or more columns in the query result.

By grouping data into meaningful subsets, aggregate functions and GROUP BY can reduce the amount of data returned by the query. This will help to minimize the amount of work that the database has to do to process the query and return the result.

Let's take a look at the example below:

SELECT 
    f.name AS faculty_name, 
    SUM(m.mark) AS total_marks
FROM mark m
INNER JOIN students s ON m.student_id=s.id
INNER JOIN faculty_name f ON f.id=s.faculty_id
GROUP BY f.name;

This query groups the marks by the faculty and uses the SUM function to calculate the total sum for each faculty. By grouping the data this way, the database only needs to process and return a single row for each faculty, rather than returning a separate row for each mark.

Conclusion

To sum up, writing efficient SQL queries is crucial for improving query performance, maintainability, and readability. To achieve this, it is pivotal to write readable queries, break down complex queries into smaller, more manageable parts, and utilize JOINs efficiently by minimizing the number of JOIN operations, use the appropriate type of JOIN, and use EXISTS instead of IN when appropriate.

By following these practices and techniques, you will write more efficient SQL queries, avoid unnecessary complexity, and optimize the performance of your database system.