Cómo eliminar filas de manera eficiente sin usar Truncar tabla en una tabla de más de 500,000 filas

Digamos que tenemos Ventas de tabla con 30 columnas y 500,000 filas. Me gustaría eliminar 400,000 en la tabla (aquellos donde "toDelete='1'" ).

Pero tengo algunas limitaciones:

  • la table es leída / escrita "a menudo" y no me gustaría una "eliminación" larga para tomar mucho time y bloquear la table por mucho time
  • Necesito saltear el logging de transactions (como con un TRUNCATE ) pero mientras hago un "DELETE ... WHERE..." (Necesito poner una condición), pero no he encontrado ninguna manera de hacer esto …

Cualquier consejo sería bienvenido para transformar una

 DELETE FROM Sales WHERE toDelete='1' 

a algo más particionado y posiblemente logging de transactions gratis.

Llamar a DELETE FROM TableName hará la eliminación completa en una transacción grande. Esto es caro.

Aquí hay otra opción que eliminará filas en lotes:

 deleteMore: DELETE TOP(10000) Sales WHERE toDelete='1' IF @@ROWCOUNT != 0 goto deleteMore 

Lo que quieres es un procesamiento por lotes.

 While (select Count(*) from sales where toDelete =1) >0 BEGIN Delete from sales where SalesID in (select top 1000 salesId from sales where toDelete = 1) END 

Por supuesto, puede experimentar cuál es el mejor valor para usar para el lote, lo he usado de 500 a 50000 dependiendo de la tabla. Si usa la eliminación en cascada, probablemente necesite un número más pequeño ya que tiene esos loggings secundarios para eliminar.

Una manera en que tuve que hacer esto en el pasado es tener un procedimiento almacenado o script que elimina n loggings. Repita hasta que termine.

 DELETE TOP 1000 FROM Sales WHERE toDelete='1' 

Debería intentar darle una pista ROWLOCK para que no bloquee toda la tabla. Sin embargo, si elimina muchas filas, se producirá una escalada de locking.

Además, asegúrese de tener un índice filtrado no agrupado (solo para 1 valor) en la columna toDelete . Si es posible, conviértalo en una columna de bit, no varchar (o lo que es ahora).

 DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1' 

En última instancia, puede intentar iterar sobre la tabla y eliminar en fragments.

Actualizado

Como los loops while y las eliminaciones de fragments son el nuevo rosa aquí, también includeé mi versión (combinada con mi respuesta anterior):

 SET ROWCOUNT 100 DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1' WHILE @@rowcount > 0 BEGIN SET ROWCOUNT 100 DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1' END 

Mi propia opinión sobre esta funcionalidad sería la siguiente. De esta forma, no hay código repetido y puedes administrar tu tamaño de fragment.

 DECLARE @DeleteChunk INT = 10000 DECLARE @rowcount INT = 1 WHILE @rowcount > 0 BEGIN DELETE TOP (@DeleteChunk) FROM Sales WITH(ROWLOCK) SELECT @rowcount = @@RowCount END 

He utilizado lo siguiente para eliminar alnetworkingedor de 50 millones de loggings:

 BEGIN TRANSACTION DeleteOperation: DELETE TOP (BatchSize) FROM [database_name].[database_schema].[database_table] IF @@ROWCOUNT > 0 GOTO DeleteOperation COMMIT TRANSACTION 

Tenga en count que mantener BatchSize <5000 es less costoso en resources.

Como supongo, la mejor manera de eliminar una gran cantidad de loggings es eliminarlos por Primary Key . (¿Qué es Primary Key ver aquí )

Por lo tanto, debe generar la secuencia de commands tsql que contiene toda la list de líneas para eliminar y luego ejecutar esta secuencia de commands.

Por ejemplo, el siguiente código generará ese file

 GO SET NOCOUNT ON SELECT 'DELETE FROM DATA_ACTION WHERE ID = ' + CAST(ID AS VARCHAR(50)) + ';' + CHAR(13) + CHAR(10) + 'GO' FROM DATA_ACTION WHERE YEAR(AtTime) = 2014 

El file de salida va a tener loggings como

 DELETE FROM DATA_ACTION WHERE ID = 123; GO DELETE FROM DATA_ACTION WHERE ID = 124; GO DELETE FROM DATA_ACTION WHERE ID = 125; GO 

Y ahora debe usar la utilidad SQLCMD para ejecutar este script.

 sqlcmd -S [Instance Name] -E -d [Database] -i [Script] 

Puede encontrar este enfoque explicado aquí https://www.mssqltips.com/sqlservertip/3566/deleting-historical-data-from-a-large-highly-concurrent-sql-server-database-table/