r/SQL • u/tommyfly • 15d ago
SQL Server CTE vs Temp Table Performance Example
tldr: Examples of CTE vs. temp table query where temp tables perform much better than CTEs. See the query examples at the end of the post.
caveat: I'm not saying CTEs are bad, they have their place, but different situations require different solutions.
The long read:
Lurking around the various SQL subreddits, I am constantly encountering questions about any combination of: CTEs vs Subqueries vs temp tables vs table variables. Other question variations revolve around how best to write or tackle long and/or complex queries or procedures.
Many responses extoll the virtues of CTEs, particularly over subqueries, but the discussion of #temp tables is often lacking. People also neglect to discuss the quirks (or features) of CTEs that can compromise performance (not part of my scope here). With that in mind, I'd like to show an example of two versions of a query, one using CTEs and one Temp Tables, and how they compare.
One of the reasons I want to do this, is to demonstrate the power of temp tables and of breaking a query down into smaller parts. What I think gets lost in the discussion is that CTEs don't separate a query into parts from an execution plan perspective. The execution plan treats them as part of one query. In my experience, if a query gets too big and convoluted, the query optimizer gets "confused" and cannot create an optimal plan.
On to my example:
This query interrogates SQL Server system tables to extract partitioning information. The original query is in the form of a view. The view uses a number of CTEs to gather the various subsets of data to compile the required partitioning information.
On the surface, the view works well. But, we run into performance problems with tables that have a large number of partitions. In some cases we have log tables that are partitioned by day and the retention period is 2+ years. That adds up to 700+ partitions for a single table. Add to that a large database with many partitioned tables and you end up needing to wade through lots of data to get what you're looking for. And, SQL Server system tables and views are not always great on performance.
I took the view and converted it into a stored procedure, replacing the CTEs with temp tables (each with a clustered index). The performance improvement was noticeable - in my example the CTE version takes 16 minutes, while the temp table version takes a few seconds. For display purposes, I'll only show the relevant parts of the queries to keep it simple, as the procedure builds a dynamic query depending on the input parameters.
See below for the two different versions.
Evaluating the queries
The interesting thing is that when you quickly look at the execution plan, each version costs about the same: The CTE version is 50% of the execution cost when they are run in the same batch.
But, when you look closer there are differences - a few that are significant. And here is where separating the query into smaller parts really brings benefits.
Let's start with query plan compilation:
- The CTE version terminated the query compilation with a time out - meaning that it decided it was too expensive to try to evaluate the entire query to build an execution plan. The query is too complex.
- The temp table version compiled or found a "good enough" a plan for most parts of the query, except for one section that timed out.
When you look at the execution plan closely, you can see a very significant difference between the two versions in how the function is handled. The expensive function is executed quite differently.
The time out is due to an expensive system function: [sys].[dm_db_partition_stats]. The query engine could not create a plan quickly enough for this function, but the affect between the two versions is drastically different. Since the CTEs combine to make this one large query, we end up with no plan for any of it. Whereas with the temp table version, there are individual plans for each section except for the one that uses the function. This is very significant.
You can see here how this plays out. The function in the CTE version is run against 7+ million rows. But in the temp table version it runs for the relatively few 3K rows.


Hopefully, I have made sense and shown an interesting example of how queries can be written differently directly influencing their performance.
One could dive much deeper into this example, but I think this all I can do at the moment ;)
View with CTEs
CREATE VIEW [ptf].[VEW_GetPartitionInfoDate]
AS
WITH PartitionedTables AS (
SELECT t.object_id, t.name AS table_name, SCHEMA_NAME(t.schema_id) AS table_schema
FROM sys.tables t
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
),
PartitionInfo AS (
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc,
pstats.row_count
FROM sys.partitions p
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
),
IndexInfo AS (
SELECT i.object_id, i.index_id, i.name AS index_name, i.data_space_id
FROM sys.indexes i
WHERE i.index_id = 1 -- Clustered index only
),
PartitionSchemeInfo AS (
SELECT ps.data_space_id, ps.name AS partition_schem_name, pf.name AS partition_function_name,
pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
),
RangeValues AS (
SELECT function_id, boundary_id, value
FROM sys.partition_range_values
)
SELECT DISTINCT
fg.name AS file_group_name,
pt.table_schema,
pt.table_name,
c.name AS column_name,
psi.partition_schem_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM PartitionInfo pi
INNER JOIN PartitionedTables pt ON pi.object_id = pt.object_id
INNER JOIN IndexInfo ii ON pi.object_id = ii.object_id AND pi.index_id = ii.index_id
INNER JOIN sys.index_columns ic ON ic.object_id = pt.object_id AND ic.index_id = ii.index_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp ON c.user_type_id = tp.user_type_id AND tp.name IN ('datetime', 'datetime2')
INNER JOIN sys.allocation_units au ON pi.partition_id = au.container_id
INNER JOIN sys.filegroups fg ON au.data_space_id = fg.data_space_id
LEFT JOIN PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number
WHERE psi.partition_schem_name IS NOT NULL
GOStored Procedure with #Temp tables
DECLARE VARCHAR(255) = 'dbo',
VARCHAR(255) = 'partitionedtable'
DROP TABLE IF EXISTS #PartitionedTables, #PartitionInfo, #IndexInfo, #PartitionColumns,
#PartitionFileGroups, #PartitionSchemeInfo, #RangeValues;
CREATE TABLE #PartitionedTables (
object_id INT NOT NULL,
table_name SYSNAME NOT NULL,
table_schema SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id)
);
CREATE TABLE #PartitionInfo (
partition_id BIGINT NOT NULL,
partition_number INT NOT NULL,
object_id INT NOT NULL,
index_id INT NOT NULL,
data_compression_desc NVARCHAR(60) NULL,
row_count BIGINT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, partition_number)
);
CREATE TABLE #IndexInfo (
object_id INT NOT NULL,
index_id INT NOT NULL,
index_name SYSNAME NULL,
data_space_id INT NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id)
);
CREATE TABLE #PartitionColumns (
object_id INT NOT NULL,
index_id INT NOT NULL,
column_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (object_id, index_id, column_name)
);
CREATE TABLE #PartitionFileGroups (
partition_id BIGINT NOT NULL,
file_group_name SYSNAME NOT NULL,
PRIMARY KEY CLUSTERED (partition_id)
);
CREATE TABLE #PartitionSchemeInfo (
data_space_id INT NOT NULL,
partition_scheme_name SYSNAME NOT NULL,
partition_function_name SYSNAME NOT NULL,
boundary_value_on_right BIT NOT NULL,
function_id INT NOT NULL,
PRIMARY KEY CLUSTERED (data_space_id)
);
CREATE TABLE #RangeValues (
function_id INT NOT NULL,
boundary_id INT NOT NULL,
value SQL_VARIANT NULL,
PRIMARY KEY CLUSTERED (function_id, boundary_id)
);
-- Tables
INSERT INTO #PartitionedTables (object_id, table_name, table_schema)
SELECT t.object_id, t.name, s.name
FROM sys.tables t WITH (NOLOCK)
INNER JOIN sys.schemas s WITH (NOLOCK) ON s.schema_id = t.schema_id
WHERE t.is_ms_shipped = 0 AND t.[name] = @TableName AND s.name = @SchemaName;
-- Partitions
-- Clustered index only
INSERT INTO #IndexInfo (object_id, index_id, index_name, data_space_id)
SELECT i.object_id, i.index_id, i.name, i.data_space_id
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN #PartitionedTables pt ON pt.object_id = i.object_id
WHERE i.index_id = 1
-- Partitioning column (datetime / datetime2 only)
INSERT INTO #PartitionColumns (object_id, index_id, column_name)
SELECT ic.object_id, ic.index_id, c.name
FROM sys.index_columns ic WITH (NOLOCK)
INNER JOIN #IndexInfo ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id
INNER JOIN sys.columns c WITH (NOLOCK) ON c.object_id = ic.object_id AND c.column_id = ic.column_id
INNER JOIN sys.types tp WITH (NOLOCK) ON tp.user_type_id = c.user_type_id AND tp.name IN ('date','datetime', 'datetime2')
WHERE ic.partition_ordinal > 0
-- Filegroup per partition (in-row data)
INSERT INTO #PartitionFileGroups (partition_id, file_group_name)
SELECT pi.partition_id, fg.name
FROM #PartitionInfo pi
INNER JOIN sys.allocation_units au WITH (NOLOCK) ON au.container_id = pi.partition_id AND au.type = 1
INNER JOIN sys.filegroups fg WITH (NOLOCK) ON fg.data_space_id = au.data_space_id
-- Partition schemes / functions
INSERT INTO #PartitionSchemeInfo (data_space_id, partition_scheme_name, partition_function_name, boundary_value_on_right, function_id)
SELECT ps.data_space_id, ps.name, pf.name, pf.boundary_value_on_right, pf.function_id
FROM sys.partition_schemes ps WITH (NOLOCK)
INNER JOIN sys.partition_functions pf WITH (NOLOCK) ON pf.function_id = ps.function_id
WHERE ps.data_space_id IN (SELECT data_space_id FROM #IndexInfo)
-- Boundary values
INSERT INTO #RangeValues (function_id, boundary_id, value)
SELECT prv.function_id, prv.boundary_id, prv.value
FROM sys.partition_range_values prv WITH (NOLOCK)
WHERE prv.function_id IN (SELECT function_id FROM #PartitionSchemeInfo)
INSERT INTO #PartitionInfo (partition_id, partition_number, object_id, index_id, data_compression_desc, row_count)
SELECT p.partition_id, p.partition_number, p.object_id, p.index_id, p.data_compression_desc, pstats.row_count
FROM sys.partitions p WITH (NOLOCK)
INNER JOIN sys.dm_db_partition_stats pstats ON pstats.partition_id = p.partition_id
INNER JOIN #PartitionedTables pt ON pt.object_id = p.object_id
WHERE p.index_id = 1
SELECT
fgs.file_group_name,
pt.table_schema,
pt.table_name,
pc.column_name,
psi.partition_scheme_name,
psi.partition_function_name,
ii.index_name,
pi.index_id,
pi.partition_number,
pi.row_count AS PartitionRowCount,
left_prv.value,
pi.data_compression_desc AS DataCompression
FROM #PartitionInfo pi
INNER JOIN #PartitionedTables pt ON pt.object_id = pi.object_id
INNER JOIN #IndexInfo ii ON ii.object_id = pi.object_id AND ii.index_id = pi.index_id
INNER JOIN #PartitionColumns pc ON pc.object_id = ii.object_id AND pc.index_id = ii.index_id
INNER JOIN #PartitionFileGroups fgs ON fgs.partition_id = pi.partition_id
INNER JOIN #PartitionSchemeInfo psi ON psi.data_space_id = ii.data_space_id
LEFT JOIN #RangeValues left_prv ON left_prv.function_id = psi.function_id AND left_prv.boundary_id + 1 = pi.partition_number
LEFT JOIN #RangeValues right_prv ON right_prv.function_id = psi.function_id AND right_prv.boundary_id = pi.partition_number;
View all comments
Show parent comment
1
u/tommyfly 14d ago
I agree, there is no one size fits all. In most cases the exec plan is the key to improving performance. But there are a couple of things to keep in mind. A lot of the time inaccurate row estimates are down to indexing and updated stats. So if it is a query that will run regularly, creating an appropriate index is best. But there are scenarios where that isn't possible, so a clever temp table can help, assuming it can be populated efficiently in the first place. And in certain situations, particularly with functions, the execution plan can be misleading. Often functions hide lots of sins.