SQL Server Performance

Multiple CTE Vs Temp Table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Sep 13, 2010.

  1. atulgoswami New Member

    Approach 1: CTE
    ;With CT1 AS
    (Select A.ID AS A_ID from Tab1 A)
    , CT2 AS
    (Select B.ID AS B_ID from Tab2 B INNER JOIN CT1 ON CT1.A_ID = B.AID)
    , CT3 AS
    (Select C.ID from Tab3 C INNER JOIN CT1 ON C.AID = CT1.A_ID INNER JOIN CT2 ON C.BID = CT2.B_ID)
    These three CTEs are used in one final query
    Approach 2: Temp Table/Table Variable
    I put all the data in three different temp tables and use in final query.
    I generated the execution plan with both the approaches but i am confused with size of execution plan generated with CTE approach. It is huge and in one case, it is not returning any data, but still plan is huge.
    I am thinking why it is huge and second could this be the cause of poor performance of the query with CTE approach.
    Thanks in advance
    -Aero
  2. FrankKalis Moderator

    There could be many reasons why the CTE approach might be slower, but for that we need more information.Especially where the data in the table comes from.
    From my experience, CTE are slow when they operate on large tables. I had a construct where I determine Close/High/Low prices for stocks on a 45+ million row table. Running the CTEs against that table took ~30 seconds. I then decided to extract the data of interest into a temp table first and run the CTEs against table and the runtime is now less than 10 seconds which is good enough for me as this is a nightly batch only.
  3. satya Moderator

    What is the main purpose of that code and outcome you wanted to see?
    I believe the performance is a criteria here in that case the underlying hardware, number of times the proces si called by application and volume within the tables will dictate overall performance.

Share This Page