Как правильно удалить большой объем записей регистра сведений через MS SQL и средствами 1С

← На главную

В процессе длительной эксплуатации базы данных 1С некоторые регистры сведений (например, ГрафикиРаботыПоВидамВремени, различные регистры версионирования, штрихкодов или соответствий) разрастаются до десятков миллионов строк. Удаление устаревших данных через стандартные формы или простые циклы в коде 1С может занимать недели или приводить к аварийному завершению работы из-за нехватки оперативной памяти. Разберем детально, как устроено хранение регистра сведений в MS SQL Server, почему в базе создается несколько связанных таблиц, и какими способами можно быстро и безопасно очистить устаревшие данные.

Структура хранения регистра сведений в СУБД MS SQL Server

Когда мы получаем структуру хранения базы данных с помощью встроенного языка 1С через функцию ПолучитьСтруктуруХраненияБазыДанных, мы видим две таблицы для одного регистра сведений:

Основная таблица регистра (префикс _InfoRg) — хранит фактические данные регистра (период, измерения, ресурсы и реквизиты).
Таблица регистрации изменений (префикс _InfoRgChng) — содержит информацию об измененных записях для узлов планов обмена (РИБ или универсальные обмены). Если регистр включен в состав хотя бы одного плана обмена, платформа фиксирует в этой таблице факт добавления, изменения или удаления данных для каждого узла-получателя.

Проанализируем риски прямого вмешательства в базу данных через MS SQL. Перед выполнением прямых SQL-запросов важно учесть следующие нюансы:

Смещение дат: Если при создании информационной базы 1С было включено смещение дат (2000 лет), поле _Period в таблице _InfoRg сохраняет дату 2020-01-01 в формате 4020-01-01. Любые условия фильтрации в SQL-запросе должны учитывать это смещение.
Синхронизация обменов: Прямое удаление строк из основной таблицы минует платформенные механизмы, поэтому записи об удалении не попадут в таблицу _InfoRgChng. Если в базе используются планы обмена, узлы-приемники не узнают об удалении данных.
Кэш сервера 1С: Сервер приложений кэширует данные. После прямых изменений в таблицах СУБД рекомендуется выполнить сброс кэша или перезапустить рабочий процесс rphost.

Способ 1. Порционное удаление данных через T-SQL в MS SQL Server

Удаление миллионов записей одной командой DELETE вызовет блокировку всей таблицы (Lock Escalation) и лавинообразный рост журнала транзакций (.ldf). Чтобы этого избежать, удаление выполняют небольшими пакетами в цикле.

Разберем пошаговый план действий администратора СУБД:

Выполняем полный сеанс обмена данными, чтобы очистить зарегистрированные изменения в таблице _InfoRgChng (если они есть).
Создаем резервную копию базы данных.
Временно переводим базу данных в простую модель восстановления (Simple Recovery Model), чтобы журнал транзакций автоматически очищался при фиксации транзакций: ALTER DATABASE [ИмяБазы1С] SET RECOVERY SIMPLE;

Запускаем скрипт порционного удаления строк через T-SQL. Посмотрим на пример безопасного скрипта:


SET NOCOUNT ON;
DECLARE @RowsDeleted INT = 1;

WHILE @RowsDeleted > 0
BEGIN
    DELETE TOP (5000) 
    FROM _InfoRg12345 -- Имя таблицы регистра из ПолучитьСтруктуруХраненияБазыДанных
    WHERE _Period < '40200101'; -- Условие с учетом смещения дат (2000 лет)

    SET @RowsDeleted = @@ROWCOUNT;
    
    -- Пауза для снижения нагрузки на дисковую подсистему
    WAITFOR DELAY '00:00:00.500';
END;

После завершения операции перестраиваем индексы для устранения внутренней фрагментации страниц: ALTER INDEX ALL ON _InfoRg12345 REBUILD;
Возвращаем полную модель восстановления (Full Recovery Model) и делаем полную резервную копию: ALTER DATABASE [ИмяБазы1С] SET RECOVERY FULL;

Способ 2. Оптимизированная очистка средствами платформы 1С

Прямой доступ к СУБД не всегда возможен из-за политик безопасности. Кроме того, штатными средствами 1С можно добиться высокой скорости, если не считывать данные в память, а записывать пустые наборы записей с установленным отбором.

Рассмотрим, как правильно организовать удаление без перегрузки оперативной памяти. Запись пустого набора записей с отбором физически удаляет только те строки, которые соответствуют отбору, не требуя чтения информации в память клиента или сервера:


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ РАЗЛИЧНЫЕ
    |	Графики.ГрафикРаботы КАК ГрафикРаботы,
    |	Графики.Период КАК Период
    |ИЗ
    |	РегистрСведений.ГрафикиРаботыПоВидамВремени КАК Графики
    |ГДЕ
    |	Графики.Период < &ГраничнаяДата";

Запрос.УстановитьПараметр("ГраничнаяДата", Дата("20200101"));
Выборка = Запрос.Выполнить().Выбрать();

Пока Выборка.Следующий() Цикл
    Набор = РегистрыСведений.ГрафикиРаботыПоВидамВремени.СоздатьНаборЗаписей();
    
    // Устанавливаем точный отбор по измерениям и периоду
    Набор.Отбор.Период.Установить(Выборка.Период);
    Набор.Отбор.ГрафикРаботы.Установить(Выборка.ГрафикРаботы);
    
    // Отключаем бизнес-логику и регистрацию изменений в планах обмена
    Набор.ОбменДанными.Загрузка = Истина;
    
    // Запись пустого набора удаляет записи из базы
    Набор.Записать(Истина);
КонецЦикла;

Проанализируем ключевые моменты этого кода:

Свойство ОбменДанными.Загрузка = Истина отключает платформенные проверки, подписки на события и запись в таблицу изменений _InfoRgChng, что ускоряет удаление в несколько раз.
Установка точных отборов позволяет платформе транслировать операцию в быстрый точечный SQL-запрос DELETE FROM _InfoRg... WHERE ....
Для максимальной производительности процедуру можно разделить на параллельные потоки с помощью механизма ФоновыеЗадания.Выполнить(), передавая в каждый поток отдельный список значений измерений.

Полная очистка регистра (вариант TRUNCATE)

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

В MS SQL: Команда TRUNCATE TABLE _InfoRgXXXX; мгновенно освобождает все страницы данных таблицы без записи построчных операций в журнал транзакций. Таблицу _InfoRgChngXXXX также можно очистить командой TRUNCATE TABLE.
В 1С: Создается пустой РегистрСведенийНаборЗаписей без установки каких-либо отборов и вызывается метод Набор.Записать(Истина); со свойством Набор.ОбменДанными.Загрузка = Истина.