Common Table Expression slow

Giganews Newsgroups
Subject: Common Table Expression slow
Posted by:  tshad (t…@dslextreme.com)
Date: Mon, 18 Oct 2010

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

Replies