|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:
SELECT RetailerName 'Retailer'
,sChoiceText 'Response Choice'
WHERE (@Status = 'ALL') OR ((@Status <> 'ALL') AND (dStart = (Select
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
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 =
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???