Common Table Expressions slow when using a table variable

Question

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

This is from when using the table variable. The query took over 17 minutes to run: enter image description here

Execution plans in XML format

Temp table: https://docs.google.com/open?id=0B66I-fxlyEtEZEthV3ZaWlNLWXM

Table variable: https://docs.google.com/open?id=0B66I-fxlyEtEbUFZa3RJejFCTkk

Post the query plans for the table variable and the temp table versions. — RBarryYoung
this is actually the first time i've seen a primary key inside a table variable. i'm curious - what does the execution plan have to say ? — YS.
I let the table variable version run to completion: 17m 41s. Any particular format you would like for the execution plan? BTW - the primary key was just something I was playing around with to see if it would make a difference. It didn't seem to. — Phil Haselden
Please post the execution plans in XML. If possible as a link to a file share (e.g. DropBox or google docs). — Sebastian Meine
Don't forget the #temp table version to. We need them for comparison. — RBarryYoung

RBarryYoung RBarryYoung · Accepted Answer · 2012-11-05T17:12:38

What the query plans make clear is that the Table Variable version is suffering from the notorious "table variable cardinality estimation" problem. This occurs primarily because, unlike temp tables, table variables do not support statistics. This article by Paul White explains it in exquisite detail:

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

What that also makes clear is that the simplest solution is probably to add an OPTION (Recompile); clause to the end of your query. Although this may not work in earlier versions of SQL Server, in the later versions it should cause it to get a better cardinality estimate, and thus produce the same query plan that it does for the temp table version.

Let me know if this does not work, as there are some other (less desirable) possible solutions.

Common Table Expressions slow when using a table variable

2 Answers