Optimizing SQL Performance - Advanced Techniques for Faster Processing

This post looks at the difference between materialized and non-materialized approaches to structuring queries, and when each one actually gets you faster performance.

Materialized vs. non-materialized views

Materialized views and non-materialized (regular) views are both database objects that store the result of a query. The difference is in how — and whether — they store and refresh that data.

Non-materialized views: subqueries, CTEs, and views

These are all "virtual" — none of them store data themselves; they're saved queries that get re-executed every time you reference them, so they always reflect the current state of the underlying tables.

Subqueries

The most basic form: a query nested inside another, used to return data that the outer query filters or joins on.

Given TableA and TableB, a typical subquery looks like:

SELECT * FROM TableA A
    JOIN (SELECT * FROM TableB WHERE some_column > 5) B
        ON A.id = B.id AND A.id2 = B.id2

Common Table Expressions (CTEs)

CTEs are temporary result sets defined within the scope of a single statement, via the WITH clause. They let you name a subquery and reference it like a table for the rest of that statement — same execution cost as a subquery, but far more readable once queries get nested a few levels deep.

WITH FilteredB AS (
    SELECT * FROM TableB WHERE some_column > 5
)

SELECT * FROM TableA A
    JOIN FilteredB B
        ON A.id = B.id AND A.id2 = B.id2

Views

A view is a virtual table backed by a SELECT query — you query it like a table, but it holds no data of its own. Views are useful when the same query logic is shared across teams or reports, since they abstract away the underlying schema.

CREATE VIEW FilteredB AS
    SELECT * FROM TableB WHERE some_column > 5

SELECT * FROM TableA A
    JOIN FilteredB B
        ON A.id = B.id AND A.id2 = B.id2

One downside: you can't drop or alter TableB's schema without breaking any view defined on top of it.

Materialized storage: temp tables and materialized views

Unlike the options above, these actually persist data — which is what makes them faster for expensive, frequently reused queries, at the cost of needing to be refreshed.

Temporary tables

A temp table is created and scoped to a single database session, useful for storing intermediate results during complex, multi-step processing. It's automatically dropped when the session ends.

CREATE TEMPORARY TABLE FilteredB AS
    SELECT * FROM TableB WHERE some_column > 5

SELECT * FROM TableA A
    JOIN FilteredB B
        ON A.id = B.id AND A.id2 = B.id2

Because the data is materialized for the session, joins against it are much faster than re-running the underlying query every time.

Materialized views

A materialized view is essentially a persistent version of the same idea — the result set is stored in the database (not just for one session) and can be indexed like a regular table, which is what gives it the biggest performance edge for large, frequently-queried datasets.

CREATE MATERIALIZED VIEW FilteredB AS
    SELECT * FROM TableB WHERE some_column > 5

SELECT * FROM TableA A
    JOIN FilteredB B
        ON A.id = B.id AND A.id2 = B.id2

The trade-off is staleness: unlike a regular view, a materialized view needs to be explicitly refreshed to pick up changes to the underlying tables.

Which one should you actually use?

• Default to CTEs over subqueries — same performance, much better readability.
• Reach for a view when multiple teams or reports need the same logic and you want a single source of truth.
• Reach for a temp table when you need a fast intermediate result within one session or pipeline run.
• Reach for a materialized view when the underlying query is expensive, read far more often than the source data changes, and you can tolerate refreshing it on a schedule.

Happy querying! 🚀