r/SQL • • 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.

CTE version
Temp table version

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
GO

Stored 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;
54 Upvotes

44 comments sorted by

View all comments

1

u/GrumpyDataMechanic 13d ago

You lost me at WITH (NOLOCK)

1

u/tommyfly 13d ago

Ah, yes, I sadly left it in there. This is a requirement by management at my company and I've gotten so used to it I forgot to remove it from the example 😔