Home

My query was ten times faster because to just one SQL clause.

It started with a headache and a bottleneck.

I was buried in performance reports for a fintech customer on a Thursday afternoon. In Q1, their user base had skyrocketed, causing our once-slick dashboards to lag like dial-up modems.

Optimizing a crucial query that drove their customer spend analysis was my assignment. Simply retrieving the total transaction amounts by client category for the previous quarter is all that is required.

The problem is that there were more than 150 million records in the transactions table.

And my question? The run was taking more than two minutes. even timing out at times.

The initial question (also known as "why is this so slow")
This is what I began with:
SELECT
    c.customer_id,
    c.segment,
    SUM(t.amount) AS total_spent
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.transaction_date >= '2024-01-01'
GROUP BY c.customer_id, c.segment;
It appears clean at first look. join using an indexed foreign key. To filter recent transactions, use a WHERE clause. To obtain spending by customer, group by.

What went wrong, then?

It turns out that the join was being processed before the filter, even though I had filtered the transaction_date.

This indicated that before focusing on the data, the database engine was scanning all 150 million transactions.

I experimented with increasing LIMITs, redoing the join, and adding extra indexes. Nothing provided me with the necessary lift.

The WITH clause (Common Table Expression) is the game-changer.
I was so frustrated that I modified the query using a CTE, primarily to facilitate debugging.

Everything changed with that rewrite.
WITH recent_transactions AS (
    SELECT *
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
)

SELECT
    c.customer_id,
    c.segment,
    SUM(r.amount) AS total_spent
FROM customers c
JOIN recent_transactions r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.segment;
The query now filters the pertinent 20 million entries first, then joins with the customer data, rather than scanning 150 million records and then filtering.

From 127 seconds to 11.4 seconds, the execution time decreased.

Nothing else has changed. identical data. Simply put, improved organization.

Let's examine why this was successful.

1. Early filtering reduces the amount of material to join
When the dataset has already been reduced, the join is substantially faster. Imagine organizing your clothing before you put it in the washing machine.

2. A more organized plan for implementation
CTEs can be converted into inline views by contemporary query optimizers (such as those found in PostgreSQL 12+, BigQuery, and SQL Server), which aids in their ability to make more informed optimization decisions.

3. Modular logic makes debugging simpler.
It is simpler to identify inefficiencies and test different query components when your logic is divided into smaller, more focused CTEs.

Extra optimization: Consolidating within the CTE
Do you want to take it a step further? I've included an aggregate in the CTE itself in this version:
WITH recent_spend AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM transactions
    WHERE transaction_date >= '2024-01-01'
    GROUP BY customer_id
)

SELECT
    c.customer_id,
    c.segment,
    r.total_spent
FROM customers c
JOIN recent_spend r ON c.customer_id = r.customer_id;
In order to cut down on rows and processing time, I'm now joining on an already-aggregated result. This version reduced execution to less than 6 seconds in my instance.

Concluding remarks from the trenches
I've written SQL for years, and I've discovered that structure, not syntax, is what makes a decent query exceptional.

It's similar to adding comments to your logic when you use the WITH clause. It separates problems, makes intent clear, and frequently significantly improves performance.

Here's the catch, though:
  • CTEs may manifest in older MySQL versions, resulting in temporary tables and decreased performance.
  • To utilize inline optimization in PostgreSQL, require versions 12 or later.
  • CTEs are typically advised in BigQuery for cost and performance visibility.
Important Points
  • Using a CTE alone made my query on 150M data run 10 times faster.
  • To lessen join load, filter early using the WITH clause.
  • Performance should be tested both before and after; your SQL engine is important.
  • Faster, cleaner SQL results in happy analysts.
Hi There, I'm Yahya, and I enjoy sharing knowledge and experiences.