• [アーティクル]

MERGE ステートメントのパフォーマンスの最適化

SQL Server 2008 では、MERGE ステートメントを使用して、単一のステートメントで複数のデータ操作言語 (DML) 操作を実行できます。たとえば、他のテーブルとの違いに基づいて、あるテーブル内の行を挿入、更新、または削除することにより、2 つのテーブルを同期することが必要になる場合があります。通常、この操作を行うには、INSERT、UPDATE、および DELETE の各ステートメントを含むストアド プロシージャまたはバッチを実行します。ただし、この場合は、ソース テーブルと対象テーブルの両方のデータが複数回 (ステートメントごとに 1 回以上) 評価および処理されます。

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 Join 操作が最も効率的なクエリ プランになります。これは、両方のテーブルが 1 回だけスキャンされ、データを並べ替える必要がないためです。ソース テーブルが対象テーブルよりも小さい場合は、Nested Loops 操作をお勧めします。

MERGE ステートメントで OPTION (<query_hint>) 句を指定することで、特定の結合を強制的に使用することができます。MERGE ステートメントのクエリ ヒントとしてハッシュ結合を使用しないことをお勧めします。この種類の結合ではインデックスが使用されないためです。クエリ ヒントの詳細については、「クエリ ヒント (Transact-SQL)」を参照してください。次の例では、OPTION 句で入れ子になったループ結合を指定します。

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