locked
rendimiento en borrado de filas RRS feed

  • Pregunta

  • Hola,

       Tengo un proceso de migración que trata muchos registros. Para cada registro, comprueba si ya está. Si está, procede a eliminar el registro de esa tabla principal y de todos los registros de otras tablas relacionados. El proceso es el siguiente:
         - Se deshabilitan constraints
         - Se hace un borrado de cada registro (en cada tabla) relacionada por clave primaria.
         - Se habilitan constraints.

    Sin embargo, el proceso se demora muchísimo.  Lo que multiplicado por miles de registros, da lugar a muchas horas.

    En concreto, cada registro tarda unos 5 segundos cuando el número de entradas para las distintas tablas puede ser:
           - Tabla1= 1 registro
           - Tabla2 = 1 registro
           - Tabla3=1 registro
           - Tabla4= 500 registros
           - Tabla5 = 500 registros (incluye un campo blob que no sé si influye)
           - Tabla6 = 20 registros
           - Tabla7 = 10 registros
           - Tabla8 = 3 registros
           - Tabla9 = 3 registros
           - Tabla10= 2 registros
           - Tabla11= 3 registros
           - Tabla12 = 2 registros
           - Tabla13 = 4 registros

    No se me ocurre que más mirar, pues ya he visto que todo va por clave primaria y  he deshabilitado constraint porque vimos que mejoraba un poco el rendimiento.

    Hemos visto también que, a medida que trata registros, los tiempos va empeorando y tarda un segundo más (más o menos) en borrar un registro y sus relacionados .

    Si alguien me puede indicar alguna cosa más que se pueda mirar para bajar un poco estos tiempos se lo agradecería.

    Un saludo y gracias de antemano.
    lunes, 19 de octubre de 2009 16:44

Respuestas

  • Hola.

    Dado que esto borra un único registro, es seguro que existe un bucle para ir verificando y en su caso borrando los registros de cada tabla, con otro dentro para borrar en cada una de las tablas hijas. Ese mecanismo, se haga con un cursor o con llamadas iteradas, tienen bastantes inconvenientes desde el punto de vista del rendimiento.

    La clave está en pensar en conjuntos de registros y no es registros individuales. Así, puedes realizar una sola llamada que realice todos los borrados de cada tabla en una sola sentencia delete. Por otra parte, usar sql dinámico no ayuda en nada, ya que impides que se pueda reutilizar el plan de ejecución de cada sentencia.

    Yo trataría de construir una única sentencia para cada tabla y que sean sentencias fijas. Primero, insertas todos los IDPadre a borrar en una tabla auxiliar (puede ser temporal o no), mediante el mecanismo que elijas. Luego, preparas una sentencia más o menos así:

    delete T 
    from TablaHija T inner join TablaReferencia R on T.idPadre = R.idPadre inner join TablaConIdPadre P on T.IdPadre = P.IdPadre

    Es decir, eliminas de una tacada todo lo que esté en la tabla de referencia y sea uno de los Ids a borrar. Si no lo consigues, nos dices.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    martes, 20 de octubre de 2009 9:02
    Moderador

Todas las respuestas


  •      - Se hace un borrado de cada registro (en cada tabla) relacionada por clave primaria.

    Usted dice "Para cada registro, comprueba si ya está. Si está, procede a eliminar el registro de esa tabla principal.", cómo lo hace, con un cursor?
    Puede usted brindar los detalles de cómo se hace el borrado de cada registro en las tablas relacionadas?

    Tal vez si usted muestra el código T-SQL que programó, podamos ayudarlo mejor.

    Gustavo Larriera Sosa [ascii164.blogspot.com] /*Este mensaje se proporciona tal como es, sin garantías de ninguna clase.*/
    lunes, 19 de octubre de 2009 16:48
    Moderador
  • Hola,

    Para ver si ya está, lo compruebo por una clave única que se busca en una tabla creada sólo para este propósito (se busca por la clave primaria de esta tabla que es una especia de caché de búsqueda). Es decir, se busca en dicha tabla. Si está, hay que borrar la entrada y todos sus relacionados.

    Un ejempo del el borrado de una de las tablas hijas sería:

       set @sError='Tabla tabla1'
        set @SQLString=N'DELETE FROM '+@BDRemota+'.'+@BDUsuario+'.'+
        'Tabla1 WHERE IDPadre=@p1'
       
        set @ParamDefinition=N'@p1    int'

        exec @ErrorExec=sp_executesql @SQLString,@ParamDefinition,
        @p1=@IDPadre

        set @Retorno=@@error
        if (@Retorno<>0) return @Retorno
        if (@ErrorExec<>0) return @ErrorExec


    El resto de tablas se hace igual. En algunos casos, idPadre es el primer campo de la clave primaria. En otros, hay un índice por IDPadre.

    Se hace dinámico, porque la base de datos y el usuario se pasan por parámetros.

    Un saludo y gracias de antemano.
    martes, 20 de octubre de 2009 8:32
  • Hola.

    Dado que esto borra un único registro, es seguro que existe un bucle para ir verificando y en su caso borrando los registros de cada tabla, con otro dentro para borrar en cada una de las tablas hijas. Ese mecanismo, se haga con un cursor o con llamadas iteradas, tienen bastantes inconvenientes desde el punto de vista del rendimiento.

    La clave está en pensar en conjuntos de registros y no es registros individuales. Así, puedes realizar una sola llamada que realice todos los borrados de cada tabla en una sola sentencia delete. Por otra parte, usar sql dinámico no ayuda en nada, ya que impides que se pueda reutilizar el plan de ejecución de cada sentencia.

    Yo trataría de construir una única sentencia para cada tabla y que sean sentencias fijas. Primero, insertas todos los IDPadre a borrar en una tabla auxiliar (puede ser temporal o no), mediante el mecanismo que elijas. Luego, preparas una sentencia más o menos así:

    delete T 
    from TablaHija T inner join TablaReferencia R on T.idPadre = R.idPadre inner join TablaConIdPadre P on T.IdPadre = P.IdPadre

    Es decir, eliminas de una tacada todo lo que esté en la tabla de referencia y sea uno de los Ids a borrar. Si no lo consigues, nos dices.


    Alberto López Grande (Visita mi blog en http://qwalgrande.blogspot.es/)
    martes, 20 de octubre de 2009 9:02
    Moderador
  • Hola,

    He realizado lo que me indicas quitando todos los cursores y pasando las consultas a estáticas y la verdad es que la mejora fue mínima. Ese no parecía el problema en este caso. Aunque la verdad es que da mucha claridad al código y seguro que en consultas más grandes mejorará el rendimiento.

    Al final, el problema venía con dos foreign key de una determinada tabla, que no habíamos deshabilitado. Esas tablas estaban totalmente vacías pues todavía no se usan. Por ello no consideramos necesario quitarlas. Sin embargo, parece que afectan bastante. Al quitarlas antes del proceso de borrado, los tiempos por registro se han reducido una quinta parte.

    Un saludo y gracias por la ayuda.
    jueves, 22 de octubre de 2009 8:37