Article
09/27/2010

Optimisation des performances de l'instruction MERGE

Dans SQL Server 2008, vous pouvez effectuer plusieurs opérations en langage de manipulation de données (DML, data manipulation language) dans une instruction unique à l'aide de l'instruction MERGE. Par exemple, vous pouvez avoir besoin de synchroniser deux tables en insérant, en mettant à jour ou en supprimant des lignes dans une seule table en fonction des différences trouvées dans l'autre table. En général, il faut pour cela exécuter une procédure stockée ou un lot qui contient des instructions individuelles INSERT, UPDATE et DELETE. Toutefois, cela signifie que les données contenues à la fois dans les tables sources et cibles sont évaluées et traitées plusieurs fois (au moins une fois pour chaque instruction).

En utilisant l'instruction MERGE, vous pouvez remplacer les instructions individuelles DML par une instruction unique. Vous pouvez ainsi améliorer les performances des requêtes car les opérations sont effectuées dans une seule instruction, cela permettant de réduire le nombre de traitements des données des tables sources et cibles. Toutefois, vous ne pouvez obtenir des gains de performance que si vous disposez d'index et de jointures corrects, d'autres considérations entrant également en jeu. Cette rubrique fournit les meilleures pratiques recommandées qui vous aideront à obtenir des performances optimales lors de l'utilisation de l'instruction MERGE.

Meilleures pratiques pour les index

Pour améliorer les performances de l'instruction MERGE, nous vous recommandons de respecter les règles suivantes relatives aux index :

Créez un index sur les colonnes de jointure de la table source qui sont uniques et qui sont des colonnes de couverture.
Créez un index cluster unique sur les colonnes de jointure dans la table cible.

Ces index garantissent que les clés de jointure sont uniques et que les données des tables sont triées. Les performances des requêtes sont meilleures car l'optimiseur de requête n'a pas besoin d'effectuer de validation supplémentaire pour localiser et mettre à jour les lignes en double, et aucune opération de tri supplémentaire n'est nécessaire.

Par exemple, dans l'instruction MERGE suivante, la table source, dbo.Purchases, et la table cible, dbo.FactBuyingHabits, sont jointes sur les colonnes ProductID et CustomerID. Pour améliorer les performances de cette instruction, vous pouvez créer un index de clé unique ou primaire (cluster ou non cluster) sur les colonnes ProductID et CustomerID de la table dbo.Purchases et un index cluster sur les colonnes ProductID et CustomerID de la table dbo.FactBuyingHabits. Pour afficher le code utilisé pour créer ces tables, consultez Insertion, mise à jour et suppression de données à l’aide de 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.*;

Meilleures pratiques pour les jointures

Pour améliorer les performances de l'instruction MERGE et garantir l'obtention de résultats corrects, nous vous recommandons de respecter les règles suivantes relatives aux jointures :

Spécifiez uniquement dans la clause ON <merge_search_condition> des conditions de recherche qui déterminent les critères de correspondance des données dans les tables sources et cibles. Autrement dit, spécifiez uniquement les colonnes de la table cible qui seront comparées aux colonnes correspondantes de la table source. N'incluez pas de comparaisons avec d'autres valeurs (par exemple, une constante).

Pour filtrer des lignes des tables sources ou cibles, utilisez l'une des méthodes suivantes :

Spécifiez la condition de recherche destinée au filtrage des lignes dans la clause WHEN appropriée. Par exemple, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Définissez une vue sur la source ou la cible qui retourne les lignes filtrées et référencez la vue en tant que table source ou cible. Si la vue est définie sur la table cible, toutes les opérations dont elle fait l'objet doivent satisfaire aux conditions requises pour la mise à jour des vues. Pour plus informations sur la mise à jour de données à l'aide d'une vue, consultez Modification de données par l'intermédiaire d'une vue.
Utilisez la clause WITH <expression de table commune> pour filtrer les lignes des tables sources ou cibles. Cette méthode est semblable à la spécification de critères de recherche supplémentaires dans la clause ON et peut produire des résultats incorrects. Nous vous recommandons d'éviter l'utilisation de cette méthode ou de la tester soigneusement avant de l'implémenter.

Pour plus d'informations, consultez Insertion, mise à jour et suppression de données à l’aide de MERGE.

Optimisation des requêtes de jointures

L'opération de jointure dans l'instruction MERGE peut être optimisée de la même façon qu'une jointure dans une instruction SELECT. Lorsque SQL Server procède au traitement des jointures, l'optimiseur de requête choisit parmi plusieurs possibilités la méthode de traitement la plus efficace. Pour plus d'informations sur les jointures, consultez Principes de base des jointures et Concepts avancés de paramétrage des requêtes. Lorsque la source et la cible sont de taille semblable et que vous avez appliqué aux tables sources et cibles les recommandations relatives aux index décrites précédemment dans la section « Meilleures pratiques pour les index », un opérateur de jointure de fusion constitue le plan de requête le plus efficace. En effet, les deux tables ne sont analysées qu'une fois et il n'est pas nécessaire de trier les données. Lorsque la source est plus petite que la table cible, un opérateur de boucles imbriquées est préférable.

Vous pouvez forcer l'utilisation d'une jointure spécifique en spécifiant la clause OPTION (<query_hint>) dans l'instruction MERGE. Nous vous recommandons de ne pas utiliser de jointure de hachage comme indicateur de requête pour les instructions MERGE car ce type de jointure n'utilise pas d'index. Pour plus d'informations sur les indicateurs de requête, consultez Indicateurs de requête (Transact-SQL). L'exemple suivant spécifie une jointure de boucle imbriquée dans la clause 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;

Meilleures pratiques pour le paramétrage

Si une instruction SELECT, INSERT, UPDATE ou DELETE est exécutée sans paramètres, l'optimiseur de requête SQL Server peut choisir de paramétrer l'instruction en interne. Toute valeur littérale contenue dans la requête est alors remplacée par des paramètres. Par exemple, l'instruction INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) peut être implémentée en interne sous la forme INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Ce processus, appelé paramétrage simple, augmente la capacité du moteur relationnel à faire correspondre les nouvelles instructions SQL aux plans d'exécution existants compilés précédemment. On peut ainsi améliorer les performances des requêtes grâce à la diminution de la fréquence des compilations et recompilations des requêtes. L'optimiseur de requête n'applique pas le processus de paramétrage simple aux instructions MERGE. Par conséquent, les instructions MERGE qui contiennent des valeurs littérales peuvent ne pas s'exécuter de façon aussi fluide que les instructions individuelles INSERT, UPDATE ou DELETE car un nouveau plan est compilé à chaque exécution de l'instruction MERGE.

Pour améliorer les performances des requêtes, nous vous recommandons de respecter les règles suivantes relatives au paramétrage :

Paramétrez toutes les valeurs littérales de la clause ON <merge_search_condition> et des clauses WHEN de l'instruction MERGE. Par exemple, vous pouvez incorporer l'instruction MERGE dans une procédure stockée pour remplacer les valeurs littérales par les paramètres d'entrée appropriés.
Si vous ne pouvez pas paramétrer l'instruction, créez un repère de plan de type TEMPLATE et spécifiez l'indicateur de requête PARAMETERIZATION FORCED dans le repère de plan. Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.
Si les instructions MERGE sont fréquemment exécutées sur la base de données, vous devez envisager de définir l'option PARAMETERIZATION sur FORCED. Configurez cette option avec précaution. L'option PARAMETERIZATION est un paramètre de niveau base de données qui affecte le traitement de toutes les requêtes effectuées sur la base de données. Pour plus d'informations, consultez Paramétrage forcé.

Meilleures pratiques pour la clause TOP

Dans l'instruction MERGE, la clause TOP définit le nombre ou le pourcentage de lignes affectées après la jointure de la table source et de la table cible, et après la suppression des lignes qui ne sont pas éligibles pour une opération de type INSERT, UPDATE ou DELETE. La clause TOP réduit le nombre de lignes jointes à la valeur spécifiée et les actions INSERT, UPDATE ou DELETE sont appliquées aux lignes jointes restantes sans respecter un ordre particulier. Les lignes ne sont donc pas réparties selon un ordre particulier dans le cadre des actions définies dans les clauses WHEN. Par exemple, la spécification de la clause TOP (10) affecte 10 lignes, dont 7 peuvent être mises à jour et 3 insérées, ou alors 1 ligne peut être supprimée, 5 mises à jour et 4 insérées et ainsi de suite.

On utilise couramment la clause TOP pour effectuer des opérations en langage DML sur une grande table dans les lots. Lorsque l'on utilise dans ce but la clause TOP dans l'instruction MERGE, il est important de tenir compte des conséquences suivantes.

Les performances d'E/S peuvent être affectées.

L'instruction MERGE effectue une analyse complète des tables sources et cibles. La division de l'opération en lots permet de réduire le nombre d'opérations d'écriture effectuées par lot. Toutefois, chaque lot effectuera une analyse complète des tables sources et cibles. L'activité de lecture résultante peut affecter les performances de la requête.
Des résultats incorrects peuvent se produire.

Il est important de s'assurer que tous les lots consécutifs traitent les nouvelles lignes. Dans le cas contraire, des comportements indésirables tels que l'insertion de lignes en double dans la table cible peuvent se produire. Cela peut se produire lorsque la table source inclut une ligne que ne figurait pas dans un lot cible mais figurait dans la table cible globale.

Pour garantir l'obtention de résultats corrects :
- Utilisez la clause ON pour déterminer quelles lignes sources affectent les lignes cibles existantes et quelles lignes sont vraiment nouvelles.
- Utilisez une condition supplémentaire dans la clause WHEN MATCHED pour déterminer si la ligne cible a déjà été mise à jour par un lot précédent.
Étant donné que la clause TOP n'est appliquée qu'une fois ces clauses appliquées, chaque exécution insère une ligne réellement sans correspondance ou met à jour une ligne existante. L'exemple suivant crée une table source et une table cible, puis illustre la méthode correcte d'utilisation de la clause TOP pour modifier la cible dans les traitements par lots.
```
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
```
L'exemple suivant présente une méthode incorrecte d'implémentation de la clause TOP. Le contrôle de la colonne is_current est spécifié dans la condition de jointure avec la table source. Cela signifie qu'une ligne source utilisée dans un lot sera considérée comme sans correspondance dans le lot suivant, provoquant ainsi une opération d'insertion non désirée.
```
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
```
L'exemple suivant illustre également une méthode incorrecte. En utilisant une expression de table commune (CTE, common table expression) pour restreindre le nombre de lectures de lignes pour le lot, toute ligne source qui aurait correspondu à une ligne cible autre que celle sélectionnée par la clause TOP (1) est considérée comme sans correspondance, cela provoquant une opération d'insertion non désirée. De plus, cette méthode limite uniquement le nombre de lignes pouvant être mises à jour car chaque lot tente d'insérer toutes les lignes sources sans correspondance.
```
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
```

Meilleures pratiques pour le chargement en masse

L'instruction MERGE peut être utilisée pour effectuer un chargement en masse efficace des données à partir d'un fichier de données sources vers une table cible en spécifiant la clause OPENROWSET(BULK…) comme source de table. La totalité du fichier est ainsi traitée dans un lot unique.

Pour améliorer les performances du processus de fusion en masse, nous vous recommandons de respecter les règles suivantes :

Créez un index cluster sur les colonnes de jointure de la table cible.
Utilisez les indicateurs ORDER et UNIQUE dans la clause OPENROWSET(BULK…) pour spécifier la façon dont le fichier de données sources a été trié.

Par défaut, le processus de chargement en masse considère que le fichier de données n'est pas trié. Par conséquent, il est important que les données sources soient triées d'après l'index cluster sur la table cible et que l'indicateur ORDER soit utilisé pour indiquer l'ordre de tri afin que l'optimiseur de requête puisse générer un plan de requête plus efficace. Les indicateurs sont validés pendant l'exécution. Si le flux de données ne se conforme pas aux indicateurs spécifiés, une erreur est générée.

Le respect de ces règles permet d'être sûr que les clés de jointure sont uniques et que l'ordre de tri des données dans le fichier source correspond à celui de la table cible. Les performances des requêtes sont améliorées car aucune opération de tri supplémentaire n'est nécessaire et aucune copie des données n'est requise. Dans l'exemple suivant, l'instruction MERGE est utilisée pour effectuer un chargement en masse de données de StockData.txt, un fichier plat, vers la table cible dbo.Stock. En définissant une contrainte de clé primaire sur StockName dans la table cible, un index cluster est créé sur la colonne utilisée pour assurer la jointure avec les données sources. Les indicateurs ORDER et UNIQUE sont appliqués à la colonne Stock dans la source de données, qui est mappée à la colonne clé de l'index cluster dans la table cible.

Avant d'exécuter cet exemple, créez un fichier texte nommé 'StockData.txt' dans le dossier C:\SQLFiles\. Ce fichier doit comporter deux colonnes de données séparées par une virgule. Utilisez, par exemple, les données suivantes.

Alpine mountain bike,100

Brake set,22

Cushion,5

Créez ensuite un fichier de format xml nommé 'BulkloadFormatFile.xml' dans le dossier C:\SQLFiles\. Utilisez les informations suivantes.

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

Mesure et diagnostic des performances de l'instruction MERGE

Les fonctionnalités suivantes sont à votre disposition pour vous aider à mesurer et diagnostiquer les performances des instructions MERGE.

Utilisez le compteur merge stmt dans la fonction de gestion dynamique sys.dm_exec_query_optimizer_info pour retourner le nombre d'optimisations de requêtes concernant les instructions MERGE.
Utilisez l'attribut merge_action_type dans la fonction de gestion dynamique sys.dm_exec_plan_attributes pour retourner le type de plan d'exécution de déclencheur utilisé comme résultat d'une instruction MERGE.
Utilisez SQL Trace pour rassembler des données de résolution des problèmes pour l'instruction MERGE comme vous le feriez pour d'autres instructions en langage DML. Pour plus d'informations, consultez Présentation de SQL Trace.