From: tfs@dslextreme.com   
      
   I have a common table expression that runs VERY slow.   
      
   The CTE query definition section takes about 2 seconds to run and has 99   
   rows in it.   
      
   But then I do a Select from it where I am looking for the latest date and it   
   takes about 30 minutes to run.   
      
   The query looks something like:   
      
   WITH MyQuery   
   AS   
   (   
   SELECT ...   
   )   
   SELECT  RetailerName 'Retailer'   
    ,sQuestionText 'Question'   
    ,sChoiceText 'Response Choice'   
   -- ,COUNT(*)   
   FROM MyCTE   
   WHERE (@Status = 'ALL') OR ((@Status <> 'ALL') AND (dStart = (Select   
   MAX(dStart)   
        from MyCTE   
        WHERE  MyCTE.RetailerName = RetailerName and   
            MyCTE.StoreID = StoreID and   
            MyCTE.nQuestionID = nQuestionID)))   
      
   If I do the @Status = 'ALL' (where all the rows are returned) takes about 3   
   seconds.  If I have @Status = 'ALL' (where only the group with the largest   
   date is returned - should return 92) - it takes over 30 minutes to run.   
      
   If I replace the CTE with a temporary table or temporary variable - it takes   
   about 6 seconds.   
      
   How can that be???   
      
   I thought the CTE actually creates temp table internallly.   
      
   The only change I make to the last SELECT statment is to change MyCTE to   
   #table.   
      
   I also tried to use a temporary variable and changed all the reference form   
   #table to @table (as well as change the declaration of the table) and it   
   works fine if I don't have the where clause, but the where clause:   
      
   WHERE (@sRecordStatus = 'ALL') OR ((@sRecordStatus <> 'ALL') AND (dStart =   
   (Select MAX(dStart)   
        from @tfs   
        WHERE  @tfs.Retailer = Retailer and   
            @tfs.StoreID = StoreID and   
            @tfs.QuestionID = QuestionID)))   
      
   I get the error:   
      
   Must declare the scalar variable "@tfs"   
      
   Does that mean you can't use temporary variables in a sub query???   
      
   Thanks,   
      
   Tom   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)