sql server - una - ¿Cómo eliminar datos grandes de la tabla en SQL sin registro?

restriccion check sql server

Question

Tengo una gran tabla de datos. Hay 10 millones de registros en esta tabla.

Cuál es la mejor manera para esta consulta

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

Answer 1

1. Si está borrando todas las filas en esa tabla, la opción más simple es Truncar tabla, algo así como

   TRUNCATE TABLE LargeTable
   GO

   La tabla truncada simplemente vaciará la tabla, no puede usar la cláusula WHERE para limitar las filas que se eliminarán y no se dispararán los activadores.

2. Por otro lado, si está eliminando más del 80-90 por ciento de los datos, decir si tiene un total de 11 millones de filas y desea eliminar 10 millones de otra manera sería Insertar estas 1 millón de filas (registros que desea conservar) ) a otra mesa de ensayo. Trunque esta tabla grande e inserte estas 1 millón de filas.

3. O si los permisos / vistas u otros objetos que tienen esta tabla grande como su tabla subyacente no se ven afectados al eliminar esta tabla, puede obtener esta cantidad relativamente pequeña de las filas en otra tabla soltar esta tabla y crear otra tabla con el mismo esquema e importar estos vuelve a entrar en esta tabla ex-Large.

4. Una última opción que puedo pensar es cambiar el Recovery Mode to SIMPLE tu base de datos Recovery Mode to SIMPLE y luego eliminar las filas en lotes más pequeños usando un ciclo while algo así ...

   DECLARE @Deleted_Rows INT;
   SET @Deleted_Rows = 1;
   
   
   WHILE (@Deleted_Rows > 0)
     BEGIN
      -- Delete some small number of rows at a time
        DELETE TOP (10000)  LargeTable 
        WHERE readTime < dateadd(MONTH,-7,GETDATE())
   
     SET @Deleted_Rows = @@ROWCOUNT;
   END

y no te olvides de volver a cambiar el modo de recuperación y creo que debes hacer una copia de seguridad para que sea completamente efectivo (los modos de cambio o recuperación).

Answer 2

@Francisco Goldenstein, solo una pequeña corrección. El COMMIT debe utilizarse después de establecer la variable; de ​​lo contrario, WHILE se ejecutará una sola vez:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

Answer 3

Esta variación de M.Ali está funcionando bien para mí. Elimina algunos, borra el registro y se repite. Estoy viendo el registro crecer, caer y comenzar de nuevo.

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
    delete top (100000) from InstallLog where DateTime between ''2014-12-01'' and ''2015-02-01''
    SET @Deleted_Rows = @@ROWCOUNT;
    dbcc shrinkfile (MobiControlDB_log,0,truncateonly);
END

Answer 4

La respuesta de @m-ali es correcta, pero también tenga en cuenta que los registros podrían crecer mucho si no se compromete la transacción después de cada fragmento y se realiza un punto de control. Así es como lo haría y tome este artículo http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes como referencia, con pruebas de rendimiento y gráficos:

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable 
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

     SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

Answer 5

Otro uso:

SET ROWCOUNT 1000 -- Buffer

DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE())

DELETE LargeTable  WHERE readTime < @DATE
WHILE @@ROWCOUNT > 0
BEGIN
   DELETE LargeTable  WHERE readTime < @DATE
END
SET ROWCOUNT 0

Opcional;

Si el registro de transacciones está habilitado, deshabilite los registros de transacciones.

ALTER DATABASE dbname SET RECOVERY SIMPLE;

Answer 6

Puede eliminar lotes pequeños usando un ciclo while, algo como esto:

DELETE TOP (10000)  LargeTable 
WHERE readTime < dateadd(MONTH,-7,GETDATE())
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

Answer 7

Si está dispuesto (y puede) implementar particiones, esa es una técnica efectiva para eliminar grandes cantidades de datos con poca sobrecarga de tiempo de ejecución. Sin embargo, no es rentable para un ejercicio de una sola vez.

Answer 8

Sintaxis más corta

select 1
WHILE (@@ROWCOUNT > 0)
BEGIN
  DELETE TOP (10000) LargeTable 
  WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

Answer 9

También puede usar GO + cuantas veces desea ejecutar la misma consulta.

DELETE TOP (10000)  [TARGETDATABASE].[SCHEMA].[TARGETTABLE] 
WHERE readTime < dateadd(MONTH,-1,GETDATE());
-- how many times you want the query to repeat
GO 100

Answer 10

Pude borrar 19 millones de filas de mi tabla de 21 millones de filas en cuestión de minutos . Aquí está mi enfoque.

Si tiene una clave principal de incremento automático en esta tabla, puede utilizar esta clave principal.

1. Obtenga el valor mínimo de la clave principal de la tabla grande donde readTime <dateadd (MONTH, -7, GETDATE ()). (Agregue índice en readTime, si no está presente, este índice se eliminará de todos modos junto con la tabla en el paso 3.). Permite almacenarlo en una variable ''min_primary''

2. Inserte todas las filas que tienen la clave principal> min_primary en una tabla de etapas (tabla de memoria si no hay filas grandes).

3. Suelta la gran mesa.

4. Recrea la mesa. Copie todas las filas de la tabla de etapas a la tabla principal.

5. Suelta la tabla de etapas.