大批量時觸發性能慢
我有一個插入審計表的更新觸發器。在有人決定更新超過 100 萬條記錄之前，我們沒有問題……（那是我的錯。我認為開發時不會有問題）。現在面對現實，我需要找到解決方案…
我一直在做許多測試和研究，試圖找出如何解決我的觸發器性能不佳的問題……我得出的結論是，要盡量減少執行中“表格插入”的不良性能計劃，我需要小批量插入。
問題是：由於我不確定所有不同的更新都來自哪裡，我試圖弄清楚如何在觸發器中批量插入審計記錄？
例如，100 萬條記錄的主表更新將發生並呼叫觸發器，該觸發器將在某種類型的循環中一次插入 10 萬條記錄。
這可能嗎？如果是這樣，您如何建議？如果沒有，我還能如何改進執行計劃的表插入？
添加測試腳本以重現：
這是實物的簡化版
-- drop trigger PriceHist_trig_U -- drop table MyPriceTable -- drop table price_history Create Table MyPriceTable (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100) CONSTRAINT [PRICE_TAB_P01] PRIMARY KEY CLUSTERED ( SKU ASC, PriceGroup ASC, PriceLevel ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Declare @Id int Set @Id = 1 While @Id <= 1000000 Begin insert into MyPriceTable values (right('000000000000' + CAST(@Id as nvarchar(10)),13),'Grp ' + CAST(@Id%10 as nvarchar(10)), @id%3, RAND()*(25-10)+10, 1, 'there are many other columns') Print @Id Set @Id = @Id + 1 End -- Drop table price_history create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max)) CREATE NONCLUSTERED INDEX price_history_nc1 ON price_history ( HistoryDate ASC, SKU ASC, PriceGroup ASC, PriceLevel ASC ) go Create TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE AS INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate, ChangedColumns) SELECT INS.SKU,INS.PriceGroup,INS.PriceLevel,INS.Price,INS.Qty,INS.ManyOtherColumns, getdate(), CASE WHEN update(Price) and INS.Price<>DEL.Price THEN 'Price-' ELSE '' END + CASE WHEN update(Qty) and INS.Qty<>DEL.Qty THEN 'Qty-' ELSE '' END + CASE WHEN update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns THEN 'other-' ELSE '' END FROM INSERTED INS JOIN DELETED DEL ON DEL.sku=INS.sku AND DEL.PriceGroup=INS.PriceGroup AND DEL.PriceLevel=INS.PriceLevel WHERE (update(Price) and INS.Price<>DEL.Price) OR (update(Qty) and INS.Qty<>DEL.Qty) OR (update(ManyOtherColumns) and INS.ManyOtherColumns<>DEL.ManyOtherColumns) /* tests */ update MyPriceTable set price = price-1當我在禁用觸發器的情況下執行它時，它會在 2 秒內執行。啟用觸發器後，需要 32 秒才能完成。執行計劃在“表格插入”上顯示 98%
我一直在試圖弄清楚如何改進表格插入，但找不到任何具體的東西……
我嘗試過使用聚集索引，但性能更差。
任何幫助，將不勝感激
我把它放在這裡是因為它有點長，但我認為它不應該作為答案。這裡沒有答案，只有觀察和建議。
短版，沒有任何辦法可以使查詢更快並獲得相同的結果。如果要通過批處理來解決問題，則需要更改將數據輸入主表的過程。否則，您必須更改歷史程序。
首先，讓您放慢速度的不是表插入，而是將 INSERTED 和 DELETED 表拉在一起的查詢。
為什麼不？
INSERTED 和 DELETED 表是沒有索引的堆。將它們連接在一起需要兩次表掃描和一次排序。操作越大，成本越高。
在觸發器內進行批處理
這在這裡對您沒有幫助，因為源表是堆。如果不創建某種可以使用的密鑰，您就無法行走它們，並且添加任何東西只會增加（在最壞的情況下）或只會增加複雜性（最好的情況）而不會改進任何東西。
在觸發器外進行批處理
如果您可以重新排列，以便在觸發器外部執行較小的更新語句，則 INSERTED/DELETED 表將更小，從而使操作更快且阻塞更少，儘管總成本將相同。
解決方案？
任何解決此問題的解決方案都需要以某種方式進行更改。您沒有提及您的 SQL 版本，但如果您使用的是 2016 年或更高版本，您可以查看臨時表。https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15
或者，對於這種類型的歷史表，您只想擷取 UPDATES，我會直接插入 DELETED 表內容。沒有與 INSERTED 表的其他比較或連接。您的成本應該與插入大致相同，因此增加最少（我的意思是，I/O 增加一倍，但這是您可以獲得的最低限度）。
然後查看它，您只需獲取所有歷史記錄+實時記錄，您就可以看到發生了什麼變化以及何時發生的變化。它沒有您目前版本所具有的“ChangedColumns”列表，但如果您願意，您可以將類似的內容放在一起。
祝你好運。
不幸的是，沒有足夠的資訊。有用的資訊是：
- 涉及的表的結構，包括索引
- 執行計劃
- 觸發器的作用和它的樣子（也許它的程式碼可以改進）
- 審計表實際包含的內容
小批量分解該過程可能會有所幫助。請記住，無論您是僅插入一行還是插入 1000 行，每次批處理操作都會觸發一次觸發器。很多人中了這個陷阱，認為每個 insert = 1 觸發呼叫，但事實並非如此。因此，您的觸發器應該知道要添加的行數並採取相應措施。
缺少適當的索引可能是插入速度慢的一個因素。同樣，我們無法知道。您必須查看您的表結構、執行計劃和触發器程式碼。
就其本身而言，插入或更新超過 100 萬條記錄並不是一項小操作。該操作可能包含在隱式或顯式事務中，這會增加更多成本（通過寫入事務日誌文件）。
與其重新發明輪子，不如利用SQL Server 中已有的**審計功能更好。**例如，請參閱：了解 SQL Server 審計。很多時候，觸發器不是解決方案，而是問題。
事實上，這可能不是慢的插入，可能是觸發器進行一些計算或獲取數據來進行插入。
您可以做的一件事是創建目標表的副本，而不使用觸發器並模擬大規模更新以測量時間差。換句話說，驗證觸發器確實是導致性能不佳的原因並對其進行量化。執行執行計劃並進行比較。