Удаление подчиненных таблиц
в базе 1С средствами MS SQL
По мере заполнения данными базы 1С:Предприятия может возникнуть необходимость для "зачистки" уж совсем устаревших таблиц, которые являются, в целом, служебными и были использованы однократно для каких-то технологических нужд и с тех пор хранятся в базе 1С. Не понятно, зачем.
В первом приближении, разумеется, была предпринята попытка удаления средствами 1С. Проблем не возникло, за исключением того, что количество подлежащих удалению строк 300+ тысяч и, соответственно, таким способом эта процедура, которая проводится, конечно, с поиском записей в регистрах сведений и подчищает всё, что связано с удаляемым элементом, растягивается на несколько (!) суток, что конечно неприемлемо. Таким образом было решено обследовать структуру таблиц собственно, в СУБД с тем, чтобы попытаться удалить это с помощью скрипта SQL.
В процессе анализа размеров таблиц в базе, обнаружилось, что справочник, подлежащий удалению, состоит из ссылочной структуры, которая хранит не только записи верхнего уровня, но и подчиненные, как они называются в терминах 1С, табличные части. То есть, справочник в СУБД физически представляет из себя систему из нескольких подчиненных таблиц. Вкупе с ними данная структура занимала до 30% объема базы на СУБД.
Таким образом, постановка задачи кратко выглядела следующим образом:
- выбрать все подлежащие удалению записи справочника (читай, "основной") таблицы, сделанные ранее определенной даты с отбором, кроме этого, по специфическим реквизитам;
- выбрать все записи из подтаблиц, ссылающиеся на отобранные записи "основной таблицы";
- обойти результат в цикле и удалить сначала строки из подчиненных таблиц, а затем удалить ненужные строки из "основной" таблицы.
Какие нам понадобятся инструменты для решения?
- любая обработка для 1С, показывающая структуру хранения базы 1С в именах таблиц СУБД
- незабвенный SSMS
Порядок действий.
1. Устанавливаем имена основной таблицы справочника и подчиненных к ней, как они именованы в базе SQL любым доступным инструментом, хоть бы и Инструментами разработчика
2. Пишем на SQL простейший запрос, показывающий количество строк в основной таблице с отбором по датам, подлежащим удалению. Напоминаю, что даты в SQL в применении к 1С для обеспечения совместимости хранятся со сдвигом на 2000, то есть, 2022 год, например, хранится как 4022 год. Данный запрос нам пригодится для контроля процесса удаления.
3. Усложняем запрос, добавляя подчиненные таблицы. Для этого ищем "подчиненные" записи, ссылающиеся на основную таблицу по ID.
4. Записи то мы получили, а дальше? Как будем удалять? Очевидно, нужен какой-то цикл, в котором мы переберем все записи и последовательно их удалим. На помощь приходит нехитрый, но коварный метод SQL #курсор. Здесь мы пишем цикл, в котором позиционируемся сначала на первой записи к удалению, а затем в цикле обходим остальные. Итоговый скрипт выглядит так:
DECLARE @T2ID BINARY(16)
DECLARE @T3ID BINARY(16)
DECLARE @T4ID BINARY(16)
DECLARE @T5ID BINARY(16)
DECLARE @T6ID BINARY(16)
-- обьявляем курсор
declare some_cursor cursor
--- sql запрос любой сложности, формирующий набор данных для курсора
for
--SELECT TOP (100) _IDRRef
SELECT _IDRRef
FROM _Reference15171
WHERE (_Fld16591 = 0x00) AND (_Fld16584 <= '4018-12-31 23:59:59') AND (_Fld18088RRef = 0x942E92E9141DD64F4A430ECEC614FC8C)
ORDER BY _Fld16584
-- открываем курсор
open some_cursor
-- курсор создан, обьявляем переменные и обходим набор строк в цикле
declare @counter int
declare @MainID Binary(16)
set @counter = 0
-- выборка первой строки
fetch next from some_cursor INTO @MainID
-- цикл с логикой и выборкой всех последующих строк после первой
while @@FETCH_STATUS = 0
begin
--- логика внутри цикла
set @counter = @counter + 1
--if @counter >= 5 break -- возможный код для проверки работы, прерываем после пятой итерации
-- отладочный select, на большом количестве строк выборка данных в sql server management studio может привести к ошибке переполнения памяти
--SELECT @int_var, @string_var
--INSERT INTO OTHER_TABLE (SOME_FIELD1, SOME_FIELD2) VALUES (@string_var, 'Мегастрока')
--DELETE FROM OTHER_TABLE2 WHERE ID_FIELD = @int_var
--exec some_stored_procedure
print('Основной ID: ') print(@MainID)
DELETE FROM _Reference15171_VT15274 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171_VT15279 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171_VT15286 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171_VT15274 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171_VT15292 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171_VT18349 WHERE _Reference15171_IDRRef = @MainID
DELETE FROM _Reference15171 WHERE _IDRRef = @MainID
-- выборка следующей строки
fetch next from some_cursor INTO @MainID
-- завершение логики внутри цикла
end
select @counter as final_count
-- закрываем курсор
close some_cursor
deallocate some_cursor
Ну что же, решение найдено, оно вполне рабочее. По времени удаление 300000+ строк с подчиненными таблицами заняло около 20 минут. Во избежание конфликтов это было проделано в нерабочее время.
Итоговый размер базы, разумеется, не изменился, но внутри появился, так сказать, искусственно созданный резерв для роста остальных данных таблиц.
#mssql, #1c, #1спредприятие, #субд, #sql, #sqlserver, #sqlcursor, #howto, #1chowto
