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

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

Почему удаление средствами 1С работает медленно?

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

Для Каждого Ключ Из Ключи Цикл
    Набор.Отбор.Ключ.Установить(Ключ);
    Набор.Записать();
КонецЦикла;

Даже если распараллелить этот процесс с помощью фоновых заданий, производительность оставляет желать лучшего. Проблема кроется в нескольких факторах. Во-первых, объект НаборЗаписей поддерживает отборы только на равенство. На каждую уникальную комбинацию измерений формируется отдельный запрос к СУБД на чтение и отдельный на запись. Во-вторых, при записи срабатывают события ПередЗаписью и ПриЗаписи, могут вызываться подписки на события и производиться регистрация изменений для узлов обмена. Чтобы ускорить типовое удаление, необходимо программно устанавливать свойство ОбменДанными.Загрузка = Истина и объединять операции в транзакции по 5-10 тысяч записей. Но даже при этих оптимизациях для 150 миллионов строк процесс может занять сутки и более.

Решение через прямой SQL-запрос и типичная синтаксическая ошибка

Посмотрим на пример прямого удаления через MS SQL. Идея состоит в том, чтобы использовать подзапрос с условием по дате. Однако, часто при написании подобных конструкций возникает ошибка синтаксиса. Рассмотрим пример такого неудачного запроса:

DELETE FROM [Test].[dbo].[_InfoRg1960]
WHERE [_Fld1961] IN (
    SELECT [_Fld1948], [_Fld1951]
    FROM [Test].[dbo].[_InfoRg1947]
    WHERE [_Fld1948] < '4015-08-01'
)

Выполнение такого кода приведет к ошибке: "В списке выбора можно указать только одно выражение, если вложенный запрос не предварен EXISTS". Причина кроется в том, что оператор IN ожидает для сравнения один столбец, а во вложенном запросе указано два ([_Fld1948] и [_Fld1951]). Выясним причину и исправим ошибку, оставив в подзапросе только поле ключа:

DELETE FROM [Test].[dbo].[_InfoRg1960]
WHERE [_Fld1961] IN (
    SELECT [_Fld1948]
    FROM [Test].[dbo].[_InfoRg1947]
    WHERE [_Fld1948] < '4015-08-01'
)

После этого исправления запрос отработает, и скорость удаления возрастет на порядки: то, что в 1С занимало 15–25 минут, на уровне SQL выполняется за 10–15 секунд.

Оптимизация массового удаления: порционность и транзакции

Удаление 150 миллионов строк одним запросом DELETE вызовет колоссальный рост журнала транзакций (Transaction Log) и может привести к зависанию базы или исчерпанию места на диске. Рассмотрим методы, как сделать процесс безопасным.

1. Порционное удаление: Используйте конструкцию TOP. Например, удаляйте по 100 000 строк в цикле. Это позволит контролировать размер транзакции. Пример синтаксиса: DELETE TOP (100000) FROM ... WHERE ...
2. Задержки цикла: Если очистка идет на "живой" базе, где работают пользователи, непрерывный цикл удаления заблокирует таблицу. Добавьте в итерацию команду WAITFOR DELAY '00:00:02', чтобы давать серверу время на обработку других запросов.
3. Управление журналом транзакций: Перед масштабной чисткой переведите базу в простую модель восстановления (Simple Recovery Model). После завершения работ сожмите лог с помощью DBCC SHRINKFILE и верните полную модель восстановления (Full).

Экстремальные методы: TRUNCATE, временные таблицы и BCP

Если из таблицы в 200 миллионов записей требуется удалить 150 миллионов, стандартный оператор DELETE может быть не лучшим выбором. Разберем альтернативные и значительно более быстрые подходы, которые не пишут каждую удаляемую строку в лог транзакций.

Метод 1: Перенос и очистка (SELECT INTO + TRUNCATE)

Вместо удаления лишнего, мы сохраняем нужное. Этот метод идеален для свертки баз:

1. Создаем временную таблицу и копируем в нее те записи, которые нужно оставить: SELECT * INTO TempTable FROM TargetTable WHERE ...
2. Мгновенно очищаем исходную таблицу, не нагружая лог транзакций: TRUNCATE TABLE TargetTable
3. Возвращаем нужные записи обратно: INSERT INTO TargetTable SELECT * FROM TempTable

Метод 2: Утилита BCP (Bulk Copy Program)

Аналог первого метода, но для случаев, когда временная таблица не помещается в оперативной памяти сервера. Оставляемые данные выгружаются быстрым потоком в текстовый файл на диск через утилиту командной строки bcp. Затем таблица очищается командой TRUNCATE, и данные заливаются обратно через BULK INSERT.

Важные действия после масштабного удаления

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

1. Обновление статистики: После удаления более 20% записей из таблицы, статистика SQL сервера безнадежно устаревает. Оптимизатор может начать строить неэффективные планы запросов. Обязательно выполните команду UPDATE STATISTICS для очищенной таблицы.
2. Перестроение индексов: При массовом удалении индексы фрагментируются. Рекомендуется выполнить ALTER INDEX ALL ON [ИмяТаблицы] REBUILD. К слову, полезным трюком перед удалением является временное отключение некластерных индексов (Disable) с их последующим перестроением, что ускорит сам процесс очистки.

Используя комбинированный подход — перенос логики отборов на уровень СУБД, управление транзакционным логом и методы массовой вставки/очистки (TRUNCATE), — вы сможете выполнять регламентные задачи по очистке данных безопасно и в сотни раз быстрее, чем это предлагают типовые объектные механизмы платформы 1С.