沒有數據更改的更新性能
如果我有一個
UPDATE實際上沒有更改任何數據的語句（因為數據已經處於更新狀態）。在WHERE子句中進行檢查以防止更新是否有任何性能優勢？例如，下面的 UPDATE 1 和 UPDATE 2 之間的執行速度會不會有任何差異：
CREATE TABLE MyTable (ID int PRIMARY KEY, Value int); INSERT INTO MyTable (ID, Value) VALUES (1, 1), (2, 2), (3, 3); -- UPDATE 1 UPDATE MyTable SET Value = 2 WHERE ID = 2 AND Value <> 2; SELECT @@ROWCOUNT; -- UPDATE 2 UPDATE MyTable SET Value = 2 WHERE ID = 2; SELECT @@ROWCOUNT; DROP TABLE MyTable;我問的原因是我需要行數來包含未更改的行，所以我知道如果 ID 不存在是否進行插入。因此，我使用了 UPDATE 2 表格。如果使用 UPDATE 1 表單有性能優勢，是否有可能以某種方式獲得我需要的行數？
如果我有一個實際上沒有更改任何數據的 UPDATE 語句（因為數據已經處於更新狀態），那麼在 where 子句中進行檢查以防止更新是否有任何性能優勢？
由於UPDATE 1的原因，肯定會存在輕微的性能差異：
- 實際上沒有更新任何行（因此沒有寫入磁碟，甚至沒有最小的日誌活動），並且
- 取出比進行實際更新所需的限制更少的鎖（因此對並發性更好）（請參閱最後的更新部分）
但是，您需要在系統上使用您的架構、數據和系統負載來衡量有多少差異。有幾個因素會影響非更新 UPDATE 的影響：
- 正在更新的表上的爭用量
- 正在更新的行數
- 如果正在更新的表上有 UPDATE 觸發器（正如 Mark 在對問題的評論中所指出的那樣）。如果您執行
UPDATE TableName SET Field1 = Field1，則更新觸發器將觸發並指示該欄位已更新（如果您使用UPDATE()或COLUMNS_UPDATED函式進行檢查），並且兩個表中的欄位INSERTED是DELETED相同的值。此外，在 Paul White 的文章The Impact of Non-Updating Updates中可以找到以下摘要部分（@spaghettidba 在對他的回答的評論中指出）：
SQL Server 包含許多優化，以避免在處理不會導致對持久數據庫進行任何更改的 UPDATE 操作時進行不必要的日誌記錄或頁面刷新。
- 對聚集表的非更新更新通常會避免額外的日誌記錄和頁面刷新，除非形成集群鍵（部分）的列受到更新操作的影響。
- 如果集群鍵的任何部分被“更新”為相同的值，則記錄該操作，就好像數據已更改一樣，並且受影響的頁面在緩衝池中被標記為臟頁。這是將 UPDATE 轉換為 delete-then-insert 操作的結果。
- 堆表的行為與集群表相同，只是它們沒有集群鍵來導致任何額外的日誌記錄或頁面刷新。即使堆上存在非聚集主鍵，情況仍然如此。因此，對堆的非更新更新通常會避免額外的日誌記錄和刷新（但請參見下文）。
- 對於使用“SET column_name = column_name”以外的任何語法將包含超過 8000 字節數據的 LOB 列更新為相同值的任何行，堆和聚群表都將遭受額外的日誌記錄和刷新。
- 簡單地在數據庫上啟用任一類型的行版本控制隔離級別總是會導致額外的日誌記錄和刷新。無論對更新事務有效的隔離級別如何，都會發生這種情況。
請記住（尤其是如果您不通過連結查看 Paul 的全文），以下兩項：
- 非更新更新仍然有一些日誌活動，表明事務正在開始和結束。只是沒有發生數據修改（這仍然是一個很好的節省）。
- 如上所述，您需要在系統上進行測試。使用 Paul 正在使用的相同研究查詢，看看您是否得到相同的結果。我在我的系統上看到的結果與文章中顯示的結果略有不同。仍然沒有要寫入的髒頁，但有更多的日誌活動。
…我需要行數來包含未更改的行，因此我知道如果 ID 不存在是否進行插入。…是否有可能以某種方式獲得我需要的行數？
簡單地說，如果您只處理單行，您可以執行以下操作：
UPDATE MyTable SET Value = 2 WHERE ID = 2 AND Value <> 2; IF (@@ROWCOUNT = 0) BEGIN IF (NOT EXISTS( SELECT * FROM MyTable WHERE ID = 2 -- or Value = 2 depending on the scenario ) ) BEGIN INSERT INTO MyTable (ID, Value) -- or leave out ID if it is an IDENTITY VALUES (2, 2); END; END;
OUTPUT對於多行，您可以使用該子句獲取做出該決定所需的資訊。通過準確擷取更新的行，您可以縮小要查找的項目範圍，以了解不更新不存在的行與不更新存在但不需要更新的行之間的區別。我在以下答案中展示了基本實現：
該答案中顯示的方法不會過濾掉存在但不需要更新的行。可以添加該部分，但您首先需要準確顯示要合併到的數據集的確切位置
MyTable。他們來自臨時表嗎？表值參數 (TVP)？更新 1：
我終於能夠進行一些測試，這是我發現的關於事務日誌和鎖定的內容。首先，表的架構：
CREATE TABLE [dbo].[Test] ( [ID] [int] NOT NULL CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED, [StringField] [varchar](500) NULL );接下來，測試將欄位更新為它已有的值：
UPDATE rt SET rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A' FROM dbo.Test rt WHERE rt.ID = 4082117結果：
-- Transaction Log (2 entries): Operation ---------------------------- LOP_BEGIN_XACT LOP_COMMIT_XACT -- SQL Profiler (3 Lock:Acquired events): Mode Type -------------------------------------- 8 - IX 5 - OBJECT 8 - IX 6 - PAGE 5 - X 7 - KEY最後，由於值不變而過濾掉更新的測試：
UPDATE rt SET rt.StringField = '04CF508B-B78E-4264-B9EE-E87DC4AD237A' FROM dbo.Test rt WHERE rt.ID = 4082117 AND rt.StringField <> '04CF508B-B78E-4264-B9EE-E87DC4AD237A';結果：
-- Transaction Log (0 entries): Operation ---------------------------- -- SQL Profiler (3 Lock:Acquired events): Mode Type -------------------------------------- 8 - IX 5 - OBJECT 7 - IU 6 - PAGE 4 - U 7 - KEY如您所見，在過濾掉該行時，沒有任何內容寫入事務日誌，與標記事務開始和結束的兩個條目相反。雖然這兩個條目確實幾乎什麼都不是，但它們仍然是一些東西。
此外，在過濾掉未更改的行時，對 PAGE 和 KEY 資源的鎖定限制較少。如果沒有其他程序與該表進行互動，那麼這可能不是問題（但這種可能性有多大，真的嗎？）。請記住，任何連結部落格（甚至我的測試）中顯示的測試都隱含地假設桌面上沒有爭用，因為它從來不是測試的一部分。說非更新更新是如此輕量級，以至於不需要進行過濾，因為測試已經或多或少地在真空中完成，所以需要用一粒鹽來進行過濾。但是在生產環境中，這個表很可能不是孤立的。當然，很可能是一點點日誌記錄和更多限制性的鎖不會降低效率。那麼回答這個問題最可靠的資訊來源是什麼？SQL 伺服器。具體來說：您的SQL 伺服器。它將向您展示哪種方法更適合您的系統:-)。
更新 2：
如果新值與目前值相同（即不更新）的操作數量超過了新值不同且需要更新的操作，那麼以下模式可能會更好，尤其是如果桌上有很多爭論。這個想法是先做一個簡單
SELECT的得到目前值。如果你沒有得到一個值，那麼你有關於INSERT. 如果你確實有一個值，你可以做一個簡單的，只有在需要時才IF發出。UPDATEDECLARE @CurrentValue VARCHAR(500) = NULL, @NewValue VARCHAR(500) = '04CF508B-B78E-4264-B9EE-E87DC4AD237A', @ID INT = 4082117; SELECT @CurrentValue = rt.StringField FROM dbo.Test rt WHERE rt.ID = @ID; IF (@CurrentValue IS NULL) -- if NULL is valid, use @@ROWCOUNT = 0 BEGIN -- row does not exist INSERT INTO dbo.Test (ID, StringField) VALUES (@ID, @NewValue); END; ELSE BEGIN -- row exists, so check value to see if it is different IF (@CurrentValue <> @NewValue) BEGIN -- value is different, so do the update UPDATE rt SET rt.StringField = @NewValue FROM dbo.Test rt WHERE rt.ID = @ID; END; END;結果：
-- Transaction Log (0 entries): Operation ---------------------------- -- SQL Profiler (2 Lock:Acquired events): Mode Type -------------------------------------- 6 - IS 5 - OBJECT 6 - IS 6 - PAGE所以只獲得了 2 個鎖而不是 3 個，並且這兩個鎖都是 Intent Shared，而不是 Intent eXclusive 或 Intent Update ( Lock Compatibility )。請記住，每個獲取的鎖也會被釋放，每個鎖實際上是 2 次操作，所以這個新方法總共是 4 次操作，而不是最初提出的方法中的 6 次操作。考慮到此操作每 15 毫秒執行一次（大約，如 OP 所述），即每秒約 66 次。因此，最初的提議相當於每秒 396 次加鎖/解鎖操作，而這種新方法即使是更輕量級的鎖，也只有每秒 264 次加鎖/解鎖操作。這並不能保證出色的性能，但肯定值得測試:-)。