How to use Common Table Expression properly - Example where it seems to be very slow in TSQL I've been starting using common table expressions more and more and thought I was using them "right" but I've run into an example where the solution with CTE is very slow compared to using a temp table. I will put the two examples below, the first one using common table expressions takes a very long time to run (3min 27sec) and the second one using temp tables which only takes 0:50. Is this an inappropriate way to use CTE or am I implementing it poorly?

;WITH tmpRiskFactor AS  --Super slow. 3:27
(SELECT col1,col2,col3,col4,col5 FROM [tablevaluedfunction]() )
(SELECT DISTINCT col1 AS [RiskFactors] FROM tmpRiskFactor
UNION
SELECT DISTINCT col2 FROM tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM tmpRiskFactor
) AS tmp;


Using temp:

SELECT col1,col2,col3,col4,col5 INTO #tmpRiskFactor FROM [tablevaluedfunction]() --A lot faster. 0:50
SELECT * INTO #RiskFactors FROM 
(SELECT DISTINCT col1 AS [RiskFactors] FROM #tmpRiskFactor
UNION
SELECT DISTINCT col2  FROM #tmpRiskFactor
UNION
SELECT DISTINCT col3 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col4 FROM #tmpRiskFactor
UNION
SELECT DISTINCT col5 FROM #tmpRiskFactor
) AS tmp;
To expand a bit more on Jonathan's comment, each time you reference a CTE, the query within the CTE will be called.

Each UNION you are using is creating a separate execution of the CTE, thereby executing the table value function each time as well. By putting the results into a temp table, you are only then reading the static data in the temp table for each UNION and avoid recalculating the data.

If you check the execution plan for each of the queries, you'll be able to see a visual confirmation of this fact. 

In your case, it makes much more sense to use a temp table as illustrated by the vastly different execution times. I personally find non-recursive CTEs rarely offer much by way of performance and are usually more helpful for readability  and logical separation of query contents.