Очередная очистка таблиц средствами MS SQL

Администрирование - Чистка базы

Очередной пост про очистку таблицы средствами MS SQL. И почему стоит очищать большие таблицы порциями.

Дано:

  • СУБД MS SQL Server
  • Регистр сведений ~80 млн записей

Задача:

  • удалить ~70 млн записей регистра сведений
  • удалить по условию

Решение: 

Если бы мне необходимо было удалить все записи, я бы воспользовался следующей командой

USE <ИмяБазы>
truncate table <ИмяТаблицы>

*имя таблицы можно посмотреть с помощью обрабтки //obmen-1c.ru/public/16282/

Но мне надо было удалить записи по условию, казалось бы, что может быть проще, пишем:

USE <ИмяБазы>
delete from <ИмяТаблицы> where <ИмяПоля> = <ЗначениеПоля>

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

Оптимальное решение -  удалять записи порциями, напимер по 5000.

USE <ИмяБазы>
WHILE 1=1
BEGIN
	DELETE top (5000) FROM <ИмяТаблицы> 
	where <ИмяПоля>= <ЗначениеПоля>;
	
	if @@ROWCOUNT<1 break;
END

 ROWCOUNT - Возвращает число строк, затронутых при выполнении последней инструкции. Если число строк превышает 2 миллиарда, используйте ROWCOUNT_BIG

Надеюсь, мой пост будет кому-то полезен.

Гуру SQL, снимайте кеды, прежде чем пинаться! :)

См. также

Комментарии
1. Михаил Беляев (METAL) 77 21.04.17 19:12 Сейчас в теме
Спасибо!
Опечатка <ИмяПоял>
2. Сергей JesteR (JesteR) 137 21.04.17 22:55 Сейчас в теме
(1) Спасибо, поправил.

По рзеузльаттам илссоевадний одонго анлигсйокго унвиертисета, не иеемт занчнеия, в каокм проякде рсапжоолены бкувы в солве. Галовне, чотбы преавя и пслонедяя бквуы блыи на мсете. осатьлыне бкувы мгоут селдовтаь в плоонм бсепордяке, все-рвано ткест чтаитсея без побрелм. Пичрионй эгото ялвятеся то, что мы не чиаетм кдаужю бкуву по отдльенотси, а все солво цлиеком.
DeD MustDie; METAL; savinsva; julia96_07; demkonst; RegrZ; AlexGroovy; +7 Ответить
3. Алексей Магеридонов (AlexGroovy) 22.04.17 22:23 Сейчас в теме
4. Алексей Дубичев (1cWin) 25.04.17 08:33 Сейчас в теме
Не стал бы я надеяться на @@ROWCOUNT
5. Сергей JesteR (JesteR) 137 25.04.17 09:28 Сейчас в теме
(4) Алексей, ты поделись с нами почему не стал бы? Может и я не стану после твоих аргументов :)
6. Сергей JesteR (JesteR) 137 25.04.17 09:34 Сейчас в теме
(4) У меня изначально, было такое условие цикла
WHILE (select COUNT(*) from <ИмяТаблицы> where <ИмяПоля>= <ЗначениеПоля>)>1


Но я решил, что это не оптимально...
Потом нашел описание системной переменной ROWCOUNT и переписал запрос на тот, что в статье. Все отработало без нареканий.
7. Алексей Дубичев (1cWin) 25.04.17 14:36 Сейчас в теме
(6) здесь может быть потенциальная ошибка. statement изменчив. ROWCOUNT лучше сохранить, а потом использовать в сравнении.
8. Сергей JesteR (JesteR) 137 25.04.17 15:22 Сейчас в теме
(7) Т.к. после операции удаления нет никаких операторов, посчитал излишним в переменную сохранять.
Оставьте свое сообщение