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

I was recently in a vacuum PARENT_ID the root record using data about CTE and with no true roots Record The question is asked (ie instead it is parented for self)

The question is here the link;

This question has been answered and now I have data, although I am interested in the difference of two ways which I think are available to me.

From the point of view I did get the required data, it was made with data to create a temporary table, which is clear to parents and then a recursive CTE down Visible;

  select case PARENT_ID = Party_Id then zero PARENT_ID end Act_Parent_Id, Party_Id, PARTY_CODE, PARTY_NAME investigation DIMENSION_PARTIES select (with #Parties where CURRENT_RECORD = 1), linkedParties Act_Parent_Id, Party_Id, PARTY_CODE, PARTY_NAME, as the level of 0 #Parties where Act_Parent_Id zero UNION select all p.Act_Parent_Id, p.Party_Id, p.PARTY_CODE, p.PARTY_NAME, level + 1 linkedParties t inner p #Parties P choosing to join .Act_Parent_Id = t.Party_Id) or * the level of orders linkedParties  

I have the same data defines the two CTE again Attempted to get: Simulate the construction of a temporary table above one and the other for the same recursive work but instead of a floating table to refer to the initial CTE; As the parties (when selecting PARENT_ID = Party_Id then ACT_Parent_Id as NULL ELSE Parent_Id END, Party_ID, PARTY_CODE, DIMENSION_PARTIES from PARTY_NAME, where CURRENT_RECORD = 1), Linkpeak's AS ( Akt_ Parent_Id, Parti_aidi, PARTY_CODE, PARTY_NAME, 0 aS lEVEL choose WHERE the parties Act_Parent_Id NULL UNION ALL Select p.Act_Parent_Id, p ​​.Party_Id, p.PARTY_CODE, p.PARTY_NAME, level parties + 1 P inner p.Act_Parent_Id = t. Join LinkedParties at Party_Id) Select * LinkedParties from order level

Now these two scripts are the same Moving on Hwar though Tempp table results from the perspectives are born in approximately 15 seconds.

Multiple CTE approaches take 5 minutes (for so long that I've never waited for the results to return).

Is there any ri son? Why would the temp table approach be so sharp?

What matters to it, I believe it is to do with record numbers. There are 200K records in the base table and the display of CTE from memory is seriously degraded while dealing with large data sets, but I think it seems that I did check with the experts, unable to prove it It is possible.

Thank you very much

OK because there is no clear answer for this, something in the generic of the topic And the research threw a number of similar threads with the number of other threads.

This includes many variations between a floating table and CTE, so it is very useful for people to read their issues;

In my case, it will appear that my CTE will cause large amounts of data because it is not cached anywhere and so whenever it is referred to After making it again it will have a big effect.