發行項
09/24/2010

最佳化 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 <merge_search_condition> 子句中只指定可決定用來比對來源和目標資料表資料之準則的搜尋條件。也就是說，只從目標資料表中指定要與來源資料表的對應資料行進行比較的資料行。請勿包含與其他值 (如常數) 的比較。

若要從來源或目標資料表中篩選掉資料列，請使用下列其中一個方法。

在適當的 WHEN 子句中指定資料列篩選的搜尋條件。例如，WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...。
在來源或目標上定義一個可傳回篩選過之資料列的檢視表，並將此檢視表當做來源或目標資料表來參考。如果此檢視表定義在目標資料表上，則對其進行的任何動作都必須滿足更新檢視表的條件。如需有關使用檢視表來更新資料的詳細資訊，請參閱＜透過檢視修改資料＞。
使用 WITH <通用資料表運算式> 子句，從來源或目標資料表中篩選掉資料列。這個方法類似於在 ON 子句中指定其他搜尋條件，而且可能會產生不正確的結果。我們建議您最好避免使用這個方法，或是在實作這個方法之前先徹底加以測試。

如需詳細資訊，請參閱＜使用 MERGE 插入、更新，和刪除資料＞。

聯結的查詢最佳化

MERGE 陳述式中的聯結作業會使用與 SELECT 陳述式中的聯結相同的方式來最佳化。也就是說，當 SQL Server 處理聯結時，查詢最佳化工具將從多種可能性選擇最有效率的處理聯結方式。如需有關聯結的詳細資訊，請參閱＜聯結基礎觀念＞和＜進階查詢微調概念＞。當來源和目標的大小類似，而且先前「索引最佳作法」一節所述的索引指導方針適用於來源和目標資料表時，合併聯結運算子就是最有效率的查詢計畫。這是因為這兩個資料表都會掃描一次，而且不需要排序資料。當來源小於目標資料表時，偏好的是巢狀迴圈運算子。

您可以在 MERGE 陳述式中指定 OPTION (<query_hint>) 子句來強制使用特定聯結。我們建議您不要針對 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 陳述式可能不會與個別 INSERT、UPDATE 或 DELETE 陳述式一樣運作順利，因為每當執行 MERGE 陳述式時，都會編譯新的計畫。

若要改善查詢效能，我們建議您遵循參數化指導方針：

將 MERGE 陳述式的 ON <merge_search_condition> 子句和 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
```

大量載入最佳作法

使用 MERGE 陳述式可有效率地從來源資料檔將資料大量載入目標資料表中，其方式是將 OPENROWSET(BULK…) 子句指定為資料表來源。這樣做的話，整個檔案都會在單一批次中處理。

若要改善大量合併程序的效能，我們建議您遵循下列指導方針：

在目標資料表的聯結資料行上，建立叢集索引。
在 OPENROWSET(BULK…) 子句中使用 ORDER 和 UNIQUE 提示來指定來源資料檔的排序方式。

依預設，大量作業會假設資料檔沒有排序。因此，根據目標資料表上的叢集索引來排序來源資料以及使用 ORDER 提示來指示排序，好讓查詢最佳化工具可以產生更有效率的查詢計畫，都是很重要的事情。提示會在執行階段驗證；如果資料流不符合指定的提示，就會引發錯誤。

這些指導方針可確保聯結索引鍵是唯一的，而且來源檔案中的資料排序次序會符合目標資料表。查詢效能會有所提升，因為不需要其他排序作業，而且也不需要不必要的資料複製。下列範例會使用 MERGE 陳述式，從一般檔案 StockData.txt 將資料大量載入目標資料表 dbo.Stock 中。藉由在目標資料表中的 StockName 上定義主索引鍵條件約束時，就會在此資料行上建立用來聯結來源資料的叢集索引。ORDER 和 UNIQUE 提示會套用到資料來源中的 Stock 資料行，該資料行會對應到目標資料表中的叢集索引鍵資料行。

執行這個範例之前，請先在 C:\SQLFiles\ 資料夾中建立名為 'StockData.txt' 的文字檔。這個檔案具有兩個以逗號分隔的資料行。例如，請使用下列資料。

Alpine mountain bike,100

Brake set,22

Cushion,5

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

<?xml version="1.0"?>

</RECORD>

<ROW>

</ROW>

</BCPFORMAT>

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 追蹤簡介＞。