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

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

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

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

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

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

Делайте архив. 

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

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

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

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

Предыстория

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

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

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

Теория

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

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

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

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

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

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

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

Устаревшая стратегия (альтернатива)

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

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

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

Вобщем порядок был такой (отличия в пунктах 2 и 3)

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

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

Особенности

  1. Доступна опция порционного удаления.
  2. На данный момент, обработка существует только для обычного приложения.
  3. Обработка сама не подключается в SQL и не запускает скрипт там на выполнение. Только формирует текст скрипта. Считаю, что скопировать-вставить нетрудно, а если нет навыков работы в SQL-студии, то и запускать подобное, возможно, рано.
  4. Исходный код открыт.

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

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

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

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

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

14

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

Наименование Файл Версия Размер
Удаление данных средствами SQL, теория и практика:
.epf 20,72Kb
16.11.18
34
.epf 1.29 20,72Kb 34 Скачать

См. также

Специальные предложения

Вознаграждение за ответ
Показать полностью
Комментарии
Избранное Подписка Сортировка: Древо
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 100 11.07.18 16:10 Сейчас в теме
(3) Спасибо большое, комментарии приняты, постараюсь реализовать/поправить, но из-за нехватки времени сроки обещать не могу.
По 1 - есть некое решение уже, сам столкнулся, доведу чуть до ума, поделюсь
2. Сейчас есть неудобство, когда начинает чистить какую-то таблицу - сообщает о начале вместе с сообщением о завершении. Не знаю с чем связано, но постараюсь решить. Если есть предложения - внимательно слушаю.
3. Не понял честно говоря, о чём речь
4. 'Недопустимое имя столбца "_Period"' - Да, тоже замечал, такое бывает когда идёт попытка очистить регистр, у которого регистратором является выбранный документ, но регистр непериодический. В этом случае регистр не очищается. Посчитал нестрашным для наших задач, поэтому пока не успел поправить.
6. METAL 100 27.07.18 16:48 Сейчас в теме
(3)
1. Удобно было бы из ОСНОВНЫЕ таблицы заложить в фильтр (решается сортировкой нужной колонки)
2. Вывод сообщений скрипта в SQL немного не удобен, особенно при обработке большого количества документов

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

[fix] Исправлено
Оставьте свое сообщение