IE 2018

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

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

обработка удалениеданных MSSQL

12
Методика быстрого удаления данных через 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. [UPD] Добавлена опция порционного удаления! 
  2. На данный момент, обработка существует только для обычного приложения
  3. Обработка сама не подключается в SQL и не запускает скрипт там на выполнение. Только формирует текст скрипта. Считаю, что скопировать-вставить нетрудно, а если нет навыков работы в SQL-студии, то и запускать подобное, возможно, рано.

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

  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. Можно выделить нужный кусок, запустить на исполнение, посмотреть результаты, прежде чем запускать на удаление.

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

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

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

12

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

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

См. также

Вознаграждение за ответ
Показать полностью
Комментарии
Избранное Подписка Сортировка: Древо
3. refostart 05.07.18 13:31 Сейчас в теме
Обработка просто огонь, удобно делать тестовые копии с урезанными данными!
Полезна для изучения структуры таблиц в SQL

1. Удобно было бы из ОСНОВНЫЕ таблицы заложить в фильтр (решается сортировкой нужной колонки)
2. Вывод сообщений скрипта в SQL немного не удобен, особенно при обработке большого количества документов
3. Сдвиг дат

Иногда проскальзывает сообщение 'Недопустимое имя столбца "_Period"'
Похоже в скрипте

PRINT 'Начинаю очистку регистра: РегистрСведений.СостоянияОСОрганизаций.Основная	' +CAST(GETDATE() as varchar);
DELETE	FROM SubData
	FROM _InfoRg12237 SubData
 	INNER JOIN _Document188 MainData
ON SubData._RecorderRRef = MainData._IDRRef
WHERE _Period < @date_trim;
4. METAL 92 11.07.18 16:10 Сейчас в теме
(3) Спасибо большое, комментарии приняты, постараюсь реализовать/поправить, но из-за нехватки времени сроки обещать не могу.
По 1 - есть некое решение уже, сам столкнулся, доведу чуть до ума, поделюсь
2. Сейчас есть неудобство, когда начинает чистить какую-то таблицу - сообщает о начале вместе с сообщением о завершении. Не знаю с чем связано, но постараюсь решить. Если есть предложения - внимательно слушаю.
3. Не понял честно говоря, о чём речь
4. 'Недопустимое имя столбца "_Period"' - Да, тоже замечал, такое бывает когда идёт попытка очистить регистр, у которого регистратором является выбранный документ, но регистр непериодический. В этом случае регистр не очищается. Посчитал нестрашным для наших задач, поэтому пока не успел поправить.
6. METAL 92 27.07.18 16:48 Сейчас в теме
(3)
1. Удобно было бы из ОСНОВНЫЕ таблицы заложить в фильтр (решается сортировкой нужной колонки)
2. Вывод сообщений скрипта в SQL немного не удобен, особенно при обработке большого количества документов

Улучшено в новой версии
5. METAL 92 27.07.18 12:32 Сейчас в теме
UPD Обновлена версия обработки. Бета.
[*] Редизайн интерфейса, повышение юзабилити.
[+] Добавлена возможность целостного удаления документов на отдельной закладке
[+] Частично реализована опция порционного удаления. Порциями удаляются данные при выборе произвольной таблицы, а также при целостном удалении документов - основная таблица документа. Продолжение следует.
[*] Рефакторинг системы вывода сообщений. Теперь сообщения выводятся мгновенно, и выглядит всё более коротко и информативно
7. METAL 92 03.08.18 10:53 Сейчас в теме
[↑] Доделано и протестировано порционное удаление
Оставьте свое сообщение