Artigo
09/28/2010

Otimizando o desempenho da instrução MERGE

No SQL Server 2008, você pode executar várias operações DML (linguagem de manipulação de dados) em uma única instrução usando a instrução MERGE. Por exemplo, talvez você precise sincronizar duas tabelas inserindo, atualizando ou excluindo linhas de uma tabela com base nas diferenças encontradas na outra tabela. Normalmente, isso é feito executando-se um procedimento armazenado ou um lote que contém instruções INSERT, UPDATE e DELETE individuais. No entanto, isso significa que os dados contidos nas tabelas de origem e de destino são avaliados e processados várias vezes; pelo menos uma vez para cada instrução.

Utilizando a instrução MERGE, você pode substituir as instruções DML individuais por uma única instrução. Isso melhora o desempenho de consultas porque as operações são executadas em uma única instrução, diminuindo assim o número de vezes em que os dados das tabelas de origem e de destino são processados. Todavia, os ganhos de desempenho dependem de você ter os índices e junções corretos e de outras considerações. Este tópico apresenta sugestões de práticas recomendadas que ajudarão você a obter o desempenho ideal quando usar a instrução MERGE.

Práticas recomendadas de índice

Para melhorar o desempenho da instrução MERGE, é recomendável seguir estas diretrizes de índice:

Crie um índice com base nas colunas de junção da tabela de origem que seja exclusivo e abrangente.
Crie um índice clusterizado exclusivo com base nas colunas de junção da tabela de destino.

Esses índices asseguram que as chaves de junção sejam exclusivas e que os dados das tabelas sejam classificados. O desempenho das consultas é aprimorado porque o otimizador de consulta não precisa executar processamento de validação extra para localizar e atualizar linhas duplicadas, e não há necessidade de operações de suporte adicionais.

Por exemplo, na instrução MERGE a seguir, a tabela de origem dbo.Purchases e a tabela de destino dbo.FactBuyingHabits são unidas nas colunas ProductID e CustomerID. Para melhorar o desempenho dessa instrução, você deve criar um índice exclusivo ou de chave primária (clusterizado ou não clusterizado) nas colunas ProductID e CustomerID da tabela dbo.Purchases e um índice clusterizado nas colunas ProductID e CustomerID da tabela dbo.FactBuyingHabits. Para exibir o código usado na criação dessas tabelas, consulte Inserindo, atualizando e excluindo dados usando 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.*;

Práticas recomendadas de JOIN

Para melhorar o desempenho da instrução MERGE e assegurar a obtenção dos resultados corretos, é recomendável seguir estas diretrizes de junção:

Especifique apenas condições de pesquisa na cláusula ON <merge_search_condition> que determinem os critérios para corresponder dados nas tabelas de origem e de destino. Isto é, especifique apenas as colunas da tabela de destino que são comparadas com as colunas correspondentes da tabela de origem. Não inclua comparações com outros valores, como uma constante.

Para filtrar linhas das tabelas de origem ou de destino, use um dos métodos a seguir.

Especifique o critério de pesquisa para filtragem de linhas na cláusula WHEN apropriada. Por exemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Defina uma exibição na origem ou no destino que retorne as linhas filtradas e faça referência à exibição como a tabela de origem ou de destino. Se a exibição for definida na tabela de destino, quaisquer ações sobre ela deverão atender às condições para atualização das exibições. Para obter mais informações sobre como atualizar dados usando uma exibição, consulte Modificando dados por meio de uma exibição.
Use a cláusula de <expressão de tabela comum> WITH para filtrar linhas das tabelas de origem e de destino. Esse método é semelhante a especificar critérios adicionais de pesquisa na cláusula ON e pode produzir resultados incorretos. É recomendável evitar o uso desse método ou testá-lo criteriosamente antes de implementá-lo.

Para obter mais informações, consulte Inserindo, atualizando e excluindo dados usando MERGE.

Otimização de consulta de junções

A operação de junção na instrução MERGE é otimizada da mesma forma que uma junção na instrução SELECT. Ou seja, quando o SQL Server processa junções, o otimizador escolhe o método mais eficaz (entre várias possibilidades) de processamento da junção. Para obter mais informações sobre junções, consulte Conceitos básicos de junção e Conceitos de ajuste de consulta avançada. Quando a origem e o destino têm tamanho semelhante e as diretrizes de índice descritas anteriormente na seção 'Práticas recomendadas de índice' são aplicadas às tabelas de origem e de destino, um operador merge join é o plano de consulta mais eficiente. Isso porque ambas as tabelas são examinadas uma vez e não há necessidade de classificar os dados. Quando a tabela de origem é menor do que a de destino, é preferível usar um operador nested loops.

Você pode forçar o uso de uma determinada junção especificando a cláusula OPTION (<query_hint>) na instrução MERGE. É recomendável não usar a junção hash como dica de consulta para instruções MERGE, pois esse tipo de junção não usa índices. Para obter mais informações sobre dicas de consulta, consulte dicas de consulta (Transact-SQL). O exemplo a seguir especifica uma junção de loops aninhados na cláusula 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;

Práticas recomendadas de parametrização

Se uma instrução SELECT, INSERT, UPDATE ou DELETE for executada sem parâmetros, o otimizador de consulta do SQL Server poderá optar por parametrizar a instrução internamente. Isso significa que qualquer valor literal contido na consulta é substituído por parâmetros. Por exemplo, a instrução INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) pode ser implementada internamente como INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Esse processo, chamado de parametrização simples, aumenta a capacidade do mecanismo relacional de comparar as novas instruções SQL com planos existentes de execução anteriormente compilados. O desempenho da consulta pode ser melhorado porque a freqüência das compilações e recompilações de consulta é reduzida. O otimizador de consulta não aplica o processo de parametrização simples a instruções MERGE. Por isso, as instruções MERGE que contêm valores literais podem não executar tão bem quanto as instruções INSERT, UPDATE ou DELETE individuais, pois um novo plano é compilado sempre que a instrução MERGE é executada.

Para melhorar o desempenho da consulta, é recomendável seguir estas diretrizes de parametrização:

Parametrize todos os valores literais na cláusula ON <merge_search_condition> e nas cláusulas WHEN da instrução MERGE. Por exemplo, você pode incorporar a instrução MERGE a um procedimento armazenado que substitua os valores literais por parâmetros de entrada apropriados.
Se você não conseguir parametrizar a instrução, crie um guia de plano do tipo TEMPLATE e especifique a dica de consulta PARAMETERIZATION FORCED no guia de plano. Para obter mais informações, consulte Especificando comportamento de parametrização de consulta usando guias de plano.
Se instruções MERGE forem executadas com freqüência no banco de dados, considere definir a opção PARAMETERIZATION no banco de dados como FORCED. Tome cuidado quando for definir esta opção. A opção PARAMETERIZATION é uma configuração de banco de dados e afeta a maneira como são processadas todas as consultas feitas nele. Para obter mais informações, consulte Parametrização forçada.

Práticas recomendadas da cláusula TOP

Na instrução MERGE, a cláusula TOP especifica o número ou a porcentagem de linhas que são afetadas depois que as tabelas de origem e de destino são unidas e que as linhas que não se qualificam para uma ação de inserção, atualização ou exclusão são removidas. A cláusula TOP ainda reduz o número de linhas unidas para o valor especificado e as ações de inserção, atualização ou exclusão são aplicadas às linhas unidas restantes de uma forma não ordenada. Isto é, não há nenhuma ordem na qual as linhas são distribuídas entre as ações definidas nas cláusulas WHEN. Por exemplo, especificar TOP (10) afeta 10 linhas. Dessas linhas, 7 podem ser atualizadas e 3 inseridas ou 1 pode ser excluída, 5 atualizadas e 4 inseridas, etc.

É comum usar a cláusula TOP para executar operações DML (linguagem de manipulação de dados) em uma tabela grande em lotes. Quando se usa a cláusula TOP na instrução MERGE para esta finalidade, é importante entender as implicações a seguir.

O desempenho de E/S poderá ser afetado.

A instrução MERGE faz uma verificação completa das tabelas de origem e de destino. Dividir a operação em lotes diminui o número de operações de gravação executadas por lote; no entanto, cada lote fará uma verificação completa das tabelas de origem e de destino. A atividade de leitura resultante poderá afetar o desempenho da consulta.
Pode haver resultados incorretos.

É importante assegurar que todos os lotes em sucessão visem as novas linhas; do contrário, poderão ocorrer comportamentos indesejados, como inserção incorreta de linhas duplicadas na tabela de destino. Isso pode acontecer quando a tabela de origem inclui uma linha que não estava em um lote de destino, mas que estava na tabela de destino global.

Para assegurar resultados corretos:
- Use a cláusula ON para determinar quais linhas de origem afetam linhas de destino existentes e quais são genuinamente novas.
- Use uma condição adicional na cláusula WHEN MATCHED para determinar se a linha de destino já foi atualizada por um lote anterior.
Como a cláusula TOP só é aplicada depois que essas cláusulas são aplicadas, cada execução insere uma linha genuinamente não correspondente ou atualiza uma linha existente. O exemplo a seguir cria uma tabela de origem e de destino e apresenta o método correto de usar a cláusula TOP para modificar o destino em operações em lote.
```
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
```
O exemplo a seguir demonstra um método incorreto de implementar a cláusula TOP. A verificação da coluna is_current é especificada na condição de junção com a tabela de origem. Isso significa que uma linha de origem usada em um lote será tratada como "não correspondida" no próximo lote, o que leva a uma operação de inserção indesejada.
```
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
```
O exemplo a seguir também ilustra um método incorreto. Usando uma expressão de tabela comum (CTE) para restringir o número de linhas lidas para o lote, qualquer linha de origem que corresponder a uma linha de destino diferente daquela selecionada pela cláusula TOP(1) será tratada como "não correspondida", o que resultará em uma operação de inserção indesejada. Além disso, esse método limita apenas o número de linhas que podem ser atualizadas; cada lote tentará inserir todas as linhas de origem "não correspondidas".
```
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
```

Práticas recomendadas de carregamento em massa

A instrução MERGE pode ser usada para carregar com eficiência dados de carregamento em massa de um arquivo de dados de origem em uma tabela de destino especificando-se a cláusula OPENROWSET(BULK…) como tabela de origem. Dessa forma, o arquivo inteiro é processado em um único lote.

Para melhorar o desempenho do processo de mesclagem em lote, é recomendável seguir estas diretrizes:

Crie um índice clusterizado com base nas colunas de junção da tabela de destino.
Use as dicas ORDER e UNIQUE na cláusula OPENROWSET(BULK…) para especificar como o arquivo de dados de origem deverá ser classificado.

Por padrão, a operação em massa assume que o arquivo de dados não está ordenado. Por isso, é importante que os dados de origem sejam classificados de acordo com o índice clusterizado na tabela de destino e que a dica ORDER seja usada para indicar a ordem, de modo que o otimizador de consulta possa gerar um plano de consulta mais eficaz. As dicas são validadas em tempo de execução; se o fluxo de dados não estiver de acordo com as dicas especificadas, ocorrerá um erro.

Essas diretrizes asseguram que as chaves de junção sejam exclusivas e que a ordem de classificação dos dados do arquivo de origem corresponda à da tabela de destino. O desempenho das consultas é aprimorado porque não há necessidade de executar operações de classificação adicionais e não são exigidas cópias de dados desnecessárias. O exemplo abaixo usa a instrução MERGE para carregar dados em massa de StockData.txt, um arquivo simples, na tabela de destino dbo.Stock. Definindo-se uma restrição de chave primária em StockName na tabela de destino, será criado um índice clusterizado na coluna utilizada para junção com os dados de origem. As dicas ORDER e UNIQUE são aplicadas à coluna Stock na fonte de dados, que é mapeada para a coluna de chave de índice clusterizado da tabela de destino.

Antes de executar este exemplo, crie um arquivo de texto chamado 'StockData.txt' na pasta C:\SQLFiles\. Ele deve ter duas colunas de dados separados por vírgula. Por exemplo, use os dados a seguir:

Alpine mountain bike,100

Brake set,22

Cushion,5

Em seguida, crie um arquivo xml chamado 'BulkloadFormatFile.xml' na pasta C:\SQLFiles\. Use as seguintes informações:

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

Avaliação e diagnóstico do desempenho de MERGE

Os recursos a seguir estão disponíveis para ajudar você a avaliar e diagnosticar o desempenho de instruções MERGE.

Use o contador merge stmt na função de gerenciamento dinâmico sys.dm_exec_query_optimizer_info para retornar o número de otimizações de consulta relativas a instruções MERGE.
Use o atributo merge_action_type na função de gerenciamento dinâmico sys.dm_exec_plan_attributes para retornar o tipo de plano de execução de gatilho usado como resultado de uma instrução MERGE.
Use o Rastreamento SQL para coletar dados de solução de problemas para a instrução MERGE da mesma forma que você faria para outras instruções DML. Para obter mais informações, consulte Apresentando o Rastreamento SQL.