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

truncate table sql server ejemplo

Question

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 mesa es leída / escrita "a menudo" y no me gustaría una "eliminación" larga para tomar mucho tiempo y bloquear la mesa por mucho tiempo
• Necesito saltear el registro de transacciones (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 registro de transacciones gratis.

Answer 1

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

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

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

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 archivo de salida va a tener registros 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/

Answer 2

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 bloqueo.

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 fragmentos.

Actualizado

Como los bucles while y las eliminaciones de fragmentos son el nuevo rosa aquí, también incluiré 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

Answer 3

He utilizado lo siguiente para eliminar alrededor de 50 millones de registros:

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

Tenga en cuenta que mantener BatchSize <5000 es menos costoso en recursos.

Answer 4

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

Answer 5

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 en el lote, lo he usado de 500 a 50000 según la tabla. Si usa la eliminación en cascada, probablemente necesite un número más pequeño ya que tiene esos registros secundarios para eliminar.

Answer 6

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 fragmento.

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

Answer 7

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

DELETE TOP 1000 FROM Sales WHERE toDelete=''1''