none
CTE Vs Temp Table in Yukon RRS feed

  • Question

  • Hi All,

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

    Thanks,

    Suresh

    Monday, February 6, 2006 10:08 AM

Answers

  • 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.
    Wednesday, February 8, 2006 12:54 AM

All replies

  • 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

    Monday, February 6, 2006 11:23 AM
  • 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)

    Monday, February 6, 2006 6:18 PM
  • 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.
    Wednesday, February 8, 2006 12:54 AM