Удаление данных средствами SQL, теория и практика

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

Методика быстрого удаления данных через SQL. Выбираем документ в 1С, добавляем простые условия - получаем готовый скрипт, удаляющий сами документы, их табличные части и движения по регистрам.

В данной статье будет рассмотрена методика удаления данных запросом в MSSQL-студии.

Отказ от ответственности

Действия после прочтения данной статьи целиком на Вашей ответственности, поэтому делать ли архив перед манипуляциями на уровне СУБД - уверен, знаете. Если останутся сомнения "делать ли архив?" - имейте, пожалуйста, в виду, что обработка в бета-версии. Но сам лично применял неоднократно в решении наших прикладных задач. Иногда, восстанавливал базу из архива, исправлял ошибки в обработке, запускал снова.

Проверяйте, пожалуйста, текст скрипта перед выполнением. Понимайте, что делаете. Помните, что ответственность лежит целиком на Вас!

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

Основная цель применения

Прикладная задача, в процессе решения которой родилась данная методика: свертка базы (ввод остатков, удаление документов и движений регистров в прошлых периодах). То есть: удаление документов (вместе с табличными частями, разумеется) до определённой даты, и их движений. Иногда обработка использовалась для удаления данных с более сложными условиями, но они менялись вручную уже в SQL-студии.

Предыстория

Проблема: свертка стандартными средствами происходила неприлично долго. Точней, именно этап удаления старых данных. Остатки вводятся быстро, а вот удаление движений регистров, пометка на удаление документов, само удаление - по нашим оценкам на наших объёмах (500ГБ) заняло бы недели.

Решение: удалить данные средствами SQL, чтоб миновать механизмы платформы 1С, ненужные при данной операции, но отнимающие драгоценное (в рамках данной задачи) время.

Препятствия: Данные в SQL хранятся в разных таблицах. Таблиц много, как их связать - не всегда понятно. То есть мало очистить сам документ (шапку), надо очистить также данные табличных частей, и движений документа. Движения по разным регистрам. Для одного документа регистров может быть много. Каждый регистр в свою очередь хранит данные также в нескольких таблицах. Наименования таблиц - неосмысленные.

Теория

Варианты (операторы) удаления в SQL.

  1. DROP - полное удаление таблицы из структуры данных (вместе с данными). То есть очищаются не только данные, но и метаданные. Работает мгновенно.
  2. TRUNCATE - полная очистка таблицы с сохранением структуры таблицы (очищаются только строки таблицы, колонки остаются прежними). Работает мгновенно.
  3. DELETE - удаление записей в таблице по определенному условию. Занимает определенное время.

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

Для того, чтоб удалить данные целостно по ряду связанных таблиц документа (шапка, ТЧ, движения) - сперва я рассматривал вариант честно отобрать данные документа по дате, а потом уже связать с другими таблицами через JOIN. То есть очистить поочередно все связанные таблицы, после чего удалить основную (так как только в ней есть реквизит, по которому решаем удалять объект или нет)

Но впоследствии нашёл более удобное решение. А именно - очистить шапку. А потом все связанные таблицы поочередно, у кого нет "пары" в основной таблице (ссылка/регистратор="битая" ссылка). 

НО данный вариант не работает для движений документа. Так как после удаления основной таблицы документа - IS NULL даёт истину после соединения таблицы движений регистра и основной таблицы документа в 2х случаях

  1. Когда действительно записи сделаны этим видом документа, и эти документы были удалены (тут всё хорошо)
  2. Когда записи были сделаны документами других видов, и в этом случае записи удаляются ошибочно (так нельзя!). Чтоб решить эту проблему надо связывать таблицу регистра со всеми возможными типами регистраторов, а это слишком сложно

В итоге была выбрана и реализована следующая стратегия

  1. Удаляем записи регистров, которые двигает нужный вид документа, по связке с основной таблицей документа
  2. Удаляем основную таблицу документа
  3. Удаляем записи табличных частей документа, у которых Ссылка после соединения = IS NULL
  4. Очищаем целиком таблицы журналов, где участвует документ (нехорошо, но в нашем случае - не критично, можно и не трогать)
  5. Опционально можно очистить таблицы регистрации изменений для обмена

Таблиц много + названия неудобные + конструктора запросов нет = очень много рутины с высокой вероятностью ошибки и дороговизной ошибок. Поэтому был создан инструмент, берущий большую часть рутины на себя.

Особенности

  1. Данная методика не рассматривает проблемы параллельности работы во время удаления. То есть очистку я делал по данной методике без активной работы других пользователей.
  2. Данная методика удаляет данные по каждой таблице за одну итерацию. Порционное удаление не поддерживается. На наших задачах этого было достаточно, особенно учитывая п.1
  3. На данный момент, обработка существует только для обычного приложения

Порядок действий 

  1. Делаем архив
  2. Составляем список удаляемых данных (самые большие таблицы, которые удалить стандартными средствами слишком долго / неудобно / не хочется разбираться с тем, что удаляемые данные зарегистрируются к обмену и "пойдут" куда не надо)
  3. Запускаем обработку, при желании сверху ставим отбор
  4. Выделяем в списке нужные виды документов (обязательно выделяем ОСНОВНЫЕ таблицы, то есть таблицы шапок, а не табличных частей)
    Можно сразу несколько с Ctrl-ом (доступно множественное выделение)
  5. Переходим на вторую закладку и выбираем нужный нам вариант удаления (например по дате)
  6. Нажимаем соответствующую кнопку по формированию скрипта
  7. Получаем в окне сообщений готовый скрипт на удаление данных на языке SQL
  8. Копируем в буфер, вставляем в SQL Management Studio
  9. При надобности корректируем. Например через замену (Ctrl+H) можно заменить "<" на ">=", и получится скрипт удаляющий данные документов не до указанной даты, а, наоборот, - начиная с неё. Либо отбор по дате можно заменить на пометку документа на удаления (_Marked = 1). Либо чтоб удалялись только непроведенные (_Posted = 0). 
  10. Запускаем на выполнение, дожидаемся завершения, наблюдаем за статусом на закладке Messages
  11. Проверяем результат в 1С
  12. Обязательно пересчитываем итоги затронутых регистров накопления и бухгалтерии, так как таблицы итогов не обрабатываются

При желании протестировать/посмотреть "что именно будет удаляться" предусмотрена соответствующая опция (галочка справа внизу), в этом случае скрипты будут формироваться с оператором SELECT, а не DELETE. Можно выделить нужный кусок, запустить на исполнение, посмотреть результаты, прежде чем запускать на удаление.

Вместо эпилога

Обработка сама не подключается в SQL и не запускает скрипт там на выполнение. Только формирует текст скрипта. Считаю, что скопировать-вставить нетрудно, а если нет навыков работы в SQL-студии, то и запускать подобное, возможно, рано.

Статья и обработка будут дорабатываться, но, надеюсь, уже в текущем виде кому-то пригодится.

Конструктивную критику, вопросы и пожелания - прошу в комментарии!

Спасибо за прочтение!

Скачать файлы

Наименование Файл Версия Размер
Удаление данных средствами SQL, теория и практика:
.epf 15,44Kb
25.04.18
4
.epf 1 15,44Kb 4 Скачать

См. также

Вознаграждение за ответ
Показать полностью
В этой теме еще нет сообщений.
Оставьте свое сообщение