w3schools - sql server update using merge

Canalizaciones y filtros a nivel de DBMS: división del flujo de salida MERGE

Question

Guión

Tenemos un proceso de importación de datos bastante estándar en el que cargamos una tabla de staging , luego la MERGE en una tabla de target .

Los nuevos requisitos (verde) implican la captura de un subconjunto de los datos importados en una tabla de queue separada para un procesamiento completamente no relacionado.

El reto"

(1) El subconjunto consiste en una selección de los registros: aquellos que se insertaron recientemente solo en la tabla de target .

(2) El subconjunto es una proyección de algunas de las columnas insertadas, pero también de al menos una columna que solo está presente en la fuente (la tabla de staging ).

(3) La declaración MERGE ya utiliza la cláusula OUTPUT..INTO estrictamente para registrar las $action s tomadas por MERGE , de modo que podamos PIVOT el resultado y COUNT el número de inserciones, actualizaciones y eliminaciones con fines estadísticos. Realmente no disfrutamos el almacenamiento en búfer de las acciones para todo el conjunto de datos así y preferiríamos agregar las sumas sobre la marcha. No hace falta decir que no queremos agregar más datos a esta tabla de OUTPUT .

(4) No queremos hacer el trabajo coincidente que el MERGE realiza una segunda vez por cualquier razón, incluso parcialmente. La tabla de target es realmente grande, no podemos indexar todo, y la operación generalmente es bastante costosa (minutos, no segundos).

(5) No estamos considerando realizar un recorrido de ida y vuelta desde el MERGE al cliente solo para que el cliente pueda enrutarlo a la queue enviándolo de inmediato. Los datos deben permanecer en el servidor.

(6) Deseamos evitar el almacenamiento en búfer de todo el conjunto de datos en el almacenamiento temporal entre la staging y la queue .

¿Cuál sería la mejor manera de hacerlo?

Fallos

(a) El requisito de poner en cola solo los registros insertados nos impide dirigir la tabla de la queue directamente en una cláusula OUTPUT..INTO de MERGE , ya que no permite ninguna cláusula WHERE . Podemos usar algunos trucos CASE para marcar los registros no deseados para su posterior eliminación de la queue sin procesar, pero esto parece una locura.

(b) Debido a que algunas columnas destinadas a la queue no aparecen en la tabla de target , no podemos simplemente agregar un disparador de inserción en la tabla de destino para cargar la queue . La "división del flujo de datos" tiene que ocurrir antes.

(c) Como ya usamos una cláusula OUTPUT..INTO en el MERGE , tampoco podemos agregar una segunda cláusula OUTPUT y anidar el MERGE en un INSERT..SELECT para cargar la cola tampoco. Esto es una vergüenza, porque se siente como una limitación completamente arbitraria para algo que funciona muy bien; SELECT filtra solo los registros con la $action que queremos ( INSERT ) e INSERT en la queue en una sola declaración. Por lo tanto, el DBMS puede, en teoría, evitar el almacenamiento en búfer de todo el conjunto de datos y simplemente transmitirlo a la queue . (Nota: no buscamos y es probable que no haya optimizado el plan de esta manera).

Situación

Sentimos que hemos agotado nuestras opciones, pero decidimos recurrir a la mente para estar seguros. Todo lo que podemos hacer es:

(S1) Cree una VIEW de la tabla de target que también contenga columnas que puedan contener nulos para los datos destinados solo a la queue , y SELECT instrucción SELECT defina como NULL . Luego, configure los activadores INSTEAD OF que llenan la tabla de target y la queue apropiada. Finalmente, MERGE el MERGE para apuntar a la vista. Esto funciona, pero no somos fanáticos de la construcción, definitivamente se ve complicado.

(S2) Renunciar, almacenar en búfer todo el conjunto de datos en una tabla temporal utilizando otro MERGE..OUTPUT . Después de la MERGE , copie inmediatamente los datos (¡otra vez!) De la tabla temporal en la queue .

Answer 1

¿Ha considerado deshacerse de la fusión y simplemente hacer una inserción donde no existe y una actualización? A continuación, puede utilizar la cláusula de salida de la inserción para rellenar su tabla de cola.

Answer 2

A menos que me falte algo, un simple comando de inserción debe cumplir con todos sus requisitos.

insert into queue
(foo, baz)
select staging.foo, staging.baz
from staging join target on staging.foo = target.boo
where whatever

Esto sucedería después de la fusión en el objetivo.

Solo para registros nuevos, haga esto antes de la fusión

insert into queue
(foo, baz)
select staging.foo, staging.baz
from staging left join target on staging.foo = target.boo
where target.foo = null

Answer 3

Considere los siguientes dos enfoques para resolver el problema:

• Combine los datos en el destino y la salida insertada en la cola en una sola declaración, y resuma las estadísticas en el activador creado en el destino. El identificador de lote se puede pasar al activador a través de una tabla temporal.
• Combine los datos en el destino y la salida insertada en la cola en una sola declaración, y resuma las estadísticas inmediatamente después de la combinación, utilizando las capacidades de seguimiento de cambios incorporadas, en lugar de hacerlo en el activador.

Enfoque 1 (fusionar datos y recopilar estadísticas en el activador):

Configuración de datos de muestra (índices y restricciones omitidos por simplicidad):

create table staging (foo varchar(10), bar varchar(10), baz varchar(10));
create table target (foo varchar(10), bar varchar(10));
create table queue (foo varchar(10), baz varchar(10));
create table stats (batchID int, inserted bigint, updated bigint, deleted bigint);

insert into staging values
    (''A'', ''AA'', ''AAA'')
    ,(''B'', ''BB'', ''BBB'')
    ,(''C'', ''CC'', ''CCC'')
    ;

insert into target values
    (''A'', ''A_'')
    ,(''B'', ''B?'')
    ,(''E'', ''EE'')
    ;

Activar para recopilar estadísticas insertadas / actualizadas / eliminadas:

create trigger target_onChange
on target
after delete, update, insert
as
begin
    set nocount on;

    if object_id(''tempdb..#targetMergeBatch'') is NULL
        return;

    declare @batchID int;
    select @batchID = batchID from #targetMergeBatch;

    merge into stats t
    using (
        select
            batchID = @batchID,
            cntIns = count_big(case when i.foo is not NULL and d.foo is NULL then 1 end),
            cntUpd = count_big(case when i.foo is not NULL and d.foo is not NULL then 1 end),
            cntDel = count_big(case when i.foo is NULL and d.foo is not NULL then 1 end)
        from inserted i
            full join deleted d on d.foo = i.foo
    ) s
    on t.batchID = s.batchID
    when matched then
        update
        set
            t.inserted = t.inserted + s.cntIns,
            t.updated = t.updated + s.cntUpd,
            t.deleted = t.deleted + s.cntDel
    when not matched then
        insert (batchID, inserted, updated, deleted)
        values (s.batchID, s.cntIns, s.cntUpd, cntDel);

end

Fusionar declaraciones:

declare @batchID int;
set @batchID = 1;-- or select @batchID = batchID from ...;

create table #targetMergeBatch (batchID int);
insert into #targetMergeBatch (batchID) values (@batchID);

insert into queue (foo, baz)
select foo, baz
from
(
    merge into target t
    using staging s
    on t.foo = s.foo
    when matched then
        update
        set t.bar = s.bar
    when not matched then
        insert (foo, bar)
        values (s.foo, s.bar)
    when not matched by source then
        delete
    output $action, inserted.foo, s.baz
) m(act, foo, baz)
where act = ''INSERT''
    ;

drop table #targetMergeBatch

Compruebe los resultados:

select * from target;
select * from queue;
select * from stats;

Objetivo:

foo        bar
---------- ----------
A          AA
B          BB
C          CC

Cola:

foo        baz
---------- ----------
C          CCC

Estadísticas:

batchID  inserted   updated   deleted
-------- ---------- --------- ---------
1        1          2         1

Enfoque 2 (recopilar estadísticas, utilizando capacidades de seguimiento de cambios):

La configuración de los datos de muestra es la misma que en el caso anterior (simplemente suelte todo, incluido el desencadenador y vuelva a crear tablas desde cero), excepto que en este caso debemos tener PK en el objetivo para que la muestra funcione:

create table target (foo varchar(10) primary key, bar varchar(10));

Habilitar el seguimiento de cambios en la base de datos:

alter database Test
    set change_tracking = on

Habilitar el seguimiento de cambios en la tabla de destino:

alter table target
    enable change_tracking

Combine datos y capture estadísticas inmediatamente después de eso, filtrando por el contexto de cambio para contar solo las filas afectadas por la combinación:

begin transaction;
declare @batchID int, @chVersion bigint, @chContext varbinary(128);
set @batchID = 1;-- or select @batchID = batchID from ...;
SET @chVersion = change_tracking_current_version();
set @chContext = newid();

with change_tracking_context(@chContext)
insert into queue (foo, baz)
select foo, baz
from
(
    merge into target t
    using staging s
    on t.foo = s.foo
    when matched then
        update
        set t.bar = s.bar
    when not matched then
        insert (foo, bar)
        values (s.foo, s.bar)
    when not matched by source then
        delete
    output $action, inserted.foo, s.baz
) m(act, foo, baz)
where act = ''INSERT''
    ;

with ch(foo, op) as (
    select foo, sys_change_operation
    from changetable(changes target, @chVersion) ct
    where sys_change_context = @chContext
)
insert into stats (batchID, inserted, updated, deleted)
select @batchID, [I], [U], [D]
from ch
    pivot(count_big(foo) for op in ([I], [U], [D])) pvt
    ;

commit transaction;

Compruebe los resultados:

select * from target;
select * from queue;
select * from stats;

Son los mismos que en la muestra anterior.

Objetivo:

foo        bar
---------- ----------
A          AA
B          BB
C          CC

Cola:

foo        baz
---------- ----------
C          CCC

Estadísticas:

batchID  inserted   updated   deleted
-------- ---------- --------- ---------
1        1          2         1

Answer 4

Importar a través de una tabla de etapas puede ser más eficiente con el procesamiento secuencial en lugar de orientado a conjuntos. Consideraría volver a escribir MERGE en un procedimiento almacenado con la exploración del cursor. Luego, para cada registro puede tener tantos resultados como desee, más los recuentos sin pivote, a un costo total de una exploración de la tabla de staging .

El procedimiento almacenado también puede proporcionar oportunidades para dividir el procesamiento en transacciones más pequeñas, mientras que los desencadenantes de conjuntos de datos más grandes pueden provocar un desbordamiento del registro de transacciones.

Answer 5

Mi entendimiento es que el principal obstáculo es la limitación de la cláusula OUTPUT en SQL Server. Permite una OUTPUT INTO table y / o una OUTPUT que devuelve el conjunto de resultados a la persona que llama.

Desea guardar el resultado de la sentencia MERGE de dos maneras diferentes:

• todas las filas que fueron afectadas por MERGE para recopilar estadísticas
• solo filas insertadas para la queue

Variante simple

Yo usaría su solución S2. Al menos para empezar. Es fácil de entender y mantener, y debe ser bastante eficiente, ya que la operación con mayor uso de recursos ( MERGE en Target en sí misma se realizará una sola vez). Hay una segunda variante a continuación y sería interesante comparar su rendimiento en datos reales.

Asi que:

• Use OUTPUT INTO @TempTable en el MERGE
• INSERT todas las filas de @TempTable en Stats o agregue antes de insertar. Si todo lo que necesita son estadísticas agregadas, tiene sentido agregar los resultados de este lote y fusionarlos en las Stats finales en lugar de copiar todas las filas.
• INSERT en la Queue solo filas "insertadas" de @TempTable .

Tomaré datos de muestra de la respuesta por @ i-one.

Esquema

-- I''ll return to commented lines later

CREATE TABLE [dbo].[TestTarget](
    -- [ID] [int] IDENTITY(1,1) NOT NULL,
    [foo] [varchar](10) NULL,
    [bar] [varchar](10) NULL
);

CREATE TABLE [dbo].[TestStaging](
    [foo] [varchar](10) NULL,
    [bar] [varchar](10) NULL,
    [baz] [varchar](10) NULL
);

CREATE TABLE [dbo].[TestStats](
    [MergeAction] [nvarchar](10) NOT NULL
);

CREATE TABLE [dbo].[TestQueue](
    -- [TargetID] [int] NOT NULL,
    [foo] [varchar](10) NULL,
    [baz] [varchar](10) NULL
);

Data de muestra

TRUNCATE TABLE [dbo].[TestTarget];
TRUNCATE TABLE [dbo].[TestStaging];
TRUNCATE TABLE [dbo].[TestStats];
TRUNCATE TABLE [dbo].[TestQueue];

INSERT INTO [dbo].[TestStaging]
    ([foo]
    ,[bar]
    ,[baz])
VALUES
    (''A'', ''AA'', ''AAA''),
    (''B'', ''BB'', ''BBB''),
    (''C'', ''CC'', ''CCC'');

INSERT INTO [dbo].[TestTarget]
    ([foo]
    ,[bar])
VALUES
    (''A'', ''A_''),
    (''B'', ''B?'');

Unir

DECLARE @TempTable TABLE (
    MergeAction nvarchar(10) NOT NULL,
    foo varchar(10) NULL,
    baz varchar(10) NULL);

MERGE INTO TestTarget AS Dst
USING TestStaging AS Src
ON Dst.foo = Src.foo
WHEN MATCHED THEN
UPDATE SET
    Dst.bar = Src.bar
WHEN NOT MATCHED BY TARGET THEN
INSERT (foo, bar)
VALUES (Src.foo, Src.bar)
OUTPUT $action AS MergeAction, inserted.foo, Src.baz
INTO @TempTable(MergeAction, foo, baz)
;

INSERT INTO [dbo].[TestStats] (MergeAction)
SELECT T.MergeAction
FROM @TempTable AS T;

INSERT INTO [dbo].[TestQueue]
    ([foo]
    ,[baz])
SELECT
    T.foo
    ,T.baz
FROM @TempTable AS T
WHERE T.MergeAction = ''INSERT''
;

SELECT * FROM [dbo].[TestTarget];
SELECT * FROM [dbo].[TestStats];
SELECT * FROM [dbo].[TestQueue];

Resultado

TestTarget
+-----+-----+
| foo | bar |
+-----+-----+
| A   | AA  |
| B   | BB  |
| C   | CC  |
+-----+-----+

TestStats
+-------------+
| MergeAction |
+-------------+
| INSERT      |
| UPDATE      |
| UPDATE      |
+-------------+

TestQueue
+-----+-----+
| foo | baz |
+-----+-----+
| C   | CCC |
+-----+-----+

Segunda variante

Probado en SQL Server 2014 Express.

OUTPUT cláusula OUTPUT puede enviar su conjunto de resultados a una tabla y al llamante. Por lo tanto, OUTPUT INTO puede ir directamente a las Stats y si MERGE declaración MERGE en un procedimiento almacenado, entonces podemos usar INSERT ... EXEC en la Queue .

Si examina el plan de ejecución, verá que INSERT ... EXEC crea una tabla temporal detrás de la escena de todos modos (vea también Los costos ocultos de INSERT EXEC por Adam Machanic), así que espero que el rendimiento general sea similar a la primera variante cuando se crea explícitamente la tabla temporal.

Un problema más que resolver: la tabla de Queue solo debe tener filas "insertadas", no todas las filas afectadas. Para lograrlo, puede usar un disparador en la tabla de Queue para descartar filas que no sean "insertadas". Una posibilidad más es definir un índice único con IGNORE_DUP_KEY = ON y preparar los datos de tal manera que las filas "no insertadas" violen el índice único y no se inserten en la tabla.

Entonces, TargetID una columna ID IDENTITY a la tabla de Target y TargetID una columna TargetID a la tabla de Queue . (Descoméntalos en el script de arriba). Además, agregaré un índice a la tabla Queue :

CREATE UNIQUE NONCLUSTERED INDEX [IX_TargetID] ON [dbo].[TestQueue]
(
    [TargetID] ASC
) WITH (
PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, 
IGNORE_DUP_KEY = ON, 
DROP_EXISTING = OFF, 
ONLINE = OFF, 
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)

La parte importante es UNIQUE e IGNORE_DUP_KEY = ON .

Aquí está el procedimiento almacenado para el MERGE :

CREATE PROCEDURE [dbo].[TestMerge]
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    MERGE INTO dbo.TestTarget AS Dst
    USING dbo.TestStaging AS Src
    ON Dst.foo = Src.foo
    WHEN MATCHED THEN
    UPDATE SET
        Dst.bar = Src.bar
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (foo, bar)
    VALUES (Src.foo, Src.bar)
    OUTPUT $action INTO dbo.TestStats(MergeAction)
    OUTPUT CASE WHEN $action = ''INSERT'' THEN inserted.ID ELSE 0 END AS TargetID, 
    inserted.foo,
    Src.baz
    ;

END

Uso

TRUNCATE TABLE [dbo].[TestTarget];
TRUNCATE TABLE [dbo].[TestStaging];
TRUNCATE TABLE [dbo].[TestStats];
TRUNCATE TABLE [dbo].[TestQueue];

-- Make sure that `Queue` has one special row with TargetID=0 in advance.
INSERT INTO [dbo].[TestQueue]
    ([TargetID]
    ,[foo]
    ,[baz])
VALUES
    (0
    ,NULL
    ,NULL);

INSERT INTO [dbo].[TestStaging]
    ([foo]
    ,[bar]
    ,[baz])
VALUES
    (''A'', ''AA'', ''AAA''),
    (''B'', ''BB'', ''BBB''),
    (''C'', ''CC'', ''CCC'');

INSERT INTO [dbo].[TestTarget]
    ([foo]
    ,[bar])
VALUES
    (''A'', ''A_''),
    (''B'', ''B?'');

INSERT INTO [dbo].[TestQueue]
EXEC [dbo].[TestMerge];

SELECT * FROM [dbo].[TestTarget];
SELECT * FROM [dbo].[TestStats];
SELECT * FROM [dbo].[TestQueue];

Resultado

TestTarget
+----+-----+-----+
| ID | foo | bar |
+----+-----+-----+
|  1 | A   | AA  |
|  2 | B   | BB  |
|  3 | C   | CC  |
+----+-----+-----+

TestStats
+-------------+
| MergeAction |
+-------------+
| INSERT      |
| UPDATE      |
| UPDATE      |
+-------------+

TestQueue
+----------+------+------+
| TargetID | foo  | baz  |
+----------+------+------+
|        0 | NULL | NULL |
|        3 | C    | CCC  |
+----------+------+------+

Habrá un mensaje extra durante INSERT ... EXEC :

Duplicate key was ignored.

si MERGE actualiza algunas filas. Este mensaje de advertencia se envía cuando el índice único descarta algunas filas durante INSERT debido a IGNORE_DUP_KEY = ON .

Aparecerá un mensaje de advertencia cuando se inserten valores de clave duplicados en un índice único. Solo las filas que violan la restricción de unicidad fallarán.

Answer 6

Sugiero que la extracción de las estadísticas se realice mediante el uso de tres activadores independientes AFTER INSERT / DELETE / UPDATE largo de las líneas de:

create trigger dbo.insert_trigger_target
on [dbo].[target]
after insert
as
insert into dbo.[stats] ([action],[count])
select ''insert'', count(1)
from inserted;
go

create trigger dbo.update_trigger_target
on [dbo].[target]
after update
as
insert into dbo.[stats] ([action],[count])
select ''update'', count(1) from inserted -- or deleted == after / before image, count will be the same
go

create trigger dbo.delete_trigger_target
on [dbo].[target]
after delete
as
insert into dbo.[stats] ([action],[count])
select ''delete'', count(1) from deleted
go

Si necesita más contexto, ponga algo en CONTEXT_INFO y CONTEXT_INFO de los disparadores.

Ahora, voy a afirmar que los desencadenantes AFTER no son tan caros, pero tendrá que probar eso para estar seguro.

Habiendo tratado con eso, tendrá la libertad de usar la cláusula OUTPUT ( NO OUTPUT INTO ) en el MERGE y luego usar ese anidado dentro de una selección para subcontratar los datos que desea ingresar en la tabla de la queue .

Justificación

Debido a la necesidad de acceder a las columnas tanto del almacenamiento staging como del target para generar los datos para la queue , esto TIENE que hacerse utilizando la opción OUTPUT en MERGE , ya que nada más tiene acceso a "ambos lados".

Entonces, si hemos secuestrado la cláusula OUTPUT para la queue , ¿cómo podemos volver a trabajar esa funcionalidad? Creo que los desencadenadores AFTER funcionarán, dados los requisitos para las estadísticas que ha descrito. De hecho, las estadísticas podrían ser bastante complejas si fuera necesario, dadas las imágenes disponibles. Estoy afirmando que los activadores AFTER no son "caros" ya que los datos de antes y después siempre deben estar disponibles para que una transacción pueda COMPROMETIRSE O RODARSE. Sí, los datos deben analizarse (incluso para obtener el recuento) pero eso no parece ser demasiado costoso.

En mi propio análisis, el análisis agregó aproximadamente 5% al ​​costo base del plan de ejecución

¿Suena como una solución?