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

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

Up vote 1 down vote favorite share g+ share fb share tw.

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! Sql-server-2005 tsql query-plans link|improve this question asked Oct 1 '10 at 21:50zmische1599 79% accept rate.

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.

We use tables sometimes for QA purposes later. Temp Tables is used where appropriate. Hmmm... Is there a way to "tell" MS SQL that it's better to put the result in spool and reuse?

Write a Plan, Hints, something? – zmische Oct 1 '10 at 22:08 @zmische - It might well be possible with plan hints but remember the spool will just be a work table in tempdb anyway. – Martin Smith Oct 1 '10 at 22:23 Yeah, You are right.

But I'm about doing the work in one query, without additional select .. into from. Even more if it's the same from MS SQL server point of View. %) Thx anyway!

– zmische Oct 1 '10 at 22:59.

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.

NOEXPAND don't work on MSQ SQL 2005. OPTION (NOEXPAND) either. From mS connect article.

Thx for mentioning Quassnoi's articles - they are brilliant! – zmische Oct 1 '10 at 22:21 @zmische - It doesn't work on any SQL Server Version - Vote for it if you want it! Agreed on the articles.

– Martin Smith Oct 1 '10 at 22:24 Sorry, my mistake. I'll vote! – zmische Oct 1 '10 at 22:35 @zmische, @Martin Smith: It's currently be WITH (NOEXPAND) because it's a table hint... – gbn Oct 2 '10 at 8:04 @gbn: I tried with (noexpand) for queries in every step - the plan still the same.

Perhaps Table hints are not taken into account by Optimizer for this case. – zmische Oct 2 '10 at 9:58.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.