none
eliminar duplicados entre dos tablas RRS feed

  • Pregunta

  • Cómo sería la mejor forma (en cuanto a tiempo y memoria) para eliminar duplicados entre dos tablas?

    Tengo dos tablas: Tabla1 y Tabla2, que tienen que estar sincronizadas (nuevos registros de Tabla1 deben ir a Tabla2), pero obviamente no deben ir dos veces.

    Actualmente tenemos un proceso que corre una vez por día, que se fija registros en el último mes de Tabla1 y los lleva a Tabla2, si no existen. Tabla2 tiene un campo que es el ID de Tabla1.


    Lo que hacemos básicamente es:
    1) Buscar todos los registros de Tabla1 en determinado periodo de tiempo
    2) Eliminamos los que ya existen en Tabla2
    3) Insertamos los restantes

    Si bien tenemos varios approachs para el paso 2, cuál sería el mejor, ya que estamos teniendo varios timeouts (Tabla2 tiene varios millones de registros):
    1) Hacer un LEFT JOIN (Tabla1.Id = Tabla2.Tabla1ID) y eliminar cuyos Tabla2 IS NULL
    2) Hacer un NOT IN (SELECT Tabla1ID FROM Tabla2)
    3) Guardar en tabla temporal y hacer un DELETE joineando
    4) No lo planteamos, pero capaz un MERGE funcione también

    Cuál sería la mejor forma? Tal vez exista otra forma que las que escribí

    viernes, 23 de septiembre de 2022 18:06

Todas las respuestas

  • Hola, algunas formas de implementar lo que comentas con un ejemplo práctico

    Entiendo que la mejor manera sería la que tenga menos costo en el plan de ejecución

    Insert de una tabla a otra sin duplicar registros


    Votar y marcar respuestas es agradecer.
    Saludos.
    Lima-Perú

    viernes, 23 de septiembre de 2022 22:49
  • Hola Miriam Pasión:

    Cómo sería la mejor forma (en cuanto a tiempo y memoria) para eliminar duplicados entre dos tablas?

    No tener que eliminarlos.

    Eliminar es muy costoso para el porque tiene que anotar las sentencias en el transactión log y luego marcar en los datos que están disponibles las páginas para otros registros en el contexto de la transacción que está ocupando el delete.

    Lo lógico es tener una restricción unique, para que no haya duplicados.

    Como no pones el escenario real, pues es muy difícil saber si esta aproximación es posible, porque no indicas lo que es un duplicado.

    Cómo sería la mejor forma

    Sin un escenario real, es imposible acercarse a darte una opción y saber cuál es la buena. Hay que saber la definición de la tabla y los índices que tiene....etc.

    4) No lo planteamos, pero capaz un MERGE funcione también

    Pues no veo el motivo. Es una opción muy, pero que muy interesante, porque aunque puede haber otras formas en determinados escenarios de hacer un upsert, que conlleven un pelín menos de procesamiento, es bastante probable, que mejore infinitamente lo que haces actualmente y no tienes que preocuparte de nada. Te olvidas de los distinct, de los agrupamientos, etc..... 

    Merge

    https://javifer2.wordpress.com/2019/10/04/merge-upsert-insertar-o-actualizar-en-una-sola-sentencia/

    A priori lo que cuentas que haces y dices, parece más bien un registro de históricos, donde vás pasando la información de tabla1 con pocos registros a tabla2 con muchos registros.

    Si es esto y tienes un sql server 2016, a lo mejor también te puede interesar 

    Versionado de tabla.

    https://javifer2.wordpress.com/2019/10/19/versionado-de-tabla-sql-2016/

    sábado, 24 de septiembre de 2022 5:34
  • Tengo dos tablas: Tabla1 y Tabla2, que tienen que estar sincronizadas (nuevos registros de Tabla1 deben ir a Tabla2), pero obviamente no deben ir dos veces.

    Yo te diria que usaras....

    ;with dos as (

    select  id from tabla1

    except

    select  id from tabla2)

    insert into tabla2(campos)

    select  campos de a  from tabla1 as a inner join  dos as b

    on b.id = a.id ;



    • Editado Hacerdan lunes, 26 de septiembre de 2022 19:46 anexar pregunta
    lunes, 26 de septiembre de 2022 19:45
  • Tengo dos tablas: Tabla1 y Tabla2, que tienen que estar sincronizadas (nuevos registros de Tabla1 deben ir a Tabla2), pero obviamente no deben ir dos veces.

    Yo te diria que usaras....

    ;with dos as (

    select  id from tabla1

    except

    select  id from tabla2)

    insert into tabla2(campos)

    select  campos de a  from tabla1 as a inner join  dos as b

    on b.id = a.id ;



    Hola Hacerdan. ¿ Existirá algún problema de eficiencia si sustituyéramos el EXCEPT por: ?

    WHERE Id NOT IN (Select Id FROM Tabla2)

    o

    ;with dos as (
    select  A.* from tabla1 A left join tabla2 B on A.id = B.id
    where B.id is null
    )
    insert into tabla2 (campos)  select Campos from dos

    gracias

    • Editado Raimundo Ferrer miércoles, 28 de septiembre de 2022 8:13 Mejorar la redacción
    miércoles, 28 de septiembre de 2022 8:02
  • Hola Raimundo Ferrer:

    Lo primero que tienes que tener en cuenta, es que la pregunta puede ser equivocada. 

    ¿ Existirá algún problema de eficiencia si sustituyéramos el EXCEPT por: ?

    La respuesta es simple. Es posible, pero no se sabe del todo.

    En TSQL, la sentencia Select es declarativa. Sabes lo que le pides, pero el lo va a ejecutar como le parezca más eficiente, en función de un montón de causísticas.

    La primera y más clara, en función de lo que le pidas, de si lo puede obtener de algún índice o no. Porque el plan a montar, puede variar pudiendo extraer los datos de un sitio u otro. La segunda de los recursos disponibles en ese momento.

    Pero como no hay nada mejor que verlo para creerlo.

    Escenario: 2 tablas con una columna sin clave primaria. La tabla1 tiene 1.000.000 filas. La tabla2 tiene 142.857

    drop table if exists dbo.tabla1;
    drop table if exists dbo.tabla2;
    Create table dbo.tabla1 (id int);
    Create table dbo.tabla2 (id int);
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla1
    select * from nums;
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla2 (id)
    select * from nums
    where nums.rn%7=0;-- solo algunas filas.
    go
    select COUNT(*) from dbo.tabla1;
    Select COUNT(*) from dbo.tabla2;
    go

    Ahora consulta 1. La ejecutamos mostrando el plan de ejecución en un Sql Server 2019 con un modo de compatibilidad 150. Sin hacer el insert.

    with dos as (
    select  id from tabla1
    except
    select  id from tabla2)
    --insert into tabla2(campos)
    select a.id  from tabla1 as a inner join  dos as b
    on b.id = a.id ;


    Y este es el plan que ha montado.

    Segunda consulta.

    ;with dos as (
    select  A.* from tabla1 A left join tabla2 B on A.id = B.id
    where B.id is null
    )
      select id from dos


    Como podía parecer, lo hace de un modo más eficiente.

    PERO.....

    Mismo escenario, pero ahora con las columnas como primary key.

    drop table if exists dbo.tabla3;
    drop table if exists dbo.tabla4;
    Create table dbo.tabla3 (id int Primary key);
    Create table dbo.tabla4 (id int Primary key);
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla3
    select * from nums;
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla4 (id)
    select * from nums
    where nums.rn%7=0;-- solo algunas filas.
    go
    select COUNT(*) from dbo.tabla3;
    Select COUNT(*) from dbo.tabla4;
    go
    
    

    Ahora hacemos la misma query 1, pero para las tablas 3 y 4.

    with dos as (
    select  id from dbo.tabla3
    except
    select  id from dbo.tabla4
    )
    
    --insert into tabla2(campos)
    select a.id  from tabla3 as a inner join  dos as b
    on b.id = a.id ;

    Y el plan de ejecución es similar, ya tiene diferencias.

    Consulta 4. Igual que 2 pero para las tablas 3 y 4

    ;with dos as (
    select  A.* from tabla3 A left join tabla4 B on A.id = B.id
    where B.id is null
    )
      select id from dos

    Y también es diferente. Mejor que las opciones 1 y 3, y como no diferente a 2.

    En resumidas cuentas, no solo es lo que uno cree que el va a hacer, porque normalmente Not In o Not Exists, los suele reconducir al operador Anti Semi Join. Pero lo más importante es que es declarativo.

    PD. La opción que muestras es muy probablemente en todos los casos más eficiente. Porque en la consulta de exists, se está usando dos veces la tabla1. Pero si se hiciese directamente insert into Select * from tabla1 except select * from tabla2, entonces es probable, que fuera igual o muy similar.

    miércoles, 28 de septiembre de 2022 17:03
  • Hola Raimundo Ferrer:

    Lo primero que tienes que tener en cuenta, es que la pregunta puede ser equivocada. 

    ¿ Existirá algún problema de eficiencia si sustituyéramos el EXCEPT por: ?

    La respuesta es simple. Es posible, pero no se sabe del todo.

    En TSQL, la sentencia Select es declarativa. Sabes lo que le pides, pero el lo va a ejecutar como le parezca más eficiente, en función de un montón de causísticas.

    La primera y más clara, en función de lo que le pidas, de si lo puede obtener de algún índice o no. Porque el plan a montar, puede variar pudiendo extraer los datos de un sitio u otro. La segunda de los recursos disponibles en ese momento.

    Pero como no hay nada mejor que verlo para creerlo.

    Escenario: 2 tablas con una columna sin clave primaria. La tabla1 tiene 1.000.000 filas. La tabla2 tiene 142.857

    drop table if exists dbo.tabla1;
    drop table if exists dbo.tabla2;
    Create table dbo.tabla1 (id int);
    Create table dbo.tabla2 (id int);
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla1
    select * from nums;
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla2 (id)
    select * from nums
    where nums.rn%7=0;-- solo algunas filas.
    go
    select COUNT(*) from dbo.tabla1;
    Select COUNT(*) from dbo.tabla2;
    go

    Ahora consulta 1. La ejecutamos mostrando el plan de ejecución en un Sql Server 2019 con un modo de compatibilidad 150. Sin hacer el insert.

    with dos as (
    select  id from tabla1
    except
    select  id from tabla2)
    --insert into tabla2(campos)
    select a.id  from tabla1 as a inner join  dos as b
    on b.id = a.id ;


    Y este es el plan que ha montado.

    Segunda consulta.

    ;with dos as (
    select  A.* from tabla1 A left join tabla2 B on A.id = B.id
    where B.id is null
    )
      select id from dos


    Como podía parecer, lo hace de un modo más eficiente.

    PERO.....

    Mismo escenario, pero ahora con las columnas como primary key.

    drop table if exists dbo.tabla3;
    drop table if exists dbo.tabla4;
    Create table dbo.tabla3 (id int Primary key);
    Create table dbo.tabla4 (id int Primary key);
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla3
    select * from nums;
    go
    With r as (
    Select t.n 
    /* 10 */
    From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))T(n)
    ) , r2 as (
    	select r.n from r 
    	cross join r as a/*       100*/
    	cross join r as b/*     1.000*/
    	cross join r as c/*    10.000*/
    	cross join r as d/*   100.000*/
    	cross join r as e/* 1.000.000*/
    ), nums as (
    Select ROW_NUMBER() over(order by (select null)) rn
    from r2 
    )
    Insert into dbo.tabla4 (id)
    select * from nums
    where nums.rn%7=0;-- solo algunas filas.
    go
    select COUNT(*) from dbo.tabla3;
    Select COUNT(*) from dbo.tabla4;
    go
    

    Ahora hacemos la misma query 1, pero para las tablas 3 y 4.

    with dos as (
    select  id from dbo.tabla3
    except
    select  id from dbo.tabla4
    )
    
    --insert into tabla2(campos)
    select a.id  from tabla3 as a inner join  dos as b
    on b.id = a.id ;

    Y el plan de ejecución es similar, ya tiene diferencias.

    Consulta 4. Igual que 2 pero para las tablas 3 y 4

    ;with dos as (
    select  A.* from tabla3 A left join tabla4 B on A.id = B.id
    where B.id is null
    )
      select id from dos

    Y también es diferente. Mejor que las opciones 1 y 3, y como no diferente a 2.

    En resumidas cuentas, no solo es lo que uno cree que el va a hacer, porque normalmente Not In o Not Exists, los suele reconducir al operador Anti Semi Join. Pero lo más importante es que es declarativo.

    PD. La opción que muestras es muy probablemente en todos los casos más eficiente. Porque en la consulta de exists, se está usando dos veces la tabla1. Pero si se hiciese directamente insert into Select * from tabla1 except select * from tabla2, entonces es probable, que fuera igual o muy similar.

    Hola. Muchas gracias Javi por tus explicaciones, me han aclarado perfectamente mis dudas, saludos 
    jueves, 29 de septiembre de 2022 6:47