SQL Server 2008 優化MERGE語句效能

在 SQL Server 2008 中，通過使用 MERGE 語句，可以在單個語句中執行多個資料操作語言 (DML) 操作。例如，您可能需要根據兩個表之間的差異在其中一個表中插入、更新或刪除行，從而對兩個表進行同步。通常，可以通過執行包含各個 INSERT、UPDATE 和 DELETE 語句的儲存過程或批處理來實現這一目的。然而，這意味著需要多次計算和處理源表和目標表中的資料；至少對每個語句計算和處理一次。

通過使用 MERGE 語句，可以使用單個語句替換各個 DML 語句。由於操作是在單個語句中執行的，因此可以提高查詢效能，從而最大限度地減少處理源表和目標表中資料的次數。然而，效能的提升取決於是否進行了正確的索引和聯接以及是否遵守了其他注意事項。本主題提供的最佳方法建議可幫助您在使用 MERGE 語句時獲得最佳的效能。

 有關索引的最佳做法
若要提高 MERGE 語句的效能，我們建議您遵循以下索引準則：

對源表的聯接列建立唯一的涵蓋索引。

對目標表的聯接列建立唯一的聚集索引。

這些索引確保聯接鍵唯一併且表中的資料經過排序。因為查詢優化器不需要執行額外驗證處理即可定位和更新重複的行，也不需要執行其他排序操作，所以查詢效能得到了提高。

例如，在以下 MERGE 語句中，源表 dbo.Purchases 和目標表 dbo.FactBuyingHabits 在 ProductID 和 CustomerID 列上聯接。若要提高此語句的效能，可以對 dbo.Purchases 表的 ProductID 和 CustomerID 列建立唯一鍵索引或主鍵索引（聚集或非聚集），對 dbo.FactBuyingHabits 表的 ProductID 和 CustomerID 列建立聚集索引。若要檢視用於建立這些表的程式碼，請參閱使用 MERGE 插入、更新和刪除資料。

 
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*;
 與 JOIN 有關的最佳做法
若要提高 MERGE 語句的效能並確保獲得正確的結果，我們建議您遵循以下聯接準則：

在 ON 子句中僅指定可決定源表與目標表之間資料匹配標準的搜尋條件。也就是說，僅指定與源表中的對應列進行比較的目標表列。不要包括與其他值（如常量）的比較。

若要從源表或目標表中篩選出行，請使用以下方法之一。

在適當的 WHEN 子句中指定用於行篩選的搜尋條件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...。

對返回篩選行的源表或目標表定義檢視，並且將該檢視作為源表或目標表進行引用。如果該檢視是針對目標表定義的，則針對該檢視的任何操作都必須滿足更新檢視所需的條件。有關使用檢視更新資料的詳細資訊，請參閱通過檢視修改資料。

使用 WITH 子句從源表或目標表中篩選出行。此方法類似於在 ON 子句中指定附加搜尋條件，並可能產生不正確的結果。建議您避免使用此方法，或者在採用它前進行全面測試。

有關詳細資訊，請參閱使用 MERGE 插入、更新和刪除資料。

聯接的查詢優化
MERGE 語句中聯接操作的優化方式與 SELECT 語句中聯接操作的優化方式相同。也就是說，SQL Server 處理聯接時，查詢優化器會從多種可行的方法中選擇最有效的方法來處理聯接。有關聯接的詳細資訊，請參閱聯接基礎知識和高階查詢優化概念。如果源表和目標表的大小相似，並且對源表和目標表應用了先前在“有關索引的最佳做法”一節中介紹的索引準則，則 merge join 運算子是最有效的查詢計劃。這是由於對兩個表都只掃描一次，並且無需對資料進行排序。如果源表小於目標表，則最好用 nested loops 運算子。

通過在 MERGE 語句中指定 OPTION () 子句，可以強制使用某種特定聯接。建議您不要將雜湊聯接用作 MERGE 語句的查詢提示，因為該聯接型別不使用索引。有關查詢提示的詳細資訊，請參閱查詢提示 (Transact-SQL)。以下示例在 OPTION 子句中指定了巢狀迴圈聯接。

 
USE AdventureWorks;
GO
BEGIN TRAN;
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
       FROM Sales.SalesOrderDetail AS sod
       JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
         AND soh.OrderDate BETWEEN '20030701' AND '20030731'
       GROUP BY ProductID) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*
OPTION (LOOP JOIN);
GO
ROLLBACK TRAN;
 有關引數化的最佳做法
如果執行不帶引數的 SELECT、INSERT、UPDATE 或 DELETE 語句，SQL Server 查詢優化器可能會選擇在內部對語句進行引數化處理。也就是說，使用引數替換查詢中包含的任何文字值。例如，語句 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) 可能在內部替換為 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) 來執行。此過程稱為簡單引數化，它可提高關係引擎將新的 SQL 語句與先前編譯的現有執行計劃進行匹配的能力。由於減少了查詢編譯和重新編譯的頻率，因此可提高查詢效能。查詢優化器不會對 MERGE 語句應用簡單引數化過程。由於在每次執行 MERGE 語句時都需要編譯一個新計劃，因此包含文字值的 MERGE 語句的效能表現可能低於各個 INSERT、UPDATE 或 DELETE 語句。

若要提高查詢效能，我們建議您遵循以下引數化準則：

引數化 MERGE 語句的 ON 子句和 WHEN 子句中的所有文字值。例如，可以將 MERGE 語句合併到儲存過程中，並用適當的輸入引數替換文字值。

如果不能引數化該語句，可建立 TEMPLATE 型別的計劃指南，並在計劃指南中指定 PARAMETERIZATION FORCED 查詢提示。有關詳細資訊，請參閱使用計劃指南指定查詢引數化行為。

如果頻繁對資料庫執行 MERGE 語句，請考慮將資料庫的 PARAMETERIZATION 選項設定為 FORCED。設定此選項時請謹慎從事。PARAMETERIZATION 選項是資料庫級別設定，並且影響針對資料庫的所有查詢的處理方式。有關詳細資訊，請參閱強制引數化。

 與 TOP 子句有關的最佳做法
在 MERGE 語句中，TOP 子句指定在對源表和目標表進行聯接之後（或在刪除不符合執行插入、更新或刪除操作條件的行之後）受影響的行的數量或百分比。TOP 子句將聯接行的數量進一步減少為指定值，並且以一種無序方式對其餘聯接行應用插入、更新或刪除操作。也就是說，在 WHEN 子句中定義的操作中，這些行是無序分佈的。例如，如果指定 TOP (10)，將會影響 10 行；在這些行中，可能會更新 7 行而插入 3 行，或者可能刪除 1 行，更新 5 行並且插入 4 行，依此類推。

使用 TOP 子句對大型表分批執行資料操作語言 (DML) 操作是一種常見的做法。如果出於此目的而在 MERGE 語句中使用 TOP 子句，請務必瞭解以下影響。

I/O 效能可能會受到影響。
MERGE 語句對源表和目標表都進行完全表掃描。使操作分批執行可減少每批執行的寫入操作的數量；但在每個批處理中都將對源表和目標表執行完全表掃描。產生的讀取活動可能會影響查詢的效能。

可能產生不正確的結果。
務必確保所有後續批處理都以新行作為處理目標，否則可能發生意想不到的行為，例如在目標表中錯誤地插入重複的行。如果源表包含的某行未包括在目標批處理中，但卻包含在總目標表中，便會發生此情況。
確保獲得正確的結果：

使用 ON 子句確定哪些源行影響現有目標行以及哪些是全新的。

在 WHEN MATCHED 子句中使用附加條件來確定目標行是否已由先前的批處理進行了更新。

因為只有在應用這些子句之後才會應用 TOP 子句，所以每次執行會插入一個確實不匹配的行，或者更新一個現有行。以下示例將建立源表和目標表，然後展示使用 TOP 子句在批處理操作中修改目標的正確方法。

 
CREATE TABLE dbo.inventory(item_key int NOT NULL PRIMARY KEY, amount int, is_current bit)
GO
CREATE TABLE dbo.net_changes(item_key int NOT NULL PRIMARY KEY, amount int)
GO

MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key
WHEN MATCHED AND inventory.is_current = 0
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) VALUES(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
以下示例說明了實現 TOP 子句的錯誤方法。對 is_current 列的檢查是在與源表的聯接條件中指定的。也就是說，在一個批處理中使用的源行將在下一個批處理中被視為“不匹配”，從而導致不需要的插入操作。

 
MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key AND inventory.is_current = 0
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
以下示例也說明了一種不正確的方法。通過使用通用表表示式 (CTE) 來限制為批處理讀取的行數，任何具有匹配目標行的源行（通過 TOP(1) 選擇的行除外）都會被視為“不匹配”，從而導致不需要的插入操作。此外，此方法僅限制可以更新的行的數量；每個批處理都將嘗試插入所有“不匹配”的源行。

 
WITH target_batch AS (
  SELECT TOP(1) *
  FROM dbo.inventory
  WHERE is_current = 0
  )
MERGE target_batch
USING dbo.net_changes
ON target_batch.item_key = net_changes.item_key
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
 有關大容量載入的最佳做法
通過將 OPENROWSET(BULK…) 子句指定為表源，可使用 MERGE 語句高效地將源資料檔案中的資料大容量載入到目標表中。通過這種方式，可以在單個批處理中處理整個檔案。

若要改進大容量合併過程的效能，我們建議您遵循以下準則：

對目標表中的聯接列建立聚集索引。

在 OPENROWSET (BULK … ) 子句中使用 ORDER 和 UNIQUE 提示以指定如何對源資料檔案排序。
預設情況下，大容量操作假定資料檔案未排序。因此，請務必根據目標表的聚集索引對源資料進行排序並使用 ORDER 提示指示該順序，以便查詢優化器可以生成更有效的查詢計劃。在執行時將對提示進行驗證；如果資料流不符合指定的提示，則會引發錯誤。

上述準則確保聯接鍵唯一併且原始檔中資料的排序順序與目標表相符。因為不需要執行其他排序操作和不必要的資料複製，所以提高了查詢的效能。下面的示例使用 MERGE 語句將平面檔案 StockData.txt 中的資料大容量載入到目標表 dbo.Stock 中。通過對目標表中的 StockName 定義主鍵約束，對與源資料進行聯接的該列建立了一個聚集索引。對資料來源中的 Stock 列應用 ORDER 和 UNIQUE 提示，該列對映到目標表中的聚集索引鍵列。

執行此示例之前，請在資料夾 C:\SQLFiles\ 中建立一個名為“StockData.txt”的文字檔案。此檔案應具有兩列以逗號分隔的資料。例如，使用下面的資料。

Alpine mountain bike,100

Brake set,22

Cushion,5

接下來，在資料夾 C:\SQLFiles\ 中建立名為“BulkloadFormatFile.xml”的 xml 格式的檔案。請使用以下資訊。

http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 
USE AdventureWorks;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
    BULK 'C:\SQLFiles\StockData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000,
    ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
GO
 測量和診斷 MERGE 效能
以下功能可幫助您測量和診斷 MERGE 語句的效能。

在 sys.dm_exec_query_optimizer_info 動態管理函式中使用 merge stmt 計數器可返回針對 MERGE 語句進行的查詢優化次數。

在 sys.dm_exec_plan_attributes 動態管理函式中使用 merge_action_type 屬性可返回用作 MERGE 語句結果的觸發器執行計劃的型別。

使用 SQL 跟蹤收集 MERGE 語句的故障排除資料，其方式與用於其他資料操作語言 (DML) 語句的方式相同。有關詳細資訊，請參閱SQL 跟蹤簡介。

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-515892/，如需轉載，請註明出處，否則將追究法律責任。