Avoiding running on the heap and CTEs vs Temporary Tables

This post is the second in a series of small groups of two tips on improving the performance of SQL queries, in addition to useful concepts usually overlooked in the modern SQL world:

Similarly to previous iterations, an introductory note is required: I am by no means an SQL expert, feedback and suggestions are more than welcome.

CTEs can be a major help in most queries, simplifying the development process and its maintenance, providing a single source of truth. However, their impact drastically depends on the SQL engine. For instance, PostgreSQL 12+ automatically materializes (allocates the output into memory) CTEs which are called more than once. Microsoft SQL Server is known to not materialize CTEs but, instead, running it as a view each time it is called, which can even result in different outputs per call — depending on the serialization level. But even if the engine materializes the CTE like PostgreSQL, what about its indexes? Well, typically the engine will make use of the underlying source table’s indexes.

CTEs will be perfect for many situations, however sometimes, you look at the query optimizer and realize a full table scan is being made, simply because the query is not relying on the underlying existing indexes. Or worse, Microsoft SQL Server, for instance, tends to have concurrency races when a CTE is being called on itself, often resulting in deadlocks.

Another useful way of recognizing the potential benefit of a temporary table is to use the SQL engine’s statistics to spot excessive I/O operations visible through the number of logical reads and similar statistics. Hint: the fewer reads, the better.

Consider the Stack Overflow database creating a query to:

1. Find the top 5 locations in terms of users
2. Retrieve users living in such location, ordered by their DisplayName

The CTE’s execution plan’s highlights are:

• Good: the CTE combined both the top location and finding the top users in said locations into a single statement — this has an upside of not having to materialize the data
• Good: the TopLocations CTE correctly estimated 5 results
• Bad: on the other hand, it failed to recognize the content of that top 5 results, estimating 50.073 users matching said result instead of the actual 66 (an 75 868% overestimation)
• Bad: SQL Server relied on index seeks and key lookups

The temporary table’s execution plan’s highlights are:

• Good: SQL Server correctly estimated the number of locations (5)
• Good: it correctly identified those 5 locations
• Good: because of that, the temporary table version approximated the actual users count far better, estimating 17 184 rows vs the actual 50 073 rows (291% underestimation)
• Good: it relied on a table scan instead

So when to choose CTEs vs Temporary Tables?

CTEs tend to be a suitable choice when:

• the CTE’s content bears no influence on the remainder of the query’s outcome
• when the CTE may include additional unnecessary info

Temporary tables on the other hand may be a good choice when:

• the output will be used multiple times
• data needs to be passed onto other objects
• you need to split the process into several sub-processes due to performance concerns

Note: This section has been heavily retrieved from Brent Ozar’s blog. Make sure to follow it as he understands SQL Server like no other. Link below.

This is one of the SQL 101 lessons: never, under any circumstances, run queries on (clustered) unindexed tables unless you are forced to do so. Even if they are temporary tables, make sure to add at least a primary key, ideally, a composite key including the queryable columns. You can further boost up your performance (although this does come at its own cost) by specifying a non-clustered key on your filterable columns if you are not able to do so using a primary key.

Running large operations, whether a filter or a join, on an unindexed table, will reflect in a full table scan, which is exactly what it sounds, scanning the entire table, for each row and operation. Sounds inefficient? That’s because it is.

But wait, I would never ever have a table without indexes, and certainly not run a query on it!

It’s actually not that simple. Like I mentioned in the first paragraph if your query does not make use of the existing indexes (e.g. using a column in a where predicate where the column is not indexed), then a full-table scan will be performed. This is where the execution plan can become your best friend, hinting at cases where a full-table scan is being performed, which signals the potential need for additional or adapted indexes.

• If you are using Microsoft SQL server and calling a CTE more than once, explore the possibility of using a temporary table instead or use intermediate materialization (coming in performance tips #3);
• If you are unsure of which parts of a statement will be employed further on, a CTE might be a good choice given SQL Server is able to detect which portion is actually used
• If the operations performed on the CTE do not make use of the underlying table’s indexes, considering materializing the data onto a temporary table with dedicated indexes;
• Rely as much as possible on diagnostics, using the execution plan to detect full table scans, and the statistics to detect possible sources of excessive I/O which can benefit from materialization;
• For christ’s sake, add indexes to any table; if you know beforehand that you will rely heavily on other columns, not in the index, consider including them in the index or creating a new (unclustered) index;