在觸發器中加入 INSERTED 和 DELETED 表的性能很差

我在表上有一個 UPDATE 觸發器，它監視特定列從一個特定值更改為任何其他值。發生這種情況時，它會通過單個 UPDATE 語句更新另一個表中的一些相關數據。

觸發器所做的第一件事是檢查是否有任何更新的行使該列的值與所討論的值不同。它只是將 INSERTED 連接到 DELETED 並比較該列中的值。如果沒有任何條件，它會提前退出，因此 UPDATE 語句不會執行。

IF NOT EXISTS (
   SELECT TOP 1 i.CUSTNMBR
   FROM INSERTED i
       INNER JOIN DELETED d
           ON i.CUSTNMBR = d.CUSTNMBR
   WHERE d.CUSTCLAS = 'Misc'
       AND i.CUSTCLAS != 'Misc'
)
   RETURN

在這種情況下，CUSTNMBR 是基礎表的主鍵。如果我對此表進行大量更新（例如，超過 5000 行），即使我沒有觸及 CUSTCLAS 列，此語句也會佔用 AGES。我可以在 Profiler 中看到它在這個語句上停滯幾分鐘。

執行計劃很奇怪。它顯示了具有 3,714 次執行和約 1850 萬輸出行的插入掃描。這通過 CUSTCLAS 列上的過濾器執行。它將此（通過嵌套循環）連接到已刪除掃描（也在 CUSTCLAS 上過濾），該掃描僅執行一次並具有 5000 個輸出行。

我在這裡做了什麼愚蠢的事情來造成這種情況？請注意，觸發器絕對必須正確處理多行更新。

編輯：

我也試過這樣寫（以防 EXISTS 做了一些不愉快的事情），但它仍然很糟糕。

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
   INNER JOIN DELETED d
       ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
   AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
   RETURN

INNER MERGE JOIN您可以使用顯式或提示進行評估INNER HASH JOIN，但假設您稍後在觸發器中再次使用這些表，您最好將表的內容插入索引表inserted並完成它。deleted``#temp

它們不會自動為它們創建有用的索引。

我知道這個問題已經得到解答，但它只是最近才出現，我也遇到了這個問題，對於有數百萬行的表。雖然不打折已接受的答案，但我至少可以補充一點，我的經驗表明，在進行類似測試（查看一列或多列是否實際更改了其值）時，觸發器性能的一個關鍵因素是列是否被測試實際上是UPDATE聲明的一部分。我發現比較實際上不屬於語句的表inserted和表之間的列會對性能造成巨大的拖累，如果這些欄位是deleted``UPDATE``UPDATE聲明（不管它們的值實際上被改變了）。如果您可以從邏輯上排除任何這些列被更改的可能性，那麼為什麼所有這些工作（即比較 X 行中的 N 個欄位的查詢）以確定是否有任何更改，如果它們不存在，這顯然是不可能的在語句的SET子句中UPDATE。

我採用的解決方案是使用僅在觸發器內部工作的UPDATE()函式。此內置函式告訴您是否在UPDATE語句中指定了列，如果您關注的列不屬於UPDATE. 這可以與 a 結合使用SELECT來確定這些列（假設它們存在於 中UPDATE）是否具有實際更改。我在幾個審計觸發器的頂部有程式碼，如下所示：

-- exit on updates that do not update the only 3 columns we ETL
IF (
    EXISTS(SELECT 1 FROM DELETED) -- this is an UPDATE (Trigger is AFTER INSERT, UPDATE)
    AND (
           NOT (UPDATE(Column3) OR UPDATE(Column7)
                OR UPDATE(Column11)) -- the columns we care about are not being updated
           OR NOT EXISTS(
                       SELECT 1
                       FROM INSERTED ins
                       INNER JOIN DELETED del
                               ON del.KeyField1 = ins.KeyField1
                               AND del.KeyField2 = ins.KeyField2
                       WHERE ins.Column3 <> del.Column3
                                COLLATE Latin1_General_100_CS_AS -- case-sensitive compare
                       OR    ISNULL(ins.Column7, -99) <> 
                                ISNULL(del.Column7, -99) -- NULLable INT field
                       OR    ins.[Column11] <> del.[Column11] -- NOT NULL INT field
                     )
         )
   )
BEGIN
   RETURN;
END;

如果出現以下情況，此邏輯將繼續執行觸發器的其餘部分：

1. 該操作是一個INSERT
2. 至少有一個相關欄位位於SETan 的子句中， UPDATE 並且一行中的這些列中至少有一個已更改

可能看起來很奇怪或倒退，NOT (UPDATE...) OR NOT EXISTS()但它旨在避免在沒有相關列屬於. SELECT``inserted``deleted``UPDATE

根據您的需要，COLUMNS_UPDATED()函式是確定哪些列是UPDATE語句的一部分的另一個選項。

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