33

Do unused CTEs in queries affect performance and / or alter the generated query plan?

0
22

It doesn't appear that they do, but this really only applies to nested CTEs.

Create two temp tables:

CREATE TABLE #t1 (id INT);
INSERT #t1 ( id )
VALUES ( 1 );

CREATE TABLE #t2 (id INT);
INSERT #t2 ( id )
VALUES ( 1 );

Query 1:

WITH your_mom AS (
    SELECT TOP 1 *
    FROM #t1 AS t 
),
also_your_mom AS (
    SELECT TOP 1 *
    FROM #t2 AS t
)
SELECT *
FROM your_mom;

Query 2:

WITH your_mom AS (
    SELECT TOP 1 *
    FROM #t1 AS t 
),
also_your_mom AS (
    SELECT TOP 1 *
    FROM #t2 AS t
)
SELECT *
FROM also_your_mom;

Query plans:

NUTS

There is an overhead, but the unnecessary portion of the query is eliminated very early (during parsing in this case; the simplification stage in more complex cases), so the additional work is truly minimal, and does not contribute to potentially expensive cost-based optimization.

0
29

+1 to Erik, but wanted to add two things (which did not work well in a comment):

  1. You don't even need to look at execution plans to see that they are ignored when not used. The following should produce a "divide by 0" error but does not due to cte2 not being selected from at all:

    ;WITH cte1 AS
    (
      SELECT 1 AS [Bob]
    ),
    cte2 AS (
      SELECT 1 / 0 AS [Err]
      FROM cte1
    )
    SELECT *
    FROM   cte1;
    
  2. CTE's can be ignored, even if they are the only CTE, and even if they are selected from, if logically all rows would be excluded anyway. The following is a case where the query optimizer knows ahead of time that no rows could be returned from the CTE, so it doesn't even bother to execute it:

    ;WITH cte AS
    (
      SELECT 1 / 0 AS [Bob]
    )
    SELECT TOP (1) [object_id]
    FROM   sys.objects
    UNION ALL
    SELECT cte.[Bob]
    FROM   cte
    WHERE  1 = 0;
    

Regarding performance, the unused CTE is parsed and compiled (or at least compiled in the case below), so it is not 100% ignored, but the cost would have to be negligible and not worth being concerned about.

When only parsing, there is no error:

SET PARSEONLY ON;

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET PARSEONLY OFF;
GO

When doing everything just short of execution, then there is a problem:

GO
SET NOEXEC ON;
GO

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET NOEXEC OFF;
GO
/*
Msg 207, Level 16, State 1, Line XXXXX
Invalid column name 'NotHere'.
*/
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.