CTEs Aren’t Procedural, Either

Mythology


I’ve heard many times incorrectly over the years that CTEs somehow materialize data.

But a new one to me was that CTEs execute procedurally, and you could use that to influence plan shapes by always doing certain things first.

Unfortunately, that’s not true of them either, even when you use TOP.

Meaningful Life


Here’s the first example. Take some note of the order the CTEs are written and joined in, and the tables they touch.

Outside of the CTEs, there’s a join to a table not even in a CTE here.

WITH cte_1 AS 
(
SELECT u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 1
),
     cte_2 AS
(
SELECT p.OwnerUserId, p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
),   cte_3 AS 
(
SELECT v.PostId
FROM dbo.Votes AS v --WITH(INDEX = three)
WHERE v.VoteTypeId = 4
)
SELECT COUNT(*)
FROM cte_1
JOIN cte_2
    ON cte_2.OwnerUserId = cte_1.Id
JOIN cte_3 
    ON cte_3.PostId = cte_2.Id
JOIN dbo.Comments AS c
    ON c.UserId = cte_1.Id;

The plan for it looks like this:

OOW

Not even close to happening in the order we wrote things in.

Darn that optimizer.

Machanically Speaking


If we use a TOP in each CTE, that doesn’t help us either.

WITH cte_1 AS 
(
SELECT TOP (2147483647) u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 1
),
     cte_2 AS
(
SELECT TOP (2147483647) p.OwnerUserId, p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
),   cte_3 AS 
(
SELECT TOP (2147483647) v.PostId
FROM dbo.Votes AS v
WHERE v.VoteTypeId = 4
)
SELECT COUNT(*)
FROM cte_1
JOIN cte_2
    ON cte_2.OwnerUserId = cte_1.Id
JOIN cte_3 
    ON cte_3.PostId = cte_2.Id
JOIN dbo.Comments AS c
    ON c.UserId = cte_1.Id;

Tables get touched in the same order, but the plan takes an ugly turn:

bidtime

Dis-spells


CTEs have no magic powers. They don’t boss the optimizer around, they don’t materialize, and they don’t fence optimization.

If you’re gonna start stacking these things together, make sure you’re doing it for a good reason.

And if you tell me it’s to make code more readable, I know you’re messing with me.

Thanks for reading!



2 thoughts on “CTEs Aren’t Procedural, Either

Leave a Reply

Your email address will not be published. Required fields are marked *