Gute Tabellenausdrücke langsam, wenn eine Tabellenvariable verwendet wird

Ich habe mit dem folgenden (vereinfachten) CTE experimentiert. Bei der Verwendung einer Tabellenvariable () läuft die Abfrage für Minuten, bevor ich sie abbrechen kann. Jeder der anderen kommentierte methods in weniger als einer Sekunde zurück.

Wenn ich die ganze WHERE-Klausel durch eine INNER-JOIN erstelle, ist es auch schnell.

Irgendwelche Ideen, warum eine Tabellenvariable so langsam laufen würde?

FWIW: Die database enthält 2,5 Millionen datasätze und die innere Abfrage gibt 2 datasätze zurück.

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 

Dies ist bei der Verwendung der Tabellenvariablen. Die Abfrage übernahm 17 Minuten zu laufen: Bildbeschreibung hier eingeben

Ausführungspläne im XML-Format

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

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

Was die Abfragepläne klar machen, ist, dass die Tabelle Variable Version unter dem berüchtigten "Table Variable Cardinality Schätzung" Problem leidet. Dies geschieht vor allem, weil Tabellenvariablen im Gegensatz zu temp-Tabellen keine Statistiken unterstützen. Dieser Artikel von Paul White erklärt es in exquisiten Details: http://sqlblog.com/blogs/paul_white/archive/2012/08/15/mal-tables-in-stored-procedures.aspx .

Was das auch deutlich macht, ist, dass die einfachste Lösung wahrscheinlich eine OPTION (Recompile); hinzufügen soll OPTION (Recompile); Klausel bis zum Ende Ihrer Anfrage. Obwohl dies in früheren Versionen von SQL server nicht funktionieren kann, sollte es in den späteren Versionen dazu führen, dass es eine bessere Kardinalitätsabschätzung erhält und somit denselben Abfrageplan erzeugt, den es für die temporäre Tabellenversion tut.

Lassen Sie mich wissen, ob dies nicht funktioniert, da es einige andere (weniger wünschenswerte) mögliche Lösungen gibt.

Teillösung: Was sehr geholfen hat, war es, die Indizes auf der Tabelle neu zu erstellen oder neu zu ordnen, wie von SQL server empfohlen, indem sie den Index Physical Statistics Report verwenden. Dieser Bericht ist mit einem Rechtsklick auf die database> Berichte> Standardberichte> Index Physische Statistik verfügbar.