title: Joining Onto CTE Performance
body: <p>I have a stored procedure where I use a Common Table Expression to build a hierarchical path up a menu (so it can display something like Parent Menu -> Sub Menu -> Sub Sub Menu -> ...)</p>
  
  <p>It works great for what I want to use it for, the issue comes when putting the information I get from the recursive CTE into the information I really want. I do an Inner Join from my Data to the CTE and get out the Hierarchical Path. For something that returns ~300 rows, the stored procedure takes on average 15-20 seconds.</p>
  
  <p>When I insert the results from the CTE into a Temp Table and do the join based on that, the procedure takes less than a second.</p>
  
  <p>I was just wondering why it takes so long to join using only the CTE, or if I am misusing CTE's in some way.</p>
  
  <p>**Edit this is the stored procedure essentially</p>
  
  <pre><code>With Hierarchical_Path (Menu_ID, Parent_ID, Path) 
  
  As
  (
  Select
      EM.Menu_Id, Parent_ID, 
              Convert(varchar(max), 
              EM.Description) as Path
  From
      Menu EM
  Where
  --EM.Topic_No is null
      EM.Parent_ID = 0 and EM.Disabled = 0
  Union All
  Select  
      EM.Menu_ID,  
              EM.Parent_ID, 
              Convert(Varchar(max),E.Path + ' -&gt; ' + EM.Description) as Path
  From
      Menu EM
  Inner Join
      Hierarchical_Path E
  On
      EM.Parent_ID = E.Menu_ID    
  )
  
  SELECT distinct   
      EM.Description
      ,EMS.Path
  FROM
      dbo.Menu em
  INNER JOIN
      Hierarchical_Path EMS
  ON
      EMS.Menu_ID = em.Menu_Id
      2 more INNER JOINs
      2 Left Joins
      WHERE Clause
  </code></pre>
  
  <p>When I run the query like this (joining onto the CTE) the performance is around 20 seconds.</p>
  
  <p>When I insert the CTE results into a temp table, and join onto that, the performance is instantaneous.</p>
  
  <p>Taking apart my query a bit more, it seems like it gets hung up on the where clause. I guess my question is more to the point of when exactly does a CTE run and does it get stored in memory? I was running under the assumption that it gets called once and then sticks around in memory, but under some circumstances could it be called mulitple times?</p>