Удаление 1 миллиона строк в SQL Server

Ответ 1

Вот структура для пакетного удаления, как было предложено выше. Не пытайтесь 1M сразу...

Размер партии и задержки ожидания, очевидно, довольно переменный и будет зависеть от возможностей ваших серверов, а также от необходимости смягчать конкуренцию. Вам может потребоваться вручную удалить несколько строк, измерить, сколько времени они берут, и настроить размер партии на то, что может обрабатывать ваш сервер. Как упоминалось выше, что-либо более 5000 может вызвать блокировку (о которой я не знал).

Это было бы лучше всего сделать после нескольких часов... но 1M строк на самом деле не так много для обработки SQL. Если вы просматриваете свои сообщения в SSMS, для вывода печати может потребоваться некоторое время, но после нескольких партий просто имейте в виду, что он не будет обновляться в режиме реального времени.

Изменить: добавлено время остановки @MAXRUNTIME и @BSTOPATMAXTIME. Если вы установили @BSTOPATMAXTIME в 1, script остановится на нем в нужное время, например, 8:00. Таким образом, вы можете планировать его в ночное время, начиная с полуночи, и он остановится до производства в 8 утра.

Изменить: ответ довольно популярен, поэтому я добавил RAISERROR вместо PRINT для комментариев.

DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS

WHILE @BATCHSIZE>0
BEGIN
    -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
    IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
    BEGIN
        RETURN
    END

    DELETE TOP(@BATCHSIZE)
    FROM SOMETABLE
    WHERE 1=2

    SET @[email protected]@ROWCOUNT
    SET @[email protected]+1
    SET @[email protected][email protected]
    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    WAITFOR DELAY @WAITFORVAL 
END

Ответ 2

BEGIN TRANSACTION     
    DoAgain:
    DELETE TOP (1000)
    FROM <YourTable>
    IF @@ROWCOUNT > 0
    GOTO DoAgain
COMMIT TRANSACTION

Ответ 3

Вот что я использовал:

• Если плохие данные смешиваются с good-

  INSERT INTO #table 
     SELECT columns 
     FROM old_table 
     WHERE statement to exclude bad rows
  
  TRUNCATE old_table
  
  INSERT INTO old_table 
     SELECT columns FROM #table
  

Ответ 4

Не уверен, насколько это было бы хорошо, но что, если вы делаете, как показано ниже (при условии, что table_1 является отдельной таблицей, я имею в виду отсутствие ссылки на другую таблицу)

• создать дублируемую таблицу table_1 как table_1_dup

• insert into table_1_dup select * from table_1 where condition1 <> 'value';

• drop table table_1

• sp_rename table_1_dup table_1

Ответ 5

Может быть, это решение от Ури Димант

WHILE 1 = 1
BEGIN
   DELETE TOP(2000)
   FROM Foo
   WHERE <predicate>;
   IF @@ROWCOUNT < 2000 BREAK;
END

(Ссылка: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5225ca7-f16a-4b80-b64f-3576c6aa4d1f/how-to-quickly-delete-millions-of-rows?forum=transactsql)

Ответ 6

Если вы не можете позволить себе, чтобы база данных не работала при ремонте, делайте это небольшими партиями. См. Также: Как эффективно удалить строки, не используя таблицу Truncate в таблице 500 000 строк

Если вы спешите и вам нужен самый быстрый способ:

• отказаться от базы данных
• удалить все некластеризованные индексы и триггеры
• удалить записи (или если большая часть записей плохая, скопируйте + перетащите + переименовать таблицу)
• (если применимо) исправить несоответствия, вызванные тем, что вы сбросили триггеры
• заново создайте индексы и триггеры
• вернуть базу данных в производство

Ответ 7

Верьте или нет, у меня была таблица с 3 миллионами 5 сотен тысяч записей

Я написал это выражение

DELETE TOP(500000) FROM MyTable

и я выполнил это вручную около 7 раз, это займет у вас около 50 секунд для каждого выполнения, но в конце это решило проблему