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 を使用したデータの挿入、更新、および削除」を参照してください。

結合クエリの最適化

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 <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 句はこれらの句が適用された後にのみ適用されるので、実行ごとに本当に一致しない 1 行が挿入されるか、既存の 1 行が更新されます。次の例では、ソース テーブルと対象テーブルを作成し、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 列のチェックが指定されています。つまり、1 つのバッチで使用されたソース行が次のバッチでは "一致しない" 行として扱われるので、不要な挿入操作が行われることになります。

  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 列に適用され、この列が対象テーブルのクラスタ化インデックス キー列にマップされます。

この例を実行する前に、StockData.txt という名前のテキスト ファイルを C:\SQLFiles\ フォルダに作成します。このファイルには、コンマで区切られた 2 つのデータ列を記録します。たとえば、次のデータを使用します。

Alpine mountain bike,100

Brake set,22

Cushion,5

次に、BulkloadFormatFile.xml という名前の xml 形式のファイルを C:\SQLFiles\ フォルダに作成します。次の情報を使用します。

<?xml version="1.0"?>

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

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="5"/>

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="Stock" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="Delta" xsi:type="SQLSMALLINT"/>

</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 トレースの概要」を参照してください。

関連項目

参照

概念