sql - T-SQL : Why is my query faster, 如果我使用表变量？

Question

谁能解释一下为什么这个查询需要 13 秒:

SELECT   Table1.Location, Table2.SID, Table2.CID, Table1.VID, COUNT(*)
FROM     Table1 INNER JOIN
         Table2 AS ON Table1.TID = Table2.TID
WHERE    Table1.Last = Table2.Last 
GROUP BY Table1.Location, Table2.SID, Table2.CID, Table1.VID

而这个只有 1 秒:

DECLARE @Test TABLE (Location INT, SID INT, CID INT, VID INT)

INSERT INTO @Test 
SELECT   Table1.Location, Table2.SID, Table2.CID, Table1.VID
FROM     Table1 INNER JOIN
         Table2 AS ON Table1.TID = Table2.TID
WHERE    Table1.Last = Table2.Last 

SELECT   Location, SID, CID, VID, COUNT(*)
FROM     @Test
GROUP BY Location, SID, CID, VID

当我从第一个查询中删除 GROUP BY 时，它也只需要 1 秒。我也尝试编写一个子选择并对结果进行分组，但也需要 13 秒。我不明白这一点。

Answer 1

比较execution plans对于这两个查询。