RE: CTE Vs Temporary table or Table variable RRS feed

  • Question


    Recently I recommended to our development group the use of CTE in place of temporary table or table variable and the benefits of CTE. We implemented CTE in production to help leverage execution time for 1 particular query. 2 months later this query that used to perform in 1-2 seconds is now performing in 100+ seconds. Looking at the query when it’s run thru query analyzer, I see it waiting on CXPACKET which denotes it’s waiting on CPU, while the I/O and Memusage is at 0. I see no blocking and the spid is showing as suspended. So, I am baffled as to why all of a sudden the CTE has degraded in performance. We modified the query to run using table variable and it's running fine so far.


    Any ideas?



    Here is the sample code:







    DECLARE @QueueProcessIdNos varchar(4000)


    SELECT @QueueProcessIdNos = '<Ids><Id>-10139</Id><Id>-10138</Id><Id>-10137</Id><Id>-10136</Id><Id>-10135</Id><Id>-10134</Id><Id>-10133</Id><Id>-10132</Id><Id>-10131</Id><Id>-10130</Id><Id>-10129</Id><Id>-10128</Id><Id>-10127</Id><Id>-10126</Id><Id>-10125</Id><Id>-10123</Id><Id>-10122</Id><Id>-10121</Id><Id>-10120</Id><Id>-10119</Id><Id>-10118</Id><Id>-10117</Id><Id>-10116</Id><Id>-10115</Id><Id>-10114</Id><Id>-10113</Id><Id>-10112</Id><Id>-10111</Id><Id>-10110</Id><Id>-10109</Id><Id>-10108</Id><Id>-10107</Id><Id>-10106</Id><Id>-10105</Id><Id>-10104</Id><Id>-10103</Id><Id>-10102</Id><Id>-10101</Id><Id>-10100</Id></Ids>'




    DECLARE @QueueJobTimestamp TIMESTAMP;




    WITH QueueProcessesFilter(QueueProcessIdNo) AS 




               SELECT IdNodes.IdNode.value('.[1]', 'int') FROM 


               (SELECT Convert(xml, @QueueProcessIdNos)) AS IdDoc(IdDocument)  


               CROSS APPLY IdDocument.nodes('/Ids/Id') IdNodes(IdNode) 




           SELECT TOP 1


             @JobIdNo = QueueJobIdNo,


             @QueueJobTimestamp = QueueJobTimestamp




              tQUEUE_JOBS qj WITH (NOLOCK)


              INNER JOIN tQUEUE_PROCESSES qp ON qp.QueueProcessIdNo = qj.QueueProcessIdNo 


              INNER JOIN QueueProcessesFilter qpf on qp.QueueProcessIdNo = qpf.QueueProcessIdNo 




              QueueJobStatusIdNo = @JOB_STATUS_READY_TO_PROCESS AND 


              GetDate() >= QueueJobScheduledStartDate 


           ORDER BY QueueJobActualStartDate 


    SELECT @JobIdNo , @QueueJobTimestamp



    Wednesday, January 9, 2008 3:18 PM


All replies

  • Here is a discussion that talked a bit about the subject that I remember from a bit over a year ago:



    and also



    ( Keywording CTE and sugar so that I can find this post again later.   )


    Also, welcome to the SQL forum!

    Wednesday, January 9, 2008 3:42 PM
  • Couple of questions: Are you using SQL 2005 and is this in a procedure or ad hoc sql? Also, what do you have for indexes to filter by QueueJobScheduledStartDate and QueueJobStatusIdNo?
    Wednesday, January 9, 2008 3:55 PM
  • The problem is that, the look and feel of a CTE is just like a temporary/memory table. However, it is just a view which is expanded inline and executed.


    btw; Kent, could you tell me how to tag a post with a specific keyword?

    Wednesday, January 9, 2008 4:01 PM
  • In this case I just left the words "CTE" and "sugar" as extraneous words in my response so that I could tag it.  The "sugar" comment has always stuck out in my mind relating to these two previous posts.  In this case it is an odd term that for me is easy to remember.  Sorry if I have added confusion here, but this issue has popped up several times and is likely to come around again in about a year or so. 

    Wednesday, January 9, 2008 4:05 PM
  • is there a way to tag a post in this forum (thread or post) with a specifc keyword so that we can easily find it at a later date? at present i am putting it in

    Wednesday, January 9, 2008 6:03 PM
  • Hi Guys,

    Even I was doing performance tuning for a SP. There I gad 10 Common Tables Joined seperetaly with Table1 & Table2. SP was Like this

    Select * From

    Table1 Inner Join (10 common tables)

    Union All

    Select * From

    Table2 Inner Join (10 common tables)

    In This case, What I did, Instead of hitting DB 2 times for these 10 common tables, I took result set from These 10 common tables & put in #Temp Table but My DBA was not allowing for using temp table bez it will increase Temp DB size & in future it will create problem & to get rid of this they need to re-start server.

    Then Instead of using Temp Table I used CTE in SP & It was giving similar performance as temp.

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, July 11, 2012 7:27 AM