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

16.05.2017 9 Comments

Администрирование, Администрирование данных 1С

, , ,

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

Дано:

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

Задача:

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

Решение: 

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

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

*имя таблицы можно посмотреть с помощью обрабтки //infostart.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, снимайте кеды, прежде чем пинаться! 🙂

Related Posts

9 Comments

  1. METAL

    Спасибо!

    Опечатка <ИмяПоял>

    Reply ↓
  2. JesteR

    (1) Спасибо, поправил.

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

    Reply ↓
  3. AlexGroovy

    (2)Работает))))

    Reply ↓
  4. 1cWin

    Не стал бы я надеяться на @@ROWCOUNT

    Reply ↓
  5. JesteR

    (4) Алексей, ты поделись с нами почему не стал бы? Может и я не стану после твоих аргументов 🙂

    Reply ↓
  6. JesteR

    (4) У меня изначально, было такое условие цикла

    WHILE (select COUNT(*) from <ИмяТаблицы> where <ИмяПоля>= <ЗначениеПоля>)>1

    Но я решил, что это не оптимально…

    Потом нашел описание системной переменной ROWCOUNT и переписал запрос на тот, что в статье. Все отработало без нареканий.

    Reply ↓
  7. 1cWin

    (6) здесь может быть потенциальная ошибка. statement изменчив. ROWCOUNT лучше сохранить, а потом использовать в сравнении.

    Reply ↓
  8. JesteR

    (7) Т.к. после операции удаления нет никаких операторов, посчитал излишним в переменную сохранять.

    Reply ↓
  9. gra4enok

    Если нужно удалить много больше, чем половина записей таблицы, можно и через truncate сделать. Insert и Delete примерно одинаково отрабатывают по времени. Поэтому можно сразу очистить всю таблицу и вставить оставшиеся строки. К примеру:

    declare @test int

begin transaction Tran_<ИмяТаблицы>
select * into ##tmp_<ИмяТаблицы> from <ИмяТаблицы> where <ИмяПоля>= <ЗначениеПоля>

set @test = @@ROWCOUNT

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

if @test>0
insert into <ИмяТаблицы> select * from ##tmp_<ИмяТаблицы>;

commit transaction Tran_<ИмяТаблицы>
while exists(select name from tempdb..sysobjects where name=’##tmp_<ИмяТаблицы>’)
begin
drop table ##tmp_<ИмяТаблицы>
end;

    Показать

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *