為什麼臨時表比急切線軸更有效地解決萬聖節問題？

考慮以下查詢，該查詢僅在源表中尚未在目標表中時才從源表中插入行：

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
   SELECT 1
   FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
   WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

一種可能的計劃形狀包括合併連接和急切線軸。急切的線軸操作員在場以解決萬聖節問題：

在我的機器上，上面的程式碼在大約 6900 毫秒內執行。用於創建表格的複制程式碼包含在問題的底部。如果我對性能不滿意，我可能會嘗試將要插入的行載入到臨時表中，而不是依賴於急切的假離線。這是一種可能的實現：

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
   ID BIGINT,
   PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
   SELECT 1
   FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
   WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

新程式碼在大約 4400 毫秒內執行。我可以獲得實際計劃並使用 Actual Time Statistics™ 來檢查在操作員級別上花費的時間。請注意，要求實際計劃會為這些查詢增加大量成本，因此總數將與先前的結果不匹配。

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

與使用臨時表的計劃相比，帶有急切假離線的查詢計劃似乎在插入和假離線運算符上花費了更多的時間。

為什麼使用臨時表的計劃更有效率？無論如何，急切的線軸不是主要只是一個內部臨時表嗎？我相信我正在尋找專注於內部的答案。我能夠看到呼叫堆棧有何不同，但無法弄清楚大局。

如果有人想知道，我在 SQL Server 2017 CU 11 上。這是填充上述查詢中使用的表的程式碼：

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);


DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

這就是我所說的手動萬聖節保護。

您可以在我的文章Optimizing Update Queries中找到它與更新語句一起使用的範例。必須小心保留相同的語義，例如，在執行單獨的查詢時鎖定目標表以防止所有並發修改，如果這與您的場景相關。

為什麼使用臨時表的計劃更有效率？無論如何，急切的線軸不是主要只是一個內部臨時表嗎？

假離線具有臨時表的一些特徵，但兩者並不完全等價。特別是，spool 本質上是對 b-tree 結構的逐行無序插入。它確實受益於鎖定和日誌記錄優化，但不支持批量載入優化。

因此，通常可以通過以一種自然的方式拆分查詢來獲得更好的性能：將新行批量載入到臨時表或變數中，然後從臨時對象執行優化插入（沒有顯式的萬聖節保護）。

進行這種分離還可以讓您更自由地分別調整原始語句的讀取和寫入部分。

作為旁注，考慮如何使用行版本解決萬聖節問題是很有趣的。也許未來版本的 SQL Server 將在適當的情況下提供該功能。

---

正如 Michael Kutz 在評論中提到的那樣，您還可以探索利用空洞填充優化來避免顯式 HP 的可能性。為展示實現此目的的一種方法是ID在A_HEAP_OF_MOSTLY_NEW_ROWS.

CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

有了這個保證，優化器就可以使用空洞填充和行集共享：

MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
   ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

雖然很有趣，但在許多情況下，您仍然可以通過使用精心實施的手動萬聖節保護來獲得更好的性能。

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