32

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

21

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.

28

+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'.
*/
  • Wish I could mark more than one answer as correct, but Erik beat you to the pistol draw. :) But your answer is very informative and great as well, thank you! – J.D. Mar 2 at 2:25
  • What if the CTE's are in a View and the view is nested more than 3 times? Isn't there a point where optimizer gives up and runs all? – Zikato Apr 10 at 13:20
  • @Zikato I have no idea, but that's a great question. You should be able to set up a test without too much effort by creating a view using the divide by zero trick I showed in the first two examples. Please let me know the results as I am very curious now about this scenario :-). – Solomon Rutzky Apr 10 at 14:21
  • @SolomonRutzky To be fair, I did test it, but it was not conclusive. I've created a view from your cte example and nested it 5 times, but since it's all constant scan and not really complicated, the optimizer handled it well. I would like to test it more thoroughly in the future and hide it behind more complex logic. I'll let you know. – Zikato Apr 10 at 14:26
  • @Zikato Interesting. Not sure what would be considered "complex", but yes, my example is very simplistic. When you say "nested it 5 times", do you mean in other views / procs that call each other and it was 5 deep, or in subqueries / CTEs? I think there's the possibility that nesting enough levels might skip it, but not due to it not being referenced but instead due to a higher nest level not using it and that being assumed for lower levels. I have seen where the trick of putting NEWID() in a view to use in a UDF can return the same value from multiple calls due to the optimizer caching it. – Solomon Rutzky Apr 10 at 14:49

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.