Как эффективно удалять строки, не используя таблицу Truncate в таблице 500 000 строк

Ответ 1

Вызов DELETE FROM TableName сделает все удаление в одной крупной транзакции. Это дорого.

Вот еще один вариант, который будет удалять строки пакетами:

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

Ответ 2

Что вы хотите - пакетная обработка.

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

Конечно, вы можете поэкспериментировать, что лучше всего использовать для партии, я использовал от 500 до 50000 в зависимости от таблицы. Если вы используете каскадное удаление, вам, вероятно, понадобится меньшее число, так как у вас есть эти дочерние записи для удаления.

Ответ 3

Один из способов, которым я должен был это сделать в прошлом, - иметь хранимую процедуру или script, которая удаляет n записей. Повторяйте до конца.

DELETE TOP 1000 FROM Sales WHERE toDelete='1'

Ответ 4

Вам следует попытаться дать подсказку ROWLOCK, чтобы она не блокировала всю таблицу. Однако, если вы удалите много строк, произойдет эскалация блокировки.

Кроме того, убедитесь, что в столбце toDelete есть некластеризованный отфильтрованный индекс (только для 1 значения). Если возможно, сделайте бит столбцом, а не varchar (или тем, что он сейчас).

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

В конечном счете, вы можете попробовать выполнить итерацию по таблице и удалить в кусках.

Обновление

Так как в то время как петли и удаленные фрагменты являются новыми розовыми здесь, я тоже вложу свою версию (в сочетании с моим предыдущим ответом):

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

Ответ 5

Мой собственный подход к этой функции будет следующим. Таким образом, нет повторяющегося кода, и вы можете управлять размером вашего блока.

DECLARE @DeleteChunk INT = 10000
DECLARE @rowcount INT = 1

WHILE @rowcount > 0
BEGIN

  DELETE TOP (@DeleteChunk) FROM Sales WITH(ROWLOCK)

  SELECT @rowcount = @@RowCount
END

Ответ 6

Я оставлю свой ответ здесь, так как я смог протестировать различные подходы для массового удаления и обновления (мне пришлось обновить и затем удалить 125 миллионов строк, сервер имеет 16 ГБ ОЗУ, Xeon E5-2680 @2,7 ГГц, SQL Server 2012).

TL; DR: всегда обновлять/удалять по первичному ключу, а не по каким-либо другим условиям. Если вы не можете использовать PK напрямую, создайте временную таблицу и заполните ее значениями PK и обновите/удалите вашу таблицу, используя эту таблицу. Используйте индексы для этого.

Я начал с решения сверху (@Kevin Aenmey), но этот подход оказался неуместным, так как моя база данных работала и обрабатывает пару сотен транзакций в секунду, и была некоторая блокировка (был индекс для всех там поля из условия, использование WITH(ROWLOCK) ничего не изменило).

Итак, я добавил оператор WAITFOR, который позволял базе данных обрабатывать другие транзакции.

deleteMore:
WAITFOR DELAY '00:00:01'
DELETE TOP(1000) FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3
IF @@ROWCOUNT != 0
    goto deleteMore

Этот подход смог обработать ~ 1.6 млн. Строк/час для обновления и ~ 0,2 млн. Строк/час для удаления.

Переход к временным таблицам изменил многое.

deleteMore:
SELECT TOP 10000 Id /* Id is the PK */
  INTO #Temp 
  FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3 

DELETE MT
  FROM MyTable MT
  JOIN #Temp T ON T.Id = MT.Id 

/* you can use IN operator, it doesn't change anything
 DELETE FROM MyTable WHERE Id IN (SELECT Id FROM #Temp)

 */
IF @@ROWCOUNT > 0 BEGIN
    DROP TABLE #Temp
    WAITFOR DELAY '00:00:01'
    goto deleteMore
END ELSE BEGIN
    DROP TABLE #Temp
    PRINT 'This is the end, my friend'
END

Это решение обрабатывало ~ 25 миллионов строк/час для обновления (в 15 раз быстрее) и ~ 2,2 миллиона строк/час для удаления (в 11 раз быстрее).

Ответ 7

Я использовал ниже, чтобы удалить около 50 миллионов записей -

BEGIN TRANSACTION     
     DeleteOperation:
     DELETE TOP (BatchSize)
     FROM  [database_name].[database_schema].[database_table] 

     IF @@ROWCOUNT > 0
     GOTO DeleteOperation
COMMIT TRANSACTION

Обратите внимание, что сохранение BatchSize < 5000 дешевле на ресурсах.

Ответ 8

Как я полагаю, лучший способ удалить огромное количество записей - удалить его с помощью Primary Key. (Что такое Primary Key см. Здесь)

Итак, вам нужно сгенерировать tsql script, который содержит весь список удаляемых строк и после этого выполнить этот script.

Например, код ниже должен сгенерировать этот файл

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

В выходном файле будут записи типа

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

И теперь вам нужно использовать утилиту SQLCMD, чтобы выполнить этот script.

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

Этот подход можно найти здесь https://www.mssqltips.com/sqlservertip/3566/deleting-historical-data-from-a-large-highly-concurrent-sql-server-database-table/