Common Table Expressions slow when using a table variable
- by Phil Haselden
I have been experimenting with the following (simplified) CTE. When using a table variable () the query runs for minutes before I cancel it. Any of the other commented out methods return in less than a second.
If I replace the whole WHERE clause with an INNER JOIN it is fast as well.
Any ideas why using a table variable would run so slowly?
FWIW: The database contains 2.5 million records and the inner query returns 2 records.
CREATE TABLE #rootTempTable (RootID int PRIMARY KEY)
INSERT INTO #rootTempTable VALUES (1360);
DECLARE @rootTableVar TABLE (RootID int PRIMARY KEY);
INSERT INTO @rootTableVar VALUES (1360);
WITH My_CTE AS
(
SELECT ROW_NUMBER() OVER(ORDER BY d.DocumentID) rownum, d.DocumentID, d.Title
FROM [Document] d
WHERE d.LocationID IN
(
SELECT LocationID
FROM Location
JOIN @rootTableVar rtv ON Location.RootID = rtv.RootID -- VERY SLOW!
--JOIN #rootTempTable tt ON Location.RootID = tt.RootID -- Fast
--JOIN (SELECT 1360 as RootID) AS rt ON Location.RootID = rt.RootID -- Fast
--WHERE RootID = 1360 -- Fast
)
)
SELECT * FROM My_CTE WHERE (rownum > 0) AND (rownum <= 100) ORDER BY rownum
