Introducción

¡Hola, chicos!
En este artículo me gustaría compartir con ustedes un pequeño código que necesitaba usar hoy para hacer una ACTUALIZACIÓN en una tabla relativamente grande (más de 55 millones de registros) en una base de datos SQL de Azure y, después de esperar 1 hora y 30 minutos, hubo un error de conexión y tuve que hacer todo nuevamente.

No solo en este caso, sino que puede suceder que el registro se desborde y la operación también cause un error y, como sabemos, cuando ocurre un error durante la ACTUALIZAR o ELIMINAR, se inicia la reversión automática y en realidad no se cambia ninguna línea. Dividir esta única operación en operaciones más pequeñas y segmentadas permitirá vaciar el registro y así minimizar posibles desbordamientos del registro.

También es posible que desee dividir estas operaciones grandes y que consumen mucho tiempo en partes más pequeñas para realizar un seguimiento del progreso, o que su ventana de mantenimiento no sea lo suficientemente larga para procesar ACTUALIZAR/ELIMINAR en todas las líneas necesarias y desee continuar en otra ventana.

También he visto casos de tablas con disparadores que pueden terminar generando mucha sobrecarga al ejecutar una ACTUALIZAR/BORRAR que cambia muchas filas. Y también tenemos que recordar los bloqueos de la mesa, que al romperse en partes más pequeñas, se pueden desbloquear rápidamente mientras el siguiente lote comienza a procesarse.

Hay varias razones que pueden influir en la decisión de participar en una ACTUALIZACIÓN/ELIMINACIÓN grande y en este artículo le mostraré algunas formas sencillas de hacerlo.

Importante: Si solo desea eliminar o cambiar el primer o último registro de una tabla, puede usar la solución que compartí en el artículo. SQL Server: cómo realizar ACTUALIZAR y ELIMINAR con registros TOP x.

Cómo eliminar o actualizar datos en tablas grandes

ACTUALIZAR particionado por campo entero
En el siguiente ejemplo, estoy usando una columna de incremento automático de tipo entero para ensamblar los rangos de valores que actualizaré. La variable @Increase define la cantidad de líneas de cada lote que se actualizarán y definí en este ejemplo que la cantidad de líneas actualizadas a la vez será 1 millón de líneas.

ACTUALIZAR particionado por campo entero

DECLARE
    @Min INT,
    @Max INT,
    @Contador INT = 0,
    @Aumento INT = 1000000,
    @LimiteInferior INT = 0,
    @LimiteSuperior INT = 0,
    @Msg VARCHAR(MAX)
    

SELECT 
    @Min = MIN(Id_Registro),
    @Max = MAX(Id_Registro)
FROM
    dbo.Tabela


WHILE(@LimiteSuperior < @Max)
BEGIN
    

    SET @LimiteInferior = @Min + (@Contador * @Aumento)
    SET @LimiteSuperior = @LimiteInferior + @Aumento

    
    UPDATE
        A
    SET
        Dt_Registro = (CASE
            WHEN ISNUMERIC(A.Data_Registro_String) = 1 THEN CONVERT(DATE, DATEADD(DAY, TRY_CONVERT(INT, A.Data_Registro_String), '1900-01-01'))
            WHEN LEN(A.Data_Registro_String) = 10 AND TRY_CONVERT(DATE, A.Data_Registro_String, 103) IS NOT NULL THEN CONVERT(DATE, A.Data_Registro_String, 103)
            ELSE CONVERT(DATE, A.Data_Registro_String)
        END)
    FROM
        dbo.Tabela A
    WHERE
        Id_Registro >= @LimiteInferior
        AND Id_Registro < @LimiteSuperior


    SET @Contador += 1


    SET @Msg = CONCAT('Processando dados no intervalo ', @LimiteInferior, '-', @LimiteSuperior, '...')
    RAISERROR(@Msg, 1, 1) WITH NOWAIT

END

Resultado:


ACTUALIZAR particionado por campo de fecha
En el siguiente ejemplo, estoy usando una columna de fecha para ensamblar los rangos de valores que actualizaré. La variable @Increase define la cantidad de días de cada lote que se actualizará y en este ejemplo definí que la cantidad de días que se actualizará para cada bloque será 30 días.

ACTUALIZAR particionado por campo de fecha

DECLARE
    @Min DATE,
    @Max DATE,
    @Contador INT = 0,
    @Aumento INT = 30,
    @LimiteInferior DATE = '1900-01-01',
    @LimiteSuperior DATE = '1900-01-01',
    @Msg VARCHAR(MAX)
    

SELECT 
    @Min = MIN(Dt_Cadastro),
    @Max = MAX(Dt_Cadastro)
FROM
    dbo.Tabela


WHILE(@LimiteSuperior < @Max)
BEGIN
    

    SET @LimiteInferior = DATEADD(DAY, (@Contador * @Aumento), @Min)
    SET @LimiteSuperior = DATEADD(DAY, @Aumento, @LimiteInferior)

    
    UPDATE
        A
    SET
        Dt_Registro = (CASE
            WHEN ISNUMERIC(A.Data_Registro_String) = 1 THEN CONVERT(DATE, DATEADD(DAY, TRY_CONVERT(INT, A.Data_Registro_String), '1900-01-01'))
            WHEN LEN(A.Data_Registro_String) = 10 AND TRY_CONVERT(DATE, A.Data_Registro_String, 103) IS NOT NULL THEN CONVERT(DATE, A.Data_Registro_String, 103)
            ELSE CONVERT(DATE, A.Data_Registro_String)
        END)
    FROM
        dbo.Tabela A
    WHERE
        Dt_Cadastro >= @LimiteInferior
        AND Dt_Cadastro < @LimiteSuperior


    SET @Contador += 1


    SET @Msg = CONCAT('Processando dados no intervalo ', CONVERT(VARCHAR(10), @LimiteInferior, 103), '-', CONVERT(VARCHAR(10), @LimiteSuperior, 103), '...')
    RAISERROR(@Msg, 1, 1) WITH NOWAIT

END

Resultado:


ELIMINAR TOP(N) particionado usando porcentaje
En el siguiente ejemplo, estoy eliminando el 10% de mi tabla con cada iteración. A medida que se eliminan los datos, no necesito controlar los intervalos, solo los elimino cada 10%.

ELIMINAR TOP(N) particionado usando porcentaje

DECLARE 
    @Msg VARCHAR(MAX),
    @Qt_Linhas INT

WHILE (1=1)
BEGIN
    
    DELETE TOP(10) PERCENT
    FROM dbo.Tabela
    WHERE [Status] = 6

    SET @Qt_Linhas = @@ROWCOUNT

    IF (@Qt_Linhas = 0)
        BREAK

    SET @Msg = CONCAT('Quantidade de Linhas Apagadas: ', @Qt_Linhas)
    RAISERROR(@Msg, 1, 1) WITH NOWAIT

END

Resultado:

Observación: Esta solución puede presentar problemas en tablas muy grandes, ya que el 10% puede representar un volumen muy grande de filas. Y cuando quedan pocos registros, el 10% puede requerir muchas iteraciones para eliminarse.

ELIMINAR TOP(N) particionado usando el número de filas
En el siguiente ejemplo, estoy eliminando 500 mil filas de mi tabla con cada iteración. A medida que se eliminan los datos, no necesito controlar los intervalos, simplemente elimino cada 500 mil líneas hasta que no queden más líneas que cumplan con los criterios del filtro.

ELIMINAR TOP(N) particionado usando el número de filas

DECLARE 
    @Msg VARCHAR(MAX),
    @Qt_Linhas INT

WHILE (1=1)
BEGIN
    
    DELETE TOP(100000)
    FROM dbo.Tabela
    WHERE [Status] = 6

    SET @Qt_Linhas = @@ROWCOUNT

    IF (@Qt_Linhas = 0)
        BREAK

    SET @Msg = CONCAT('Quantidade de Linhas Apagadas: ', @Qt_Linhas)
    RAISERROR(@Msg, 1, 1) WITH NOWAIT

END

Resultado:

¡Y eso es todo, amigos!
Espero que os haya gustado y un fuerte abrazo!