使用 JOIN 有效地更新表

November 20, 2019

我有一個包含家庭詳細資訊的表格，另一個包含與家庭相關的所有人員的詳細資訊。對於家庭表，我使用其中的兩列定義了一個主鍵 - [tempId,n]。對於 person 表，我有一個使用其 3 列定義的主鍵[tempId,n,sporder]
使用由主鍵上的聚集索引指定的排序，我為每個家庭[HHID]和每個人[PERID]的記錄生成了一個唯一的 ID（下面的程式碼片段用於生成 PERID]：
ALTER TABLE dbo.persons
ADD PERID INT IDENTITY
CONSTRAINT [UQ dbo.persons HHID] UNIQUE;
現在，我的下一步是將每個人與相應的家庭相關聯，即；將 a 映射[PERID]到 a [HHID]。兩個表之間的人行橫道基於兩列[tempId,n]。為此，我有以下內部聯接語句。
UPDATE t1
 SET t1.HHID = t2.HHID
 FROM dbo.persons AS t1
 INNER JOIN dbo.households AS t2
 ON t1.tempId = t2.tempId AND t1.n = t2.n;
我總共有 1928783 戶記錄和 5239842 個人記錄。目前執行時間非常長。
現在，我的問題：
是否可以進一步優化此查詢？更一般地說，優化連接查詢的經驗法則是什麼？
是否有另一種查詢結構可以以更好的執行時間實現我想要的結果？
我已將SQL Server 2008 為整個腳本生成的執行計劃上傳到 SQLPerformance.com

我很確定表定義接近這個：
CREATE TABLE dbo.households
(
   tempId  integer NOT NULL,
   n       integer NOT NULL,
   HHID    integer IDENTITY NOT NULL,

   CONSTRAINT [UQ dbo.households HHID] 
       UNIQUE NONCLUSTERED (HHID),

   CONSTRAINT [PK dbo.households tempId, n]
   PRIMARY KEY CLUSTERED (tempId, n)
);

CREATE TABLE dbo.persons
(
   tempId  integer NOT NULL,
   sporder integer NOT NULL,
   n       integer NOT NULL,
   PERID   integer IDENTITY NOT NULL,
   HHID    integer NOT NULL,

   CONSTRAINT [UQ dbo.persons HHID]
       UNIQUE NONCLUSTERED (PERID),

   CONSTRAINT [PK dbo.persons tempId, n, sporder]
       PRIMARY KEY CLUSTERED (tempId, n, sporder)
);
我沒有這些表或您的數據的統計資訊，但以下內容至少可以正確設置表基數（頁數是猜測）：
UPDATE STATISTICS dbo.persons 
WITH 
   ROWCOUNT = 5239842, 
   PAGECOUNT = 100000;

UPDATE STATISTICS dbo.households 
WITH 
   ROWCOUNT = 1928783, 
   PAGECOUNT = 25000;
查詢計劃分析
您現在的查詢是：
UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
   ON P.tempId = H.tempId
   AND P.n = H.n;
這會產生相當低效的計劃：
這個計劃的主要問題是雜湊連接和排序。兩者都需要記憶體授權（雜湊連接需要建構一個雜湊表，並且排序需要空間來在排序過程中儲存行）。計劃資源管理器顯示此查詢被授予 765 MB：
這是相當多的伺服器記憶體專用於一個查詢！更重要的是，在執行開始之前，根據行數和大小估計，此記憶體授予是固定的。
如果在執行時記憶體不足，則至少有一些用於雜湊和/或排序的數據將被寫入物理tempdb磁碟。這被稱為“溢出”，它可能是一個非常緩慢的操作。您可以使用 Profiler 事件Hash Warnings和Sort Warnings來跟踪這些溢出（在 SQL Server 2008 中）。
對雜湊表的建構輸入的估計非常好：
排序輸入的估計不太準確：
您必須使用 Profiler 進行檢查，但我懷疑在這種情況下排序會溢出到tempdb。雜湊表也有可能溢出，但這不太明確。
請注意，為該查詢保留的記憶體在雜湊表和排序之間是分開的，因為它們是同時執行的。Memory Fractions 計劃屬性顯示每個操作預期使用的記憶體授予的相對數量。
為什麼排序和散列？
排序由查詢優化器引入，以確保行以聚集鍵順序到達聚集索引更新運算符。這促進了對錶的順序訪問，這通常比隨機訪問更有效。
散列連接是一個不太明顯的選擇，因為它的輸入大小相似（無論如何都是第一個近似值）。在一個輸入（建構雜湊表的那個）相對較小的情況下，雜湊連接是最好的。
在這種情況下，優化器的成本計算模型確定雜湊連接是三個選項（雜湊、合併、嵌套循環）中成本較低的一個。
提高性能
成本模型並不總是正確的。它往往會高估並行合併連接的成本，尤其是隨著執行緒數量的增加。我們可以使用查詢提示強制合併連接：
UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
   ON P.tempId = H.tempId
   AND P.n = H.n
OPTION (MERGE JOIN);
這會產生一個不需要太多記憶體的計劃（因為合併連接不需要雜湊表）：
有問題的排序仍然存在，因為合併連接僅保留其連接鍵的順序（tempId，n），但集群鍵是（tempId，n，sporder）。您可能會發現合併連接計劃的性能並不比散列連接計劃好。
嵌套循環加入
我們也可以嘗試嵌套循環連接：
UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
   ON P.tempId = H.tempId
   AND P.n = H.n
OPTION (LOOP JOIN);
此查詢的計劃是：
這個查詢計劃被優化器的成本計算模型認為是最差的，但它確實有一些非常理想的特性。首先，嵌套循環連接不需要記憶體授權。其次，它可以保留Persons表中的鍵順序，因此不需要顯式排序。您可能會發現此計劃執行得相對較好，甚至可能已經足夠好。
並行嵌套循環
嵌套循環計劃的最大缺點是它在單個執行緒上執行。該查詢很可能受益於並行性，但優化器認為這樣做沒有任何優勢。這也不一定正確。不幸的是，沒有內置的查詢提示來獲取並行計劃，但是有一種未記錄的方式：
UPDATE t1
 SET t1.HHID = t2.HHID
 FROM dbo.persons AS t1
 INNER JOIN dbo.households AS t2
 ON t1.tempId = t2.tempId AND t1.n = t2.n
OPTION (LOOP JOIN, QUERYTRACEON 8649);
使用提示啟用跟踪標誌 8649QUERYTRACEON會生成以下計劃：
現在我們有了一個避免排序的計劃，連接不需要額外的記憶體，並有效地使用並行性。您應該會發現此查詢的性能比其他查詢好得多。
我的文章強制並行查詢執行計劃中有關並行性的更多資訊：