CTE Vs Temp Table in Yukon

Hi All,

I would like to know which gives better performance: CTE or Temporary Table?

Thanks,

Suresh

I wanted to add another information here.

When I replaced Temporary Tables with CTE in my query, it took more execution time. For ex., the query using Temp Table took 1 min 5 secs. The query using CTE took 4 mins. The no. of records hold by temp table in my query is apprx. 44000.

Why CTE is slower?

Suresh

|||

Check the execution plan, that is the only way you will find out. Clearly the CTE is using a different plan. You can force the execution plan to use a certain order using the FORCE command or option force_order at the end of the query.

Clarity Consulting (www.claritycon.com)

|||You cannot compare CTE and temporary table. They are different beasts. There are cases where you can break a complex query into simpler parts using temporary tables and get better performance. I am not sure how you used CTE in your query so it is hard to say. Note that CTEs also provide the capability to perform recursive queries in a declarative manner. And what are you measuring regarding the performance? Is it the temporary table creation vs query using CTE returning rows? If you are just measuring creation part then it doesn't include the time taken to send results to whatever client you are using. You need to elaborate on the actual problem. Best is to post a sample script that reproes the performance problem.