How to Improve SQL Query Performance When Working with Large Datasets

As databases grow from thousands to millions—or even billions—of rows, SQL query performance becomes increasingly important.

A query that runs in a fraction of a second on a small table can become painfully slow when executed against a large production dataset. The good news is that many performance problems can be addressed with a simple principle:

Do less work, as early as possible.

This article explores some of the most useful techniques for improving SQL query performance, particularly when working with large datasets.

Filter Data as Early as Possible

One of the simplest ways to improve query performance is to reduce the number of rows that need to be processed.

Suppose you have a large events table containing years of time-series data, but you only need events from January 2026:

SELECT *
FROM events
WHERE event_date >= '2026-01-01'
  AND event_date < '2026-02-01';

Instead of processing the entire table, the database can work with a much smaller subset of data.

This becomes especially valuable when the filtered data is later:

• Joined with other tables

• Aggregated

• Sorted

• Deduplicated

• Transformed

The fewer rows entering these operations, the less work the database has to perform.

A useful development strategy

When developing a query against a very large table, start with a small subset of the data.

For example:

1. Explore a small time range.

2. Develop and test your query.

3. Verify that the results are correct.

4. Remove the restriction.

5. Run the final query against the full dataset.

This makes development faster and can help you identify logical problems without repeatedly processing millions of rows.

Don't Assume LIMIT Makes Aggregations Faster

LIMIT is useful for restricting the number of rows returned, but it does not necessarily reduce the amount of work required by an aggregation.

Consider:

SELECT COUNT(*)
FROM events
LIMIT 10;

The result of COUNT(*) is already a single row. The database still has to calculate the count before it can apply the LIMIT.

The same principle applies to grouping:

SELECT customer_id, COUNT(*)
FROM events
GROUP BY customer_id
LIMIT 10;

Conceptually, the database needs to perform the grouping before it knows which aggregated rows can be returned.

If your goal is to reduce the amount of input data during testing, you can put the LIMIT inside a subquery:

SELECT COUNT(*)
FROM (
    SELECT *
    FROM events
    LIMIT 1000
) AS sample;

This genuinely reduces the number of rows being aggregated.

However, there is an important warning: the result now represents only the sample of 1,000 rows, not the complete dataset.

This technique is excellent for testing query logic, but it should not be confused with an optimization that preserves the original result.

Reduce Table Sizes Before Joining

Joins can become expensive when they involve large datasets.

Imagine two tables:

• accounts

• web_events

Suppose web_events contains hundreds of millions of records, but your final result only needs the number of events per account.

Instead of joining every event to the accounts table first, you can aggregate the events before performing the join:

SELECT account_id, COUNT(*) AS event_count
FROM web_events
GROUP BY account_id;

This produces a much smaller result.

You can then join that result with accounts:

SELECT
    a.name,
    e.event_count
FROM accounts AS a
JOIN (
    SELECT
        account_id,
        COUNT(*) AS event_count
    FROM web_events
    GROUP BY account_id
) AS e
    ON a.id = e.account_id;

The key idea is:

Reduce the number of rows before expensive operations such as joins.

If web_events contains 500 million rows but only 100,000 accounts, the aggregation can potentially reduce the data involved in the subsequent join dramatically.

Be Careful When Joining Multiple Detail Tables

One of the most important SQL performance and correctness issues occurs when joining multiple tables that contain several rows for the same entity.

Consider two tables containing records by date:

Date	Table A	Table B
Jan 1	100 rows	200 rows
Jan 2	50 rows	100 rows

If you join these tables directly on the date, the database can produce a multiplicative result.

For January 1:

100 × 200 = 20,000 rows

For January 2:

50 × 100 = 5,000 rows

This can quickly become a serious performance problem.

Even more importantly, it can produce incorrect aggregate results.

The better approach: aggregate first

Instead of joining the raw tables, aggregate each one independently:

SELECT
    date,
    COUNT(*) AS a_count
FROM table_a
GROUP BY date;

And:

SELECT
    date,
    COUNT(*) AS b_count
FROM table_b
GROUP BY date;

You can then join these much smaller datasets:

SELECT
    COALESCE(a.date, b.date) AS date,
    a.a_count,
    b.b_count
FROM (
    SELECT
        date,
        COUNT(*) AS a_count
    FROM table_a
    GROUP BY date
) AS a
FULL JOIN (
    SELECT
        date,
        COUNT(*) AS b_count
    FROM table_b
    GROUP BY date
) AS b
    ON a.date = b.date;

Instead of joining potentially millions of raw records, you're joining a result containing approximately one row per date.

This pattern is particularly powerful when working with large event, transaction, or log tables.

Understand Why COUNT(DISTINCT ...) Can Matter

Join multiplication can also lead to incorrect counts.

Imagine a sales representative has:

• 5 orders

• 20 web events

If you join the two detail tables on the sales representative, you could end up with:

5 × 20 = 100 rows

A simple:

COUNT(sales_rep_id)

could therefore count the same sales representative many times.

In some situations, you may need:

COUNT(DISTINCT sales_rep_id)

to count each representative only once.

However, COUNT(DISTINCT ...) should not automatically be considered the solution to every join problem.

If the underlying issue is that you're joining large detail tables unnecessarily, pre-aggregation is often a much better approach.

For example:

SELECT sales_rep_id, COUNT(*) AS order_count
FROM orders
GROUP BY sales_rep_id;

And separately:

SELECT sales_rep_id, COUNT(*) AS event_count
FROM web_events
GROUP BY sales_rep_id;

You can then join these compact results.

This avoids creating the potentially enormous intermediate dataset in the first place. 

Use EXPLAIN to Understand What the Database Is Doing

When a query is slow, guessing is rarely the best strategy.

Most SQL databases provide an EXPLAIN command that allows you to inspect the query execution plan.

For example:

EXPLAIN
SELECT ...
FROM ...
WHERE ...;

Depending on the database system, the execution plan can provide information about:

• The order in which operations are performed

• Estimated row counts

• Join strategies

• Scans and indexes

• Sort operations

• Aggregations

• Estimated costs

The exact syntax and information available vary between database systems, but the underlying idea is the same: understand where the database expects to spend its work.

A practical optimization workflow

A useful process looks like this:

Write query
    ↓
EXPLAIN
    ↓
Identify expensive operations
    ↓
Modify query
    ↓
EXPLAIN again
    ↓
Compare the plans
    ↓
Measure the actual performance

The cost shown by EXPLAIN is generally not a direct measurement of execution time. Think of it as an estimate that helps the optimizer and developer compare alternative execution strategies.

Where supported, commands such as EXPLAIN ANALYZE can provide actual execution information as well.

The Core Principle: Do Less Work Earlier

Most of the techniques discussed above are different applications of the same idea:

The best optimization is often to prevent unnecessary work from happening in the first place.

Think about a query as a pipeline.

If you start with 100 million rows and filter it down to 1 million rows before performing a join, that join has dramatically less data to process.

If you aggregate those 1 million rows into 50,000 groups before joining another table, the next operation becomes even smaller.

In general:

Filter → Aggregate → Join → Transform

is often more efficient than:

Join everything → Transform everything → Aggregate at the end

Of course, SQL optimizers can rewrite queries and choose their own execution strategies, so the database engine may already perform some of these optimizations automatically. Nevertheless, writing queries that clearly express the intended reduction of data can make both performance and correctness easier to reason about.

A Simple Before-and-After Example

Consider a query that joins two large tables directly:

SELECT
    ...
FROM large_table_a AS a
JOIN large_table_b AS b
    ON a.date = b.date
GROUP BY ...;

If both tables contain many records per date, this join can generate a huge intermediate result.

A more efficient pattern is often:

SELECT
    ...
FROM (
    SELECT
        date,
        ...
    FROM large_table_a
    GROUP BY date
) AS a
JOIN (
    SELECT
        date,
        ...
    FROM large_table_b
    GROUP BY date
) AS b
    ON a.date = b.date;

Now each table has been reduced before the join.

Instead of joining potentially millions of detail records, the database may only need to join a relatively small number of aggregated rows.

The result is not only potentially faster—it can also be much easier to reason about.

Performance Is Important, but Correctness Comes First

There is one rule that should always come before performance:

Never optimize a query by accidentally changing what it means.

For example, adding:

LIMIT 1000

may make a query run faster, but it also changes the dataset being analyzed.

Likewise, moving an aggregation before a join can improve performance, but only if the new aggregation still produces the intended result.

Good SQL optimization therefore involves two questions:

1. Does this query produce the correct result?

2. Can it produce that result with less work?

Only after correctness has been established should you optimize aggressively.

When SQL queries become slow on large datasets, the solution is often not a complicated trick. It is about reducing unnecessary work.

The most important techniques are:

• Filter early to reduce the number of rows.

• Don't rely on LIMIT to speed up aggregations.

• Aggregate before joining when it is logically correct.

• Avoid joining multiple large detail tables unnecessarily.

• Use COUNT(DISTINCT ...) when duplicate rows created by joins would otherwise produce incorrect counts.

• Use EXPLAIN and execution plans to identify expensive operations.

• Compare query plans and measure performance after making changes.

• Always preserve correctness.

The principle worth remembering is simple:

Do less work, as early as possible.

When working with millions or billions of rows, reducing the amount of data before expensive operations such as joins, sorting, and aggregation can make an enormous difference.

Good SQL isn't just about getting the right answer. It's about getting the right answer efficiently.