Artículo
09/29/2010

Optimizar el rendimiento de la instrucción MERGE

En SQL Server 2008, puede realizar varias operaciones del lenguaje de manipulación de datos (DML) en una instrucción única mediante la instrucción MERGE. Por ejemplo, puede que necesite sincronizar dos tablas insertando, actualizando o eliminando las filas de una tabla según las diferencias que se encuentren en la otra. Normalmente, esto se realiza ejecutando un procedimiento almacenado o lote que contiene instrucciones individuales INSERT, DELETE y UPDATE. Sin embargo, esto significa que tanto en las tablas de destino como en las de origen se evalúan y se procesan los datos varias veces; por lo menos una vez por cada instrucción.

Mediante la instrucción MERGE, puede reemplazar las instrucciones DML individuales con una instrucción única. Esto puede mejorar el rendimiento de las consultas debido a que las operaciones se realizan dentro de una instrucción única y, por consiguiente, se reduce el número de veces que se procesan los datos en las tablas de destino y de origen. Sin embargo, las mejoras en el rendimiento dependerán de si hay índices, combinaciones y otras consideraciones correctas en su lugar. En este tema se proporcionan prácticas recomendadas que servirán de ayuda para lograr un rendimiento óptimo cuando se use la instrucción MERGE.

Prácticas recomendadas para índices

Para mejorar el rendimiento de la instrucción MERGE, recomendamos las siguientes instrucciones de índices:

Cree un índice en las columnas de combinación de la tabla de origen que sea única y de cobertura.
Cree un índice en un clúster único en las columnas de combinación de la tabla de destino.

Estos índices aseguran que las claves de combinación son únicas y los datos de las tablas están ordenados. Se mejora el rendimiento de las consultas debido a que el optimizador de consultas no necesita realizar un procesamiento de validación adicional para buscar y actualizar filas duplicadas, y no son necesarias operaciones de ordenación adicionales.

Por ejemplo, en la siguiente instrucción MERGE, la tabla de origen, dbo.Purchases, y la tabla de destino, dbo.FactBuyingHabits, se combinan en las columnas ProductID y CustomerID. Para mejorar el rendimiento de esta instrucción, crearía un índice de clave única o primario (clúster o no clúster) en las columnas ProductID y CustomerID de la tabla dbo.Purchases, y un índice clúster en las columnas ProductID y CustomerID de la tabla dbo.FactBuyingHabits. Para ver el código que se usa para crear estas tablas, vea Insertar, actualizar y eliminar datos mediante 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ácticas recomendadas para JOIN

Para mejorar el rendimiento de la instrucción MERGE y asegurarse de que se obtienen los resultados correctos, recomendamos las siguientes instrucciones de combinación:

Especifique únicamente las condiciones de búsqueda en la cláusula ON <merge_search_condition> que determinan los criterios para que coincidan los datos en las tablas de origen y de destino. Es decir, especifique solo las columnas de la tabla de destino que se comparan con las correspondientes columnas de la tabla de origen. No incluya comparaciones a otros valores como una constante.

Para filtrar las filas de las tablas de origen o de destino, use uno de los métodos siguientes.

Especifique la condición de búsqueda para el filtrado de filas en la cláusula WHEN adecuada. Por ejemplo, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Defina una vista en el origen o destino que devuelva las filas filtradas y haga referencia a la vista como la tabla de origen o de destino. Si se define la vista en la tabla de destino, cualquier acción con ella debe satisfacer las condiciones para actualizar las vistas. Para obtener más información acerca de cómo actualizar datos mediante una vista, vea Modificar datos mediante una vista.
Use la cláusula WITH <expresión de tabla común> para filtrar filas de las tablas de origen o de destino. Este método es similar a especificar el criterio de búsqueda adicional en la cláusula ON y puede generar resultados incorrectos. Se recomienda evitar el uso de este método o prueba de manera exhaustiva antes de implementarlo.

Para obtener más información, vea Insertar, actualizar y eliminar datos mediante MERGE.

Optimizar consultas de combinaciones

La operación de combinación en la instrucción MERGE se optimiza de la misma manera que una combinación en una instrucción SELECT. Es decir, cuando SQL Server procesa combinaciones, el optimizador de consultas elige el método más eficaz entre varias posibilidades para procesar la combinación. Para obtener más información acerca de las combinaciones, vea Aspectos básicos de las combinaciones y Conceptos de la optimización avanzada de consultas. Cuando el origen y el destino son de tamaño similar y las instrucciones de índice descritas anteriormente en la sección 'Prácticas recomendadas para índices' se aplican a las tablas de destino y de origen, el plan de consultas más eficaz es un operador de combinación de mezcla. Esto es debido a que ambas tablas se examinan una vez y no hay necesidad de ordenar los datos. Cuando el origen es menor que la tabla de destino, es preferible usar un operador de bucles anidados.

Puede exigir el uso de una combinación concreta especificando la cláusula OPTION (<query_hint>) en la instrucción MERGE. Se recomienda no usar la combinación hash como una sugerencia de consulta para las instrucciones MERGE porque este tipo de combinación no usa índices. Para obtener más información acerca de las sugerencias de consulta, vea Sugerencias de consulta (Transact-SQL). En el ejemplo siguiente se especifica una combinación de bucle anidado en la 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ácticas recomendadas para parametrización

Si una instrucción SELECT, INSERT, UPDATE o DELETE se ejecuta sin parámetros, el optimizador de consultas de SQL Server puede decidir parametrizar la instrucción internamente. Esto indica que todos los valores literales incluidos en la consulta se sustituirán por parámetros. Por ejemplo, la instrucción INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10), se puede implementar internamente como INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Este proceso, denominado parametrización simple, aumenta la capacidad del motor relacional para que coincida con nuevas instrucciones SQL con los planes de ejecución existentes compilados previamente. Se puede mejorar el rendimiento de las consultas debido a que se reduce la frecuencia de las compilaciones y recompilaciones de la consulta. El optimizador de consultas no aplica el proceso de parametrización simple a las instrucciones MERGE. Por consiguiente, puede que no se realicen las instrucciones MERGE que contienen los valores literales, además de las instrucciones INSERT, DELETE o UPDATE individuales, porque se compila un plan nuevo cada vez que se ejecuta la instrucción MERGE.

Para mejorar el rendimiento de las consultas, recomendamos las siguientes instrucciones de parametrización:

Parametrice todos los valores literales en la cláusula ON <merge_search_condition> y en las cláusulas WHEN de la instrucción MERGE. Por ejemplo, puede incorporar la instrucción MERGE en un procedimiento almacenado que reemplaza los valores literales con parámetros de entrada adecuados.
Si no puede parametrizar la instrucción, cree una guía de plan de tipo TEMPLATE y especifique la sugerencia de consulta PARAMETERIZATION FORCED en la guía de plan. Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.
Si las instrucciones MERGE se ejecutan con frecuencia en la base de datos, considere la posibilidad de establecer en FORCED la opción PARAMETERIZATION en la base de datos. Actúe con precaución cuando establezca esta opción. La opción PARAMETERIZATION es un valor de nivel de base de datos y afecta a la manera en que se procesan todas las consultas con la base de datos. Para obtener más información, vea Parametrizaciones forzadas.

Prácticas recomendadas para la cláusula TOP

En la instrucción MERGE, la cláusula TOP especifica el número o porcentaje de filas afectadas después de que la tabla de origen y la tabla de destino se combinen, y después de quitar las filas que no cumplen los requisitos para una acción de inserción, actualización o eliminación. La cláusula TOP reduce aún más el número de filas combinadas al valor especificado y se aplican las acciones de inserción, actualización o eliminación a las filas combinadas restantes de una manera desordenada. Es decir, no hay ningún orden en el que las filas se distribuyan entre las acciones definidas en las cláusulas WHEN. Por ejemplo, cuando se especifica TOP (10), afecta a 10 filas; de estas filas 7 se pueden actualizar y 3 insertar, o se puede eliminar 1, actualizar 5 e insertar 4, etc.

Es habitual usar la cláusula TOP para realizar operaciones del lenguaje de manipulación de datos (DML) en una tabla grande en lotes. Cuando se usa la cláusula TOP en la instrucción MERGE con este fin, es importante comprender las implicaciones siguientes.

Puede afectar al rendimiento de la E/S.

La instrucción MERGE realiza exámenes de tabla completos, tanto de las tablas de destino como de origen. Al dividir la operación en lotes, se reduce el número de operaciones de escritura realizadas por lote; sin embargo, cada lote realiza exámenes de tabla completos, tanto de las tablas de destino como de origen. La actividad de lectura resultante puede afectar al rendimiento de la consulta.
Se pueden producir resultados incorrectos.

Es importante asegurarse de que todos los lotes sucesivos se destinen a filas nuevas o puede producirse un comportamiento no deseado como la inserción incorrecta de filas duplicadas en la tabla de destino. Esto puede ocurrir cuando la tabla de origen incluye una fila que no estaba en un lote de destino pero estaba en la tabla de destino total.

Para asegurar que los resultados son correctos:
- Use la cláusula ON para determinar qué filas de origen afectan a las filas de destino existentes y cuáles son auténticamente nuevas.
- Use una condición adicional en la cláusula WHEN MATCHED para determinar si un lote anterior ya ha actualizado la fila de destino.
Dado que la cláusula TOP solo se aplica una vez aplicadas estas cláusulas, cada ejecución inserta una fila no coincidente inigualable o actualiza una fila existente. En el ejemplo siguiente se crea una tabla de origen y de destino, y a continuación se muestra el uso correcto de la cláusula TOP para modificar el destino en operaciones por lotes.
```
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
```
En el ejemplo siguiente se muestra un método incorrecto de implementación de la cláusula TOP. La comprobación de la columna is_current se especifica en la condición de combinación con la tabla de origen. Esto significa que una fila de origen que se usa en un lote se tratará como "no coincidente" en el lote siguiente, produciendo una operación de inserción no deseada.
```
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
```
El ejemplo de código siguiente también muestra un diseño de método incorrecto. Mediante el uso de una expresión de tabla común (CTE) para restringir el número de filas leídas en el lote, cualquier fila de origen que hubiera coincidido con una fila de destino distinta de la seleccionada por TOP (1) se trata como "no coincidente", produciendo una operación de inserción no deseada. Además, este método sólo limita el número de filas que se pueden actualizar; cada lote intentará insertar todas las filas de origen "no coincidentes".
```
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ácticas recomendadas para carga masiva

La instrucción MERGE se puede usar para cargar eficazmente datos de manera masiva del archivo de datos de origen en una tabla de destino especificando la cláusula OPENROWSET (BULK.) como el origen de la tabla. De esta forma, el archivo completo se procesa en un lote único.

Para mejorar el rendimiento del proceso de mezcla masiva, recomendamos las siguientes instrucciones:

Cree un índice clúster en las columnas de combinación de la tabla de destino.
Use las sugerencias ORDER y UNIQUE en la cláusula OPENROWSET (BULK.) para especificar cómo se ordena el archivo de datos de origen.

De forma predeterminada, la operación masiva presupone que los datos del archivo no están ordenados. Por consiguiente, es importante que los datos del origen estén ordenados según el índice clúster de la tabla de destino y que la sugerencia ORDER se use para indicar el orden, de manera que el optimizador de consultas pueda generar un plan de consultas más eficaz. Las sugerencias se validan en tiempo de ejecución; si la secuencia de datos no se ajusta a las sugerencias especificadas, se produce un error.

Estas instrucciones garantizan que las claves de unión son únicas y que el criterio de ordenación de los datos en el archivo de origen coincide con la tabla de destino. Se mejora el rendimiento de las consultas debido a que las operaciones de ordenación adicionales no son necesarias y no se requieren copias innecesarias de datos. En el siguiente ejemplo se usa la instrucción MERGE para realizar la carga masiva de datos desde un archivo plano, StockData.txt, en la tabla de destino dbo.Stock. Al definir una restricción de clave principal en StockName en la tabla de destino, se crea un índice clúster en la columna combinaba con los datos de origen. Las sugerencias UNIQUE y ORDER se aplican a la columna Stock en el origen de datos, que se asigna a la columna de clave del índice clúster en la tabla de destino.

Antes de ejecutar este ejemplo, cree un archivo de texto denominado 'StockData.txt' en la carpeta C:\SQLFiles\. El archivo debería tener dos columnas de datos separadas por una coma. Por ejemplo, use los datos siguientes.

Alpine mountain bike,100

Brake set,22

Cushion,5

Luego, cree un archivo de formato xml denominado 'BulkloadFormatFile.xml' en la carpeta C:\SQLFiles\. Use la siguiente información.

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

Medir y diagnosticar el rendimiento de MERGE

Las características siguientes están disponibles para ayudarle a medir y diagnosticar el rendimiento de las instrucciones MERGE.

Use el contador merge stmt en la administración dinámica sys.dm_exec_query_optimizer_info para devolver el número de optimizaciones de consulta para las instrucciones MERGE.
Use el atributo merge_action_type en la función de administración dinámica sys.dm_exec_plan_attributes para devolver el tipo de plan de ejecución de desencadenadores que se usa como el resultado de una instrucción MERGE.
Use Seguimiento SQL para recopilar datos de la solución de problemas de la instrucción MERGE de la misma manera que haría para otras instrucciones del lenguaje de manipulación de datos (DML). Para obtener más información, vea Introducción a Traza de SQL.