Articolo
09/28/2010

Ottimizzazione delle prestazioni delle istruzioni MERGE

In SQL Server 2008 è possibile eseguire più operazioni DML (Data Manipulation Language) in un'unica istruzione tramite l'istruzione MERGE. Potrebbe essere necessario, ad esempio, sincronizzare due tabelle inserendo, aggiornando o eliminando righe in una tabella in base alle differenze trovate in un'altra tabella. Questa operazione viene eseguita in genere tramite una stored procedure o un batch che contiene istruzioni INSERT, UPDATE e DELETE singole. Questo comporta tuttavia che i dati contenuti in entrambe le tabelle di origine e di destinazione vengono valutati ed elaborati più volte, almeno una volta per ciascuna istruzione.

L'istruzione MERGE consente di sostituire le istruzioni DML singole con un'unica istruzione. In questo modo è possibile migliorare le prestazioni delle query poiché le operazioni vengono eseguite in un'unica istruzione, riducendo di conseguenza al minimo il numero di elaborazioni dei dati delle tabelle di origine e di destinazione. I vantaggi in termini di prestazioni dipendono tuttavia dalla disponibilità di join ed indici corretti e da altre considerazioni appropriate. In questo argomento vengono fornite indicazioni sulle procedure consigliate che consentono di ottenere prestazioni ottimali quando si utilizza l'istruzione MERGE.

Procedure consigliate per gli indici

Per migliorare le prestazioni delle istruzioni MERGE, si consiglia di attenersi alle linee guida relative agli indici seguenti:

Creare un indice univoco e di copertura sulle colonne di join della tabella di origine.
Creare un indice cluster univoco nelle colonne di join della tabella di destinazione.

Tali indici garantiscono l'univocità delle chiavi di join e l'ordinamento dei dati contenuti nelle tabelle. Le prestazioni delle query risultano migliorate poiché Query Optimizer non deve eseguire alcuna ulteriore elaborazione della convalida per individuare e aggiornare righe duplicate né è necessario eseguire operazioni di ordinamento aggiuntive.

Nell'istruzione MERGE seguente, ad esempio, la tabella di origine dbo.Purchases e quella di destinazione dbo.FactBuyingHabits vengono unite in join in base alle colonne ProductID e CustomerID. Per migliorare le prestazioni di questa istruzione, è possibile creare un indice di chiave primaria o univoco (cluster o non cluster) sulle colonne ProductID e CustomerID della tabella dbo.Purchases e un indice cluster sulle colonne ProductID e CustomerID della tabella dbo.FactBuyingHabits. Per visualizzare il codice utilizzato per la creazione di queste tabelle, vedere Inserimento, aggiornamento ed eliminazione dei dati con 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.*;

Procedure consigliate per l'operatore JOIN

Per migliorare le prestazioni delle istruzioni MERGE e garantire che vengano ottenuti risultati corretti, si consiglia di attenersi alle linee guida relative ai join seguenti:

Specificare nella clausola ON <merge_search_condition> solo condizioni di ricerca che determinano i criteri per la corrispondenza dei dati nelle tabelle di origine e di destinazione, ovvero specificare solo colonne della tabella di destinazione confrontate con quelle corrispondenti della tabella di origine. Non includere confronti con altri valori, ad esempio una costante.

Per filtrare le righe delle tabelle di origine o di destinazione, effettuare una delle operazioni indicate di seguito.

Specificare la condizione di ricerca per applicare il filtro alla riga nella clausola WHEN appropriata, ad esempio WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Definire una vista sull'origine o sulla destinazione che restituisca le righe filtrate e faccia riferimento alla vista come la tabella di origine o di destinazione. Se la vista viene definita sulla tabella di destinazione, qualsiasi azione eseguita su tale vista deve soddisfare le condizioni per l'aggiornamento delle viste. Per ulteriori informazioni sull'aggiornamento di dati tramite una vista, vedere Modifica di dati tramite una vista.
Utilizzare la clausola WITH <common table expression> per filtrare le righe delle tabelle di origine o di destinazione. Questo metodo è analogo alla specifica di criteri di ricerca aggiuntivi nella clausola ON e può produrre risultati non corretti. Si consiglia di evitare l'utilizzo di questo metodo o di eseguirne un test accurato prima di implementarlo.

Per ulteriori informazioni, vedere Inserimento, aggiornamento ed eliminazione dei dati con MERGE.

Ottimizzazione di query relative ai join

L'operazione di join nelle istruzioni MERGE viene ottimizzata in modo analogo a un join in un'istruzione SELECT. Questo significa che durante l'elaborazione di join in SQL Server Query Optimizer sceglie il metodo di elaborazione del join più efficiente tra quelli possibili. Per ulteriori informazioni sulle operazioni di join, vedere Nozioni fondamentali sui join e Concetti di base sulle opzioni di ottimizzazione avanzate delle query. Quando le dimensioni dell'origine e della destinazione sono simili e le linee guida relative agli indici descritte nella sezione "Procedure consigliate per gli indici" vengono applicate alle tabelle di origine e di destinazione, un operatore merge join rappresenta il piano di query più efficiente poiché entrambe le tabelle vengono sottoposte a scansione una sola volta e non è necessario ordinare i dati. Quando le dimensioni della tabella di origine sono inferiori rispetto a quelle della tabella di destinazione, è preferibile utilizzare un operatore nested loops.

Per forzare l'utilizzo di un join specifico, è possibile indicare la clausola OPTION (<query_hint>) nelle istruzioni MERGE. Si consiglia di non utilizzare hash join come hint per la query per le istruzioni MERGE poiché questo tipo di join non utilizza indici. Per ulteriori informazioni sugli hint per la query, vedere Hint per la query (Transact-SQL). Nell'esempio seguente viene specificato un nested loop join nella clausola 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;

Procedure consigliate per la parametrizzazione

Se un'istruzione SELECT, INSERT, UPDATE o DELETE viene eseguita senza parametri, Query Optimizer di SQL Server può scegliere di parametrizzare l'istruzione internamente, ovvero di sostituire con parametri i valori letterali contenuti nella query. L'istruzione INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), ad esempio, può essere implementata internamente come INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Questo processo, denominato parametrizzazione semplice, consente di aumentare la capacità del motore relazionale di trovare una corrispondenza tra le nuove istruzioni SQL e i piani di esecuzione esistenti compilati in precedenza. Le prestazioni delle query possono risultare migliorate poiché viene ridotta la frequenza di compilazioni e ricompilazioni delle query stesse. Query Optimizer non applica il processo di parametrizzazione semplice alle istruzioni MERGE. Di conseguenza, le prestazioni delle istruzioni MERGE che contengono valori letterali potrebbero non corrispondere a quelle di singole istruzioni INSERT, UPDATE o DELETE poiché viene compilato un nuovo piano ogni volta che l'istruzione MERGE viene eseguita.

Per migliorare le prestazioni delle query, si consiglia di attenersi alle linee guida relative alla parametrizzazione seguenti:

Parametrizzare tutti i valori letterali contenuti nella clausola ON <merge_search_condition> e nella clausola WHEN dell'istruzione MERGE. È possibile ad esempio incorporare l'istruzione MERGE in una stored procedure sostituendo i valori letterali con parametri di input appropriati.
Se non è possibile parametrizzare l'istruzione, creare una guida di piano di tipo TEMPLATE e specificare l'hint per la query PARAMETERIZATION FORCED in tale guida. Per ulteriori informazioni, vedere Definizione delle funzionalità di parametrizzazione delle query tramite guide di piano.
Se le istruzioni MERGE vengono eseguite frequentemente nel database, impostare l'opzione PARAMETERIZATION relativa al database su FORCED. Si consiglia di prestare attenzione quando si imposta questa opzione, poiché l'opzione PARAMETERIZATION è un'impostazione a livello di database e influisce sulle modalità di elaborazione delle query sul database. Per ulteriori informazioni, vedere Parametrizzazione forzata.

Procedure consigliate per la clausola TOP

Nell'istruzione MERGE la clausola TOP specifica il numero o la percentuale di righe interessate dopo l'unione in join della tabella di origine e di quella di destinazione e dopo la rimozione delle righe non qualificate per un'azione di inserimento, aggiornamento o eliminazione. La clausola TOP riduce ulteriormente il numero di righe unite in join in base al valore specificato e l'azione di inserimento, aggiornamento o eliminazione viene applicata alle righe unite in join rimanenti in modo non ordinato. Questo significa che le righe vengono distribuite tra le azioni definite nelle clausole WHEN senza alcun ordine. La specifica della clausola TOP (10), ad esempio, influisce su 10 righe, 7 delle quali possono essere aggiornate e 3 inserite oppure 1 riga può essere eliminata, 5 aggiornate e 4 inserite e così via.

In genere la clausola TOP viene utilizzata per eseguire operazioni DML (Data Manipulation Language) in batch in una tabella di grandi dimensioni. Quando la clausola TOP viene utilizzata nell'istruzione MERGE per questo scopo, è importante comprendere le implicazioni seguenti.

Possibile impatto sulle prestazioni di I/O.

L'istruzione MERGE esegue una scansione completa di entrambe le tabelle di origine e di destinazione. Se l'operazione viene divisa in batch, è possibile ridurre il numero di operazioni di scrittura eseguite per ogni batch, sebbene ciascun batch eseguirà una scansione completa sia delle tabelle di origine che di quelle di destinazione. L'attività di lettura risultante può influire sulle prestazioni della query.
Possibile restituzione di risultati non corretti.

È importante garantire che tutti i batch successivi vengano destinati a nuove righe per evitare un comportamento non desiderato, ad esempio l'inserimento non corretto di righe duplicate nella tabella di destinazione. Questa situazione può verificarsi quando nella tabella di origine è contenuta una riga non presente in un batch di destinazione, ma presente nella tabella di destinazione complessiva.

Per garantire la restituzione di risultati corretti:
- Utilizzare la clausola ON per determinare le righe di origine che influiscono sulle righe di destinazione esistenti e le righe effettivamente nuove.
- Utilizzare una condizione aggiuntiva nella clausola WHEN MATCHED per determinare se la riga di destinazione è già stata aggiornata da un batch precedente.
Poiché la clausola TOP viene applicata solo dopo l'applicazione di queste clausole, a ogni esecuzione viene inserita solo una riga effettivamente non corrispondente o viene aggiornata una riga esistente. Nell'esempio seguente vengono create una tabella di origine e una di destinazione e successivamente viene illustrato il metodo corretto di utilizzo della clausola TOP per modificare la destinazione nelle operazioni batch.
```
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
```
Nell'esempio seguente viene illustrato un metodo non corretto di implementazione della clausola TOP. Il controllo della colonna is_current viene specificato nella condizione di join con la tabella di origine. Questo significa che una riga di origine utilizzata in un batch verrà considerata come "non corrispondente" nel batch successivo, determinando pertanto l'esecuzione di un'operazione di inserimento non desiderata.
```
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
```
Nell'esempio seguente viene illustrato un ulteriore metodo non corretto. Se si utilizza un'espressione di tabella comune (CTE, Common Table Expression) per limitare il numero righe lette per il batch, qualsiasi riga di origine eventualmente corrispondente a una riga di destinazione diversa da quella selezionata dalla clausola TOP(1) viene considerata come "non corrispondente", determinando pertanto l'esecuzione di un'operazione di inserimento non desiderata. Questo metodo limita inoltre solo il numero di righe che possono essere aggiornate e ciascun batch tenterà di inserire tutte le righe di origine "non corrispondenti".
```
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
```

Procedure consigliate per il caricamento bulk

L'istruzione MERGE può essere utilizzata per eseguire in modo efficiente il caricamento bulk di dati da un file di dati di origine in una tabella di destinazione specificando la clausola OPENROWSET(BULK...) come origine della tabella. In questo modo, l'intero file viene elaborato in un unico batch.

Per migliorare le prestazioni del processo di merge di tipo bulk, si consiglia di attenersi alle linee guida seguenti:

Creare un indice cluster sulle colonne di join della tabella di destinazione.
Utilizzare gli hint ORDER e UNIQUE nella clausola OPENROWSET(BULK...) per specificare l'ordinamento del file di dati di origine.

Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. Di conseguenza, è importante che i dati di origine siano ordinati in base all'indice cluster nella tabella di destinazione e che l'hint ORDER venga utilizzato per indicare l'ordine, in modo che Query Optimizer possa generare un piano di query più efficiente. Gli hint vengono convalidati in fase di esecuzione. Se il flusso di dati non è conforme agli hint specificati, viene generato un errore.

Queste linee guida garantiscono che le chiavi di join siano univoche e che l'ordinamento dei dati nel file di origine corrisponda alla tabella di destinazione. Le prestazioni delle query risultano migliorate poiché non sono necessarie ulteriori operazioni di ordinamento né vengono richieste copie dei dati non necessarie. Nell'esempio seguente viene utilizzata l'istruzione MERGE per eseguire il caricamento bulk di dati dal file flat StockData.txt nella tabella di destinazione dbo.Stock. Se si definisce un vincolo di chiave primaria su StockName nella tabella di destinazione, viene creato un indice cluster sulla colonna utilizzata per eseguire l'unione in join con i dati di origine. Gli hint ORDER e UNIQUE vengono applicati alla colonna Stock nell'origine dati mappata alla colonna chiave di indice cluster nella tabella di destinazione.

Prima di eseguire questo esempio, creare un file di testo denominato 'StockData.txt' nella cartella C:\SQLFiles\. Il file deve contenere due colonne di dati separati da una virgola. Utilizzare ad esempio i dati seguenti.

M mountain bike alpina,100

Set freni,22

Cuscinetto,5

Creare quindi un file in formato xml denominato 'BulkloadFormatFile.xml' nella cartella C:\SQLFiles\. Utilizzare le informazioni seguenti.

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

Misurazione e diagnosi delle prestazioni delle istruzioni MERGE

Per effettuare la misurazione e la diagnosi delle prestazioni delle istruzioni MERGE, sono disponibili le funzionalità seguenti.

Contatore merge stmt nella funzione a gestione dinamica sys.dm_exec_query_optimizer_info, che consente di restituire il numero di ottimizzazioni di query per le istruzioni MERGE.
Attributo merge_action_type nella funzione a gestione dinamica sys.dm_exec_plan_attributes, che consente di restituire il tipo del piano di esecuzione del trigger utilizzato come il risultato di un'istruzione MERGE.
Traccia SQL, che consente di raccogliere i dati relativi alla risoluzione dei problemi per l'istruzione MERGE in modo analogo alla raccolta dei dati per altre istruzioni DML (Data Manipulation Language). Per ulteriori informazioni, vedere Introduzione a Traccia SQL.