tsql - Why is performance of CTE worse than temporary table in this example -

tsql - Why is performance of CTE worse than temporary table in this example -

i asked question regarding cte's , using info no true root records (i.e instead of root record having null parent_id parented itself)

the question link here; creating recursive cte no rootrecord

the reply has been provided question , have info require interested in difference between 2 approaches think available me.

the approach yielded info required create temp table cleaned parenting info , run recursive cte against. looked below;

select case when parent_id = party_id null else parent_id end act_parent_id , party_id , party_code , party_name #parties dimension_parties current_record = 1), linkedparties ( select act_parent_id, party_id, party_code, party_name, 0 level #parties act_parent_id null union select p.act_parent_id, p.party_id, p.party_code, p.party_name, level + 1 #parties p inner bring together linkedparties t on p.act_parent_id = t.party_id ) select * linkedparties order level

i attempted retrieve same info defining 2 cte's. 1 emulate creation of temp table above , other same recursive work referencing initial cte rather temp table;

with parties (select case when parent_id = party_id null else parent_id end act_parent_id , party_id , party_code , party_name dimension_parties current_record = 1), linkedparties ( select act_parent_id, party_id, party_code, party_name, 0 level parties act_parent_id null union select p.act_parent_id, p.party_id, p.party_code, p.party_name, level + 1 parties p inner bring together linkedparties t on p.act_parent_id = t.party_id ) select * linkedparties order level

now these 2 scripts run on same server temp table approach yields results in approximately 15 seconds.

the multiple cte approach takes upwards of 5 minutes (so long in fact have never waited results return).

is there reason why temp table approach much quicker?

for worth believe record counts. base of operations table has 200k records in , memory cte performance severely degraded when dealing big info sets cannot seem prove thought i'd check experts.

many thanks

well there appears no clear reply farther research generics of subject threw number of other threads similar problems.

this 1 seems cover many of variations between temp table , ctes useful people looking read around issues;

which more performant, cte or temporary tables?

in case appear big amount of info in ctes cause issue not cached anywhere , hence recreating each time referenced later have big impact.

tsql common-table-expression