SQL - why this CTE expression hangs while temp table runs fine
I have below SQL CTE statement which i found to be a bottleneck for the
performance. While debugging, it just hangs there (I think it does table
scan) so i replaced it with a temp table and the query runs fine. I wanted
to know if there is some difference in the way CTE expression is written
which is making the statement hand. I know CTEs have some performance
attached but i don't think i am doing anything special in the below query
to make CTE give me such a bad performance.
;with ContList (ContKey, CKey, CreatedDate, DeletedDate, SourceId) AS
(
SELECT ContKey, CKey, CreatedDate, DeletedDate, SourceId FROM #someTempTable
UNION ALL
SELECT list.ContKey AS ContKey,
fact.CKey AS CKey,
case when fact.CreatedDate > list.CreatedDate then fact.CreatedDate
else list.CreatedDate end AS CreatedDate,
case when isnull(fact.DeletedDate, '9999/01/01') <
isnull(list.DeletedDate, '9999/01/01') then fact.DeletedDate else
list.DeletedDate end AS DeletedDate,
fact.DataSourceDimKey As SourceId
FROM ContList list
INNER JOIN SomeFact fact ON list.CKey = fact.DimKey
INNER JOIN SomeDimvw someDim on someDim.SomeKey = fact.SomeKey
INNER JOIN #contTypes contTypes on someDim.SomeTypeId = contTypes.SomeTypeId
WHERE list.DeletedDate IS NULL
)
I replaced the above query with this:
SELECT ContKey, CKey, CreatedDate, DeletedDate, SourceId FROM #someTempTable
UNION ALL
SELECT list.ContKey AS ContKey,
fact.CKey AS CKey,
case when fact.CreatedDate > list.CreatedDate then fact.CreatedDate
else list.CreatedDate end AS CreatedDate,
case when isnull(fact.DeletedDate, '9999/01/01') <
isnull(list.DeletedDate, '9999/01/01') then fact.DeletedDate else
list.DeletedDate end AS DeletedDate,
fact.DataSourceDimKey As SourceId
into #ContList
FROM ContList list
INNER JOIN SomeFact fact ON list.CKey = fact.DimKey
INNER JOIN SomeDimvw someDim on someDim.SomeKey = fact.SomeKey
INNER JOIN #contTypes contTypes on someDim.SomeTypeId = contTypes.SomeTypeId
WHERE list.DeletedDate IS NULL
)
No comments:
Post a Comment