Artikel
09/24/2010

Optimieren der Leistung bei MERGE-Anweisungen

In SQL Server 2008 können Sie mithilfe der MERGE-Anweisung in einer einzelnen Anweisung mehrere DML (Data Manipulation Language)-Vorgänge ausführen. Möglicherweise müssen Sie z. B. zwei Tabellen synchronisieren, indem Sie Zeilen in einer Tabelle anhand von Unterschieden, die in der anderen Tabelle gefunden wurden, einfügen, aktualisieren oder löschen. Dies erfolgt meist durch Ausführen einer gespeicherten Prozedur oder eines Batchs mit einzelnen INSERT-, UPDATE- und DELETE-Anweisungen. Dies bedeutet jedoch, dass die Daten in der Quell- und Zieltabelle mehrmals ausgewertet und verarbeitet werden, mindestens einmal pro Anweisung.

Mit der MERGE-Anweisung können Sie die einzelnen DML-Anweisungen durch eine einzelne Anweisung ersetzen. Auf diese Weise können Sie die Abfrageleistung verbessern, da die Vorgänge innerhalb einer einzelnen Anweisung ausgeführt und so die Anzahl der Verarbeitungsvorgänge für die Daten in der Quell- und Zieltabelle minimiert werden. Leistungssteigerungen sind jedoch von richtigen Indizes, Verknüpfungen und anderen Fragen abhängig. In diesem Thema finden Sie praktische Empfehlungen, mit denen Sie bei Verwendung der MERGE-Anweisung eine optimale Leistung erzielen können.

Bewährte Methoden zum Indizieren

Zur Leistungsverbesserung der MERGE-Anweisung werden die folgenden Indexrichtlinien empfohlen:

Erstellen Sie einen eindeutigen und umfassenden Index für die Verknüpfungsspalten der Quelltabelle.
Erstellen Sie für die Verknüpfungsspalten in der Zieltabelle einen eindeutigen gruppierten Index.

Mit diesen Indizes wird sichergestellt, dass die Verknüpfungsschlüssel eindeutig und die Daten in den Tabellen sortiert sind. Die Abfrageleistung wird verbessert, da der Abfrageoptimierer keine zusätzliche Validierung ausführen muss, um doppelte Zeilen zu suchen und zu aktualisieren, und zusätzliche Sortiervorgänge nicht erforderlich sind.

In der folgenden MERGE-Anweisung werden beispielsweise die Quelltabelle dbo.Purchases und die Zieltabelle dbo.FactBuyingHabits mit den Spalten ProductID und CustomerID verknüpft. Zur Leistungsverbesserung dieser Anweisung erstellen Sie einen eindeutigen oder primären Schlüsselindex (gruppiert oder nicht gruppiert) für die ProductID-Spalte und die CustomerID-Spalte in der dbo.Purchases-Tabelle sowie einen gruppierten Index für die ProductID-Spalte und die CustomerID-Spalte in der dbo.FactBuyingHabits-Tabelle. Den Code zum Erstellen dieser Tabellen finden Sie unter Einfügen, Aktualisieren und Löschen von Daten mithilfe von 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.*;

Bewährte Methoden für JOIN

Zur Leistungsverbesserung der MERGE-Anweisung und Sicherstellung richtiger Ergebnisse werden die folgenden Verknüpfungsrichtlinien empfohlen:

Geben Sie in der ON <merge_search_condition>-Klausel nur Suchbedingungen an, die die Kriterien für den Vergleich von Daten in der Quell- und der Zieltabelle bestimmen. Geben Sie also nur Spalten aus der Zieltabelle an, die mit den entsprechenden Spalten der Quelltabelle verglichen werden. Fügen Sie keine Vergleiche mit anderen Werten, z. B. einer Konstante, ein.

Zum Filtern von Zeilen aus der Quell- oder Zieltabelle verwenden Sie eine der folgenden Methoden:

Geben Sie die Suchbedingung für die Zeilenfilterung in der entsprechenden WHEN-Klausel an. Beispiel: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Definieren Sie in der Quelle oder im Ziel eine Sicht, die die gefilterten Zeilen zurückgibt, und verweisen Sie auf die Sicht als Quell- oder Zieltabelle. Wenn die Sicht für die Zieltabelle definiert ist, müssen alle Aktionen für die Tabelle die Bedingungen zum Aktualisieren von Sichten erfüllen. Weitere Informationen zum Aktualisieren von Daten mithilfe von Sichten finden Sie unter Ändern von Daten über eine Sicht.
Mit der WITH <common table expression>-Klausel können Sie Zeilen aus der Quell- oder Zieltabelle filtern. Diese Methode ähnelt dem Angeben zusätzlicher Suchkriterien in der ON-Klausel und kann zu falschen Ergebnissen führen. Es wird empfohlen, die Verwendung dieser Methode zu vermeiden oder diese vor dem Implementieren gründlich zu testen.

Weitere Informationen finden Sie unter Einfügen, Aktualisieren und Löschen von Daten mithilfe von MERGE.

Abfrageoptimierung für Verknüpfungen

Der Verknüpfungsvorgang in der MERGE-Anweisung wird auf dieselbe Weise optimiert wie eine Verknüpfung in einer SELECT-Anweisung. Das heißt, beim Verarbeiten von Verknüpfungen durch SQL Server wählt der Abfrageoptimierer aus verschiedenen Möglichkeiten die effizienteste Methode aus. Weitere Informationen zu Verknüpfungen finden Sie unter Grundlegendes zu Verknüpfungen und Grundlagen der erweiterten Abfrageoptimierung. Wenn Quelle und Ziel von ähnlicher Größe sind und die im Abschnitt 'Bewährte Methoden zum Indizieren' beschriebenen Indizierungsrichtlinien auf die Quell- und die Zieltabelle angewendet werden, bildet ein Merge Join-Operator den effizientesten Abfrageplan. Das liegt daran, dass beide Tabellen einmal durchsucht werden und anschließend keine Notwendigkeit vorliegt, die Daten zu sortieren. Wenn die Quell- kleiner als die Zieltabelle ist, ist ein Nested Loops-Operator vorzuziehen.

Sie können die Verwendung einer bestimmten Verknüpfung erzwingen, indem Sie in der MERGE-Anweisung die OPTION (<query_hint>)-Klausel angeben. Es wird empfohlen, als Abfragehinweis für MERGE-Anweisungen nicht die Hashverknüpfung zu verwenden, da von diesem Verknüpfungstyp keine Indizes verwendet werden. Weitere Informationen zu Abfragehinweisen finden Sie unter Abfragehinweise (Transact-SQL). Im folgenden Beispiel wird in der OPTION-Klausel eine Nested Loop-Verknüpfung angegeben.

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;

Bewährte Methoden für die Parametrisierung

Wenn eine der SELECT-, INSERT-, UPDATE- oder DELETE-Anweisungen ohne Parameter ausgeführt wird, kann der SQL Server-Abfrageoptimierer die Anweisung intern parametrisieren. Dies bedeutet, dass alle eventuell in der Abfrage enthaltenen Literalwerte durch Parameter ersetzt werden. Beispielsweise kann die Anweisung INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) intern als INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2) implementiert werden. Dieser Vorgang, als einfache Parametrisierung bezeichnet, erhöht die Wahrscheinlichkeit, dass das relationale Modul neue SQL-Anweisungen vorhandenen, zuvor kompilierten Ausführungsplänen zuordnet. Möglicherweise wird die Abfrageleistung verbessert, da die Häufigkeit der Abfragekompilierungen und Neukompilierungen reduziert wird. Die einfache Parametrisierung wird vom Abfrageoptimierer nicht auf MERGE-Anweisungen angewendet. Daher ist die Leistung bei der Ausführung von MERGE-Anweisungen mit Literalwerten nicht so hoch wie bei einzelnen INSERT-, UPDATE- oder DELETE-Anweisungen, da bei jeder Ausführung der MERGE-Anweisung ein neuer Plan kompiliert wird.

Um die Abfrageleistung zu verbessern, werden die folgenden Parametrisierungsrichtlinien empfohlen:

Parametrisieren Sie alle Literalwerte in der ON <merge_search_condition>-Klausel sowie in den WHEN-Klauseln der MERGE-Anweisung. Beispielsweise können Sie die MERGE-Anweisung in eine gespeicherte Prozedur integrieren und dabei die Literalwerte durch die entsprechenden Eingabeparameter ersetzen.
Wenn Sie die Anweisung nicht parametrisieren können, erstellen Sie eine Planhinweisliste vom Typ TEMPLATE, und geben Sie in der Planhinweisliste den PARAMETERIZATION FORCED-Abfragehinweis an. Weitere Informationen finden Sie unter Angeben des Abfrageparametrisierungsverhaltens mithilfe von Planhinweislisten.
Wenn MERGE-Anweisungen für die Datenbank häufig ausgeführt werden, empfiehlt es sich möglicherweise, die PARAMETERIZATION-Option für die Datenbank auf FORCED festzulegen. Legen Sie diese Option mit Bedacht fest. Die PARAMETERIZATION-Option ist eine Einstellung auf Datenbankebene und wirkt sich darauf aus, wie alle Abfragen für die Datenbank verarbeitet werden. Weitere Informationen finden Sie unter Erzwungene Parametrisierung.

Bewährte Methoden für die TOP-Klausel

In der MERGE-Anweisung gibt die TOP-Klausel die Anzahl oder den Prozentsatz der Zeilen an, auf die sich das Verknüpfen der Quelltabelle mit der Zieltabelle auswirkt, nachdem Zeilen, auf die keine INSERT-, UPDATE- oder DELETE-Aktion angewendet wird, entfernt wurden. Die TOP-Klausel verringert zudem die Anzahl der verknüpften Zeilen auf den angegebenen Wert, und die INSERT-, UPDATE- oder DELETE-Aktionen werden ungeordnet auf die verbliebenen verknüpften Zeilen angewendet. Das heißt, für die Verteilung der Zeilen auf die in den WHEN-Klauseln definierten Aktionen gilt keine bestimmte Reihenfolge. Wenn beispielsweise TOP (10) angegeben wird, sind 10 Zeilen betroffen. Von diesen Zeilen können 7 aktualisiert und 3 eingefügt werden, oder 1 Zeile kann gelöscht, 5 können aktualisiert und 4 eingefügt werden usw.

Häufig wird die TOP-Klausel zum batchweisen Ausführen von DML (Data Manipulation Language)-Vorgängen für eine umfangreiche Tabelle verwendet. Wenn Sie die TOP-Klausel zu diesem Zweck in der MERGE-Anweisung verwenden, müssen Sie sich der folgenden Auswirkungen bewusst sein.

Die E/A-Leistung ist möglicherweise betroffen.

Die MERGE-Anweisung führt einen vollständigen Tabellenscan der Quell- und der Zieltabelle aus. Durch Aufteilung des Vorgangs in Batches wird die Anzahl der Schreibvorgänge pro Batch reduziert. Für jeden Batch wird jedoch ein vollständiger Tabellenscan der Quell- und der Zieltabelle ausgeführt. Die resultierende Leseaktivität wirkt sich möglicherweise auf die Leistung der Abfrage aus.
Es können falsche Ergebnisse auftreten.

Es sollte unbedingt sichergestellt werden, dass alle aufeinander folgenden Batches neuen Zeilen zugeordnet sind, andernfalls kann unerwünschtes Verhalten auftreten, z. B. fälschliches Einfügen doppelter Zeilen in der Zieltabelle. Dies kann der Fall sein, wenn die Quelltabelle eine Zeile enthält, die nicht im Zielbatch, aber in der Zieltabelle insgesamt enthalten war.

So stellen Sie die Richtigkeit der Ergebnisse sicher
- Bestimmen Sie mithilfe der ON-Klausel die Quellzeilen, die sich auf vorhandene Zielzeilen auswirken bzw. tatsächlich neu sind.
- Bestimmen Sie mithilfe einer zusätzlichen Bedingung in der WHEN MATCHED-Klausel, ob die Zielzeile bereits in einem früheren Batch aktualisiert wurde.
Da die TOP-Klausel erst nach diesen Klauseln angewendet wird, wird bei jeder Ausführung eine Zeile, die tatsächlich keine Entsprechung besitzt, eingefügt, oder es wird eine vorhandene Zeile aktualisiert. Im folgenden Beispiel werden eine Quell- und eine Zieltabelle erstellt, anschließend wird die richtige Methode zum Ändern des Ziels in Batchvorgängen mit der TOP-Klausel dargestellt.
```
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
```
Im folgenden Beispiel wird eine falsche Methode zum Implementieren der TOP-Klausel veranschaulicht. Die Prüfung der is_current-Spalte wird in der Verknüpfungsbedingung für die Quelltabelle angegeben. Dies bedeutet, dass eine in einem Batch verwendete Quellzeile im nächsten Batch als "ohne Übereinstimmung" behandelt wird, wodurch ein unerwünschter Einfügevorgang ausgelöst wird.
```
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
```
Im folgenden Codebeispiel wird ebenfalls eine falsche Methode veranschaulicht. Bei Verwendung eines allgemeinen Tabellenausdrucks (Common Table Expression, CTE) zum Einschränken der Anzahl der für den Batch gelesenen Zeilen werden sämtliche Quellzeilen, die mit einer anderen Zielzeile übereinstimmen würden als der von TOP(1) ausgewählten, als "ohne Übereinstimmung" behandelt, wodurch ein unerwünschter Einfügevorgang ausgelöst wird. Zudem schränkt diese Methode nur die Anzahl der Zeilen ein, die aktualisiert werden können, jeder Batch versucht weiterhin, alle Quellzeilen "ohne Übereinstimmung" einzufügen.
```
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
```

Bewährte Methoden zum Massenladen

Die MERGE-Anweisung kann zum effizienten Massenladen von Daten aus einer Quelldatendatei in eine Zieltabelle verwendet werden, indem die OPENROWSET(BULK…)-Klausel als Tabellenquelle angegeben wird. Dadurch wird die gesamte Datei als einzelner Batch verarbeitet.

Zur Leistungsverbesserung des Massenladevorgangs werden die folgenden Richtlinien empfohlen:

Erstellen Sie für die Verknüpfungsspalten in der Zieltabelle einen gruppierten Index.
Geben Sie mithilfe des ORDER-Hinweises und des UNIQUE-Hinweises in der OPENROWSET(BULK…)-Klausel an, wie die Quelldatendatei sortiert ist.

Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist. Daher ist es wichtig, dass die Quelldaten anhand des gruppierten Indexes für die Zieltabelle sortiert sind und die Reihenfolge mit dem ORDER-Hinweis angegeben wird, sodass der Abfrageoptimierer einen effizienteren Abfrageplan generieren kann. Hinweise werden zur Laufzeit validiert. Wenn der Datenstream mit den angegebenen Hinweisen nicht übereinstimmt, wird ein Fehler ausgelöst.

Mit diesen Richtlinien wird sichergestellt, dass die Verknüpfungsschlüssel eindeutig sind und die Sortierreihenfolge der Daten in der Quelldatei mit der Zieltabelle übereinstimmt. Die Abfrageleistung wird verbessert, da zusätzliche Sortiervorgänge nicht erforderlich sind und keine nicht benötigten Datenkopien angefordert werden. Im folgenden Beispiel wird mit der MERGE-Anweisung ein Massenladevorgang aus der Flatfile StockData.txt in die Zieltabelle dbo.Stock ausgeführt. Durch Definition einer Primärschlüsseleinschränkung für StockName in der Zieltabelle wird ein gruppierter Index für die Spalte erstellt, die für die Verknüpfung mit den Quelldaten verwendet wird. Der ORDER-Hinweis und der UNIQUE-Hinweis werden auf die Stock-Spalte in der Datenquelle angewendet, die der Schlüsselspalte mit dem gruppierten Index in der Zieltabelle zugeordnet wird.

Erstellen Sie vor dem Ausführen dieses Beispiels die Textdatei 'StockData.txt' im Ordner C:\SQLFiles\. Die Datei sollte zwei durch ein Komma getrennte Datenspalten enthalten. Verwenden Sie z. B. die folgenden Daten.

Mountain-Bike für Gebirge,100

Bremsensatz,22

Polster,5

Erstellen Sie danach eine XML-Formatdatei mit dem Namen 'BulkloadFormatFile.xml' im Ordner C:\SQLFiles\. Verwenden Sie die folgenden Informationen:

<?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

Messen und Diagnostizieren der MERGE-Leistung

Die folgenden Features stehen Ihnen zur Verfügung, um die Leistung von MERGE-Anweisungen zu messen und zu diagnostizieren.

Geben Sie mithilfe des merge stmt-Indikators in der dynamischen Verwaltungssicht sys.dm_exec_query_optimizer_info die Anzahl der Abfrageoptimierungen für MERGE-Anweisungen zurück.
Geben Sie mithilfe des merge_action_type-Attributs in der dynamischen Verwaltungsfunktion sys.dm_exec_plan_attributes den Typ des Triggerausführungsplans zurück, der als Ergebnis einer MERGE-Anweisung verwendet wird.
Erfassen Sie mit der SQL-Ablaufverfolgung Problembehandlungsdaten für die MERGE-Anweisung auf dieselbe Weise wie für andere DML (Data Manipulation Language)-Anweisungen. Weitere Informationen finden Sie unter Einführung in die SQL-Ablaufverfolgung.