Con la llegada de SQL Server 2008 tenemos una nueva estructura (del estándar del ANSI 2003) que nos permite realizar de forma sencilla operaciones de mezcla de datos. Es muy habitual encontrarnos en escenarios en los cuales tenemos que integrar un conjunto de cambios (inserciones, actualizaciones y/o borrados) partiendo de una fuente de datos sobre un destino de datos. Hasta ahora lo habitual era encapsular dicho proceso dentro de una transacción para asegurar su atomicidad y proceder a un conjunto de operaciones lógicas que decidan que registros insertar, cuales modificar y cuales borrar. Esto daba lugar normalmente a tres operaciones DML donde se solían repetir lecturas sobre distintas tablas entre los distintos planes de ejecución independientes.
Pensad por ejemplo en los cambios de inventario o de precios de productos donde esta situación es bastante habitual. También muchos habréis intuido que puede tratarse de una excelente oportunidad en ocasiones a pequeños procesos ETL o a replicaciones de Mezcla que no requieran resolución de conflictos.
Para la implementación de dicha operación, SQL Server cuenta con dos operadores adicionales:
- Clustered Index Merge
- Table Merge
Estos dos operadores se caracterizan por aplicar, en un único recorrido de índice/tabla todos los cambios (inserciones/modificaciones/borrados) que sean necesarios. El orden en el que se realicen dichas operaciones queda al libre albedrío del operador el cual tratará de minimizar el coste por ejemplo realizando las operaciones mientras recorre el índice en orden evitando así rewinds innecesarios. Otra característica que tenemos que tener en cuenta es que el operador MERGE detectará si una misma fila se verá afectada por más de una operación, generando en dicho caso un error, por lo que la lógica que apliquemos, dada una fila, deberá generar únicamente una operación y no varias. Esto es importante pues puede que un conjunto de operaciones insert/update/delete tal cual las tenemos definidas no se puedan aplicar en un único merge si existe ese overlapping de acciones por fila.
Como ejemplo comprobaremos el impacto del uso de MERGE como alternativa a un conjunto de tres operaciones DML dentro de la misma transacción para la actualización de una lista de productos. Comprobaremos con una mezcla de 11 cambios y con una de 201 sobre un total de unos 500 artículos. La operación consistirá en insertar nuevos artículos, actualizar precios de los existentes y eliminar los descatalogados (no existentes).
USE AdventureWorks2008
— Almacenamos 10/200 filas “modificadas”
DECLARE @filas int = 10 — 200
SELECT TOP (@filas) * INTO Production.ProductChanges FROM Production.Product
ORDER BY ProductID
— Volvamos a una tabla todos los productos
SELECT * INTO Production.Product2 FROM Production.Product
— Creamos un par de índices cluster
create unique clustered index ix_productchanges on Production.ProductChanges( productid asc)
create unique clustered index ix_product2 on Production.Product2( productid asc)
— Insertamos una fila nueva
INSERT INTO Production.ProductChanges (Name, ProductNumber, ListPrice, SafetyStockLevel, ReorderPoint, StandardCost, DaysToManufacture, SellStartDate, MakeFlag, FinishedGoodsFlag, rowguid, ModifiedDate)
VALUES (‘Mi producto’,‘XX-1234’, 12.34, 34, 12, 5.3, 4, GETDATE(),1,1, NEWID(), GetDate())
Una vez tenemos los datos, lanzaremos un MERGE midiendo el número de lecturas, consumo de CPU y duración total de la operación:
MERGE INTO Production.Product2 AS DestinoMezcla
USING Production.ProductChanges as OrigenCambios
ON OrigenCambios.ProductID = DestinoMezcla.ProductID
AND OrigenCambios.Name = DestinoMezcla.Name
WHEN MATCHED THEN
UPDATE SET DestinoMezcla.ListPrice = OrigenCambios.ListPrice
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ProductNumber, ListPrice, SafetyStockLevel, ReorderPoint, StandardCost, DaysToManufacture, SellStartDate, MakeFlag, FinishedGoodsFlag, rowguid, ModifiedDate)
VALUES (OrigenCambios.Name,OrigenCambios.ProductNumber, OrigenCambios.ListPrice, OrigenCambios.SafetyStockLevel, OrigenCambios.ReorderPoint, OrigenCambios.StandardCost, OrigenCambios.DaysToManufacture, GETDATE(),1,1, NEWID(), GetDate())
WHEN NOT MATCHED BY SOURCE THEN DELETE;
— Sin índice (Table Merge)
— 11 filas –> 140 ms 760 reads 16 writes 297 ms
— 201 filas –> 156 ms 781 reads 12 writes 211 ms
— Con índice cluster por ProductID (Clustered Index Merge)
— 11 filas –> 62 ms 2261 reads 19 writes 118 ms
— 201 filas –> 62 ms 2283 reads 16 writes 141 ms
Y una alternativa con 3 DML podría ser la siguiente:
begin tran
INSERT INTO Production.Product2
(Name, ProductNumber, ListPrice, SafetyStockLevel, ReorderPoint, StandardCost, DaysToManufacture, SellStartDate, MakeFlag, FinishedGoodsFlag, rowguid, ModifiedDate)
SELECT OrigenCambios.Name,OrigenCambios.ProductNumber, OrigenCambios.ListPrice, OrigenCambios.SafetyStockLevel, OrigenCambios.ReorderPoint, OrigenCambios.StandardCost, OrigenCambios.DaysToManufacture, GETDATE(),1,1, NEWID(), GetDate()
FROM Production.ProductChanges OrigenCambios
LEFT JOIN Production.Product2 DestinoMezcla ON OrigenCambios.ProductID = DestinoMezcla.ProductID AND OrigenCambios.Name = DestinoMezcla.Name
WHERE DestinoMezcla.ProductID is null
UPDATE Production.Product2
SET Production.Product2.ListPrice = OrigenCambios.ListPrice
FROM Production.ProductChanges OrigenCambios
INNER JOIN Production.Product2 DestinoMezcla ON OrigenCambios.ProductID = DestinoMezcla.ProductID AND OrigenCambios.Name = DestinoMezcla.Name
DELETE FROM Production.Product2
WHERE productNumber IN(
SELECT productnumber
FROM Production.Product2
EXCEPT
SELECT productnumber
FROM Production.ProductChanges OrigenCambios
)
commit tran
— Sin índices (Table insert, update y delete)
— 11 Filas –> 62ms 934 reads 16 writes 360 ms
— 201 filas –> 78ms 961 reads 14 writes 468 ms
— Con índice cluster ProductID (Clustered index insert, update y delete)
— 11 Filas –> 109ms 1282 reads 13 writes 335 ms
— 201 filas –> 109ms 936 reads 12 writes 386 ms
Como podemos ver respecto a la duración total del batch el uso de MERGE reduce considerablemente el tiempo total. La ejecución secuencial de tres operaciones tiene un lastre que estamos pagando claramente. Por otra parte si nos fijamos en el número de lecturas vemos que la operación de MERGE resulta más eficiente cuando disponemos de índices apropiados. En este caso, se minimizan los recorridos aunque el total de lecturas es mayor que en la alternativa con 3 DML. Por tanto, a falta de pruebas con mayor profundidad, creemos que el uso del operador MERGE, al igual que ocurre con el operador MERGE JOIN, será especialmente eficaz cuando el conjunto de datos a recorrer sea alto y tratemos con conjuntos ordenados. Como punto negativo consideramos que el diferencial de consumo de CPU cuando no tenemos índice cluster es muy alto, pasando a unos 150 ms mientras que la alternativa se queda en unos 75 ms aproximadamente. No tenemos un ganador claro y único por lo que deberemos analizar cada caso comenzando por la viabilidad o no de utilizar MERGE y pasando por pruebas de rendimiento inevitablemente.
¿Qué ocurre cuando entran en juego los triggers en esta operación MERGE? El primer cambio que nos puede afectar es el cambio en el comportamiento de @@rowcount. En ocasiones, @@rowcount se utiliza en los triggers para obtener el número de registros afectados por el trigger pero en el caso de MERGE nos reportará el TOTAL de filas afectadas por cualquiera de las acciones con lo cual podemos tener errores en la lógica. Debemos tener en cuenta que este tipo de situaciones no son detectadas por el Upgrade Advisor (Asesor de actualizaciones) y es por ello que siempre deberemos obtener dicho conteo de registros a partir de las tablas inserted/deleted y no utilizando @@ROWCOUNT. Para comprobar esto basta con crear un trigger como este y comprobar las diferencias de resultados cuando utilizamos MERGE respecto al INSERT:
CREATE TRIGGER Product2_insert ON Production.Product2
FOR INSERT
AS
begin
declare @rowcount int = @@rowcount
declare @count int = (select count(*) from inserted)
print ‘Rowcount=’ + convert(varchar(10),@ROWCOUNT) + ‘, Inserted=’ + convert(varchar(10),@count)
end
go
— Resultado con MERGE: Rowcount=505, Inserted=1
— Resultado con INSERT: Rowcount=1, Inserted=1
Por otra parte, si tenemos definidos varios trigger AFTER/FOR se respetará la actual prioridad que especificáramos con sp_settriggerorder pero únicamente para los triggers del mismo tipo (INSERT, UPDATE, DELETE). Vamos, hasta aquí todo como toda la vida. Podemos determinar el primer trigger a ejecutar, el último y el resto se ejecutarán (sin ninguna garantía por parte de MS) probablemente por orden de creación. ¿Qué ocurre cuando tenemos un trigger que se desencadena por un conjunto de acciones? Este caso, que no es tan extraño, es utilizado frecuentemente para evitar duplicidad en la lógica, reducir el número de planes de ejecución por triggers, etc. Un ejemplo típico es el caso de una implementación de triggers de auditoría que podemos tener un único trigger que registre en la tabla de auditoría la operación correspondiente. El siguiente trigger muestra el valor de ROWCOUNT y el conteo de cada una de las tablas en los tres casos:
CREATE TRIGGER Product2_ALL ON Production.Product2
FOR INSERT,UPDATE,DELETE
AS
begin
declare @rowcount int = @@rowcount
declare @count int = (select count(*) from inserted)
declare @count2 int = (select count(*) from deleted)
print ‘Rowcount=’ + convert(varchar(10),@ROWCOUNT) + ‘, Inserted=’ + convert(varchar(10),@count)
+ ‘, Deleted=’ + convert(varchar(10),@count2)
end
go
— Resultado con MERGE: 1) Rowcount=505, Inserted=1, Deleted=0 –> INSERT
— 2) Rowcount=505, Inserted=10, Deleted=10 –> UPDATE
— 3) Rowcount=505, Inserted=0, Deleted=494 –> DELETE
— Resultado con 3 DML:
— INSERT –> Rowcount=1, Inserted=1, Deleted=0
— UPDATE –> Rowcount=10, Inserted=10, Deleted=10
— DELETE –> Rowcount=294, Inserted=0, Deleted=494
No olvidemos que no disponemos realmente de la posibilidad de controlar el orden de ejecución de los triggers que son de diferente tipo (INSERT, UPDATE y DELETE) pero si nos remitimos a las pruebas realizadas vemos que tanto en el caso de un Table Merge como en el caso de un Clustered Index Merge se disparan los triggers de inserción, luego los de actualización y luego los de borrado. Nada de esto está garantizado pero es así como se está comportando ahora mismo SQL Server 2008 y parece lógico que se siga así. Si nuestra lógica previa a sustituirla por un MERGE seguía este esquema de INSERT, UPDATE, DELETE lo más probable es que no nos encontramos ningún problema al “traducirla”. Ahora bien, si primero actualizamos filas para que luego las inserciones siguientes (y su ejecución correspondiente del trigger) funcionen correctamente entonces si podemos tener un problema. Se me ocurre que un ejemplo de este tipo podría ser si estamos manteniendo un control de jerarquía con los triggers en el cual primero actualicemos unas filas para “desasignar” un padre/hijo en la relación y luego insertemos el nuevo padre/hijo que con el trigger actuará sobre el valor previamente modificado.
Y por último, lo más divertido (y para nota si aún sigues leyendo J)
Otros problemas que podemos encontrarnos si utilizamos MERGE son problemas de concurrencia de la misma forma que podemos encontrarlos con el ejemplo con los 3 DML. A priori podría uno pensar que al tratarse de un único DML no sufriríamos dicho problema pero esto amigos únicamente nos garantiza la atomicidad, nada de serialización. ¿Cuál es el problema de fondo? Pues el comportamiento por defecto de MERGE implica adquirir por defecto bloqueos en el índice para actualizarlo (KEY UPDATE LOCK) pero se liberan ANTES de realizar la inserción y, por tanto, antes de obtener un bloqueo exclusivo. Por hablar en términos de hints sería como marcar con UPDLOCK una SELECT. Por tanto, si tenemos varios procesos MERGE que van a realizan inserciones simultáneamente (tras haber comprobado previamente que no existe el registro AÚN) existe la posibilidad que otro proceso se nos “cuele” justo en este punto pudiéndose generar un conflicto de clave primaria al ejecutar la inserción. ¿Divertido verdad? J ¿Cuál es la solución? El uso del hint HOLDLOCK para evitar que dicho KEY UPDATE LOCK se libere.
