使用表變數而不是臨時表會使查詢執行速度變慢

我有一個表，其中包含有關AutoData具有組合聚集鍵Cas(DateTime) + GCom(Car ID) 的汽車的歷史數據。一條記錄包含各種指標，如燃油油位、車輛狀態等。

表中一輛車的各個記錄之間的間隔AutoData是不規則的，有時是 120 秒，有時是幾秒，有時是幾小時等。我需要對記錄進行規範化查看，以便每 30 秒顯示一條記錄。

我有以下腳本：

DECLARE @GCom int = 2563,
   @Od DateTime2(0) = '20170210', 
   @Do DateTime2(0) = '20170224'    

--Create a table with intervals by 30 seconds
declare @temp Table ([cas] datetime2(0))
INSERT @temp([cas])
SELECT d
FROM
(
 SELECT
     d = DATEADD(SECOND, (rn - 1)*30, @Od)
 FROM 
 (
     SELECT TOP (DATEDIFF(MINUTE, @Od, @Do)*2) 
         rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
     FROM
         sys.all_objects AS s1
     CROSS JOIN
         sys.all_objects AS s2
     ORDER BY
         s1.[object_id]
 ) AS x
) AS y;

--Create temp table
CREATE TABLE #AutoData (
   [Cas] [datetime2](0) NOT NULL PRIMARY KEY,
   [IDProvozniRezim] [tinyint] NOT NULL,
   [IDRidic] [smallint] NULL,
   [Stav] [tinyint] NOT NULL,
   [Klicek] [bit] NOT NULL,
   [Alarm] [bit] NOT NULL,
   [MAlarm] [tinyint] NOT NULL,
   [DAlarm] [bit] NOT NULL,
   [Bypass] [bit] NOT NULL,
   [Lat] [real] NULL,
   [Lon] [real] NULL,
   [ObjemAktualni] [real] NOT NULL,
   [RychlostMaxV1] [real] NOT NULL,
   [RychlostV2] [real] NOT NULL,
   [Otacky] [smallint] NOT NULL,
   [Nadspotreba] [real] NOT NULL,
   [Vzdalenost] [real] NOT NULL,
   [Motor] [smallint] NOT NULL
)

--Populate the temp table selecting only relevant AutoData records
INSERT INTO #AutoData
SELECT [Cas]
     ,[IDProvozniRezim]
     ,[IDRidic]
     ,[Stav]
     ,[Klicek]
     ,[Alarm]
     ,[MAlarm]
     ,[DAlarm]
     ,[Bypass]
     ,[Lat]
     ,[Lon]
     ,[ObjemAktualni]
     ,[RychlostMaxV1]
     ,[RychlostV2]
     ,[Otacky]
     ,[Nadspotreba]
     ,[Vzdalenost]
     ,[Motor]
FROM AutoData a 
WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do

--Select final data
SELECT t.cas, ad.malarm, ad.IDProvoznirezim, ad.Otacky, ad.motor, ad.objemAktualni, ad.Nadspotreba 
FROM @temp t
OUTER APPLY (
SELECT TOP 1 stav, malarm, otacky,motor, objemAktualni, Nadspotreba, IDProvoznirezim  FROM #AutoData a
                    WHERE DATEDIFF(SECOND, a.cas, t.cas)<=CASE WHEN Motor>120 THEN Motor ELSE 120 END 
                    AND DATEDIFF(SECOND,  a.cas, t.cas)>-30 
                    ORDER BY CASE WHEN DATEDIFF(SECOND, a.cas, t.cas)>0 THEN DATEDIFF(SECOND, a.cas, t.cas) ELSE (DATEDIFF(SECOND, a.cas, t.cas)*-1) +120 END
) ad

DROP TABLE #AutoData

起初，我嘗試使用只有一個表變數@temp 將條件WHERE a.GCom = @GCom AND a.cas BETWEEN @Od AND @do放在最後一個選擇中來編寫腳本。該腳本需要 39 秒才能執行。

當我使用#AutoData臨時表在臨時表中預載入數據子集時，如上面的腳本所示，它下降到 5 秒。

然後我嘗試使用表變數@AutoData而不是#AutoData- 但它又花了更長的時間 - 22秒。

@temp對於這個例子，表有 40320 條記錄，#AutoData表有 1904 條記錄。但令人驚訝的是，僅使用#temp表而不是@temp變數會使執行再次變慢。

我很驚訝地看到使用或不使用臨時表/變數的這種差異。顯然，SQL Server 本身無法優化 OUTER APPLY 子句的內部。

但是為什麼使用表變數和臨時表會有這麼大的區別呢？有沒有其他方法可以知道，使用什麼而不只是嘗試它？

---

帶有臨時表#AutoData的執行計劃：

https://www.brentozar.com/pastetheplan/?id=B1y2x2Zcg

帶有變數@AutoData 的執行計劃：

https://www.brentozar.com/pastetheplan/?id=r1rAZnbqx

關鍵在於您問題的這一部分：

@temp 表有 40320 條記錄

在執行計劃中，將滑鼠懸停在@temp 表的掃描上。比較估計的行數和實際的行數。（如果您想在http://PasteThePlan.com上發布計劃，我們可以為您提供更具體的細節。免責聲明：這是我公司的網站。）

您將看到估計的行數非常低。

SQL Server 估計 1-3 行將從表變數中返回（取決於您的 SQL Server 版本、基數估計器、跟踪標誌等）。這反過來給您一個非常糟糕的執行計劃，因為 SQL Server 低估了多少工作它需要從其他表中提取多少記憶體，等等。

以下是獲得更準確估計的兩種最流行的方法：

• 嘗試使用臨時表（並查看計劃中的估計行與實際行）
• 在您的查詢上使用 OPTION (RECOMPILE) - 這將使您獲得更精確的估計，但在計劃記憶體可見性和 CPU 使用率方面存在一些非常大的缺點

要看到我現場直播，請觀看 1 小時Watch Brent Tune Queries（免責聲明：就是我，連結到我的影片），我在其中使用表變數進行 Stack Overflow 查詢，並在前面進行現場調整SQL Rally 挪威的觀眾。

引用自：https://dba.stackexchange.com/questions/165626