CTE query plan for complex query - triple run of the same init query - why?

Question

It's difficult to explain, but I'll try.

As you see on query_plan picture attached (It is query plan for "All in one place" query described below), there are 3 almost the same "blocks" - my question is WHY? It seems to me that when I have "all in one" (see below) query the "Init" block (that is rather heavy) is run three times with different filters instead of being SPOOLED and reused later.

This Query exec time is about 45secs. It query could be presented in a form:

-- Complex "All in One place" Query
WITH init as (
  Init1 complex query here  -- (10 sec to run) if executed alone
)
, step1 as ( select * from init .. joins... where ... etc ), 
step2 as ( select *, row_number() over(__condition__) as rn from step1 where _filter1_)
, step3 as ( select * from step2 where __filter2_), 
.... some more steps could be here ....
select * 
into target_table
from step_N;
-- 45sec CPU time 

The important thing here is that I use those Step1, Step2, ..., StepN tables within "WITH" clause sequentially - Step 1 uses INIT table, so Step2 uses Step1 table, Step3 uses Step2 Table etc. I need this because of different rankings I process after every step that is used later for filtering.

If change this complex CTE query to (I put the result of Init query into table, then process other Steps without change):

-- Complex query separated from the rest of the query
 with Init as ( 
   The same Init1 complex query here
) 
select * 
into test_init 
from init;
-- 10sec CPU time

with step1 as ( select * from test_init .. joins... where ... etc ), 
step2 as ( select *, row_number() over(__condition__) as rn from step1 where _filter1_)  , 
step3 as ( select * from step2 where __filter2_), 
.... some more steps could be here ....
select * 
into target_table
from step_N; 
-- 5sec CPU time

I got about 15secs of exec time, that seems OK to me. Because 10sec is the 1st complex query that is difficult to improve.

So as a result I cant get this MS Sql server 2005 behavior? Could somebody explain this to me? It's rather interesting, I suppose!

Answer 1

Looks like the optimizer thinks it would be faster to run the query thrice with different conditions. The optimizer is not always right.

It's actually pretty common to use a temporary table to force SQL Server to do the whole complex query first. Usually, you'd use a temporary table instead of test_init:

insert into #temptbl select * from Init

A temporary table is also used by SQL Server to store the results of joins and subqueries. Using a temporary table does not negatively impact performance.

Answer 2

SQL Server generally doesn't materialize the result of Common Table Expressions.

It is possible to get it to spool the results though with some messing about (Article by Stack Overflow User Quassnoi).

There is a proposal on the Microsoft Connect site to make the NOEXPAND hint usable with CTEs to force this behaviour.