0

I am struggling with query that takes long time. Just briefly, I have got two CTE's that have not much data and joining them take long time (about 10 seconds). But when I pack each cte into pure temp table like this:

with cte as (...)
select * into tbl_cte from cte

and then join temp tables, everything works very fast.

I can't understand why this approach is faster. I don't use either an identity column or primary key on temp tables.

tylkonachwile
  • 2,025
  • 4
  • 16
  • 28
  • check execution plans - the table could have out of date statistics, leading to a poor execution plan - or it could just be pure bad luck that the execution plan for the CTE is not as good as the temp table. – Cato Jul 16 '18 at 10:36
  • 1
    CTEs aren't tables, temporary or otherwise. They are just subqueries. Hence, there's absolutely no point in comparing joins between two tables and joins between subqueries. A subquery does the job your code did to *load* the data into the temporary table. Did you include that in the comparison? – Panagiotis Kanavos Jul 16 '18 at 10:44
  • Without the executions plans we can't really help. Use this [paste the plan link](https://www.brentozar.com/pastetheplan/) to share the plans for the various approaches – Mazhar Jul 16 '18 at 10:45
  • rather that giving your query as `...`, which is utterly useless to every volunteer here, actually provide the full SQL. Otherwise we have no idea what your SQL is doing. The execution plan(s) will also really help. [Paste the Plan](https://www.brentozar.com/pastetheplan/) – Thom A Jul 16 '18 at 10:45
  • The database engine has a couple different ways to deal with CTE's. It may choose to do them as a join - which you want - or as a subquery on each row - which becomes much slower. You can't always force one or the other. Excuse me for tooting my own duplicate horn: https://stackoverflow.com/questions/24143372/why-is-this-cte-so-much-slower-than-using-temp-tables/24152879#24152879. – Mike M Jul 16 '18 at 13:20
  • Possible duplicate of [Why is this CTE so much slower than using temp tables?](https://stackoverflow.com/questions/24143372/why-is-this-cte-so-much-slower-than-using-temp-tables) – Mike M Jul 16 '18 at 13:20
  • You'll see what the database engine chose to do in Execution Plans. If you haven't used the Plans before, here is a (long but clear) introduction to what they mean and how to look at them: https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/ . And here is the shorter version on SO: https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – Mike M Jul 16 '18 at 13:23

0 Answers0