Как очистить регистр накопления за период средствами SQL на больших базах данных

← На главную

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

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

Архитектура таблиц регистра накопления в СУБД

Прежде чем выполнять какие-либо низкоуровневые операции в SQL Server, нам необходимо понять, как именно регистр накопления устроен на уровне базы данных. В СУБД один регистр накопления представлен не одной, а целым семейством взаимосвязанных таблиц. Если мы очистим только движения, данные в других таблицах станут некорректными.

Рассмотрим структуру таблиц регистра накопления:

_AccumRg<X> — основная таблица движений регистра. Именно здесь хранятся физические записи, которые делают документы при проведении. Её очистку мы и будем производить.
_AccumRgT<X> — таблица итогов остатков (присутствует только у регистров остатков). В ней хранятся рассчитанные остатки в разрезе измерений на конец каждого месяца.
_AccumRgB<X> — таблица периодических оборотов (присутствует у регистров оборотов и у некоторых регистров остатков, если для них включены обороты).
_AccumRgOpt<X> — таблица настроек итогов, где хранится период, по который рассчитаны итоги, и флаги их использования.
_AccumRgChrg<X> — таблица регистрации изменений. Она создается, если регистр включен в состав хотя бы одного плана обмена для синхронизации данных.

Перед началом работы обязательно нужно определить точное имя физической таблицы в SQL. Для этого в платформе 1С предусмотрен встроенный метод ПолучитьСтруктуруХраненияБазыДанных(). Мы можем программно получить точные имена SQL-таблиц для нашего регистра.

Почему стандартный DELETE FROM — это плохая идея для больших объемов

Первое решение, которое приходит в голову — выполнить простой запрос на удаление записей до определенной даты:

DELETE FROM _AccumRg1234 WHERE _Period < '2024-01-01 00:00:00'

Если в вашей таблице более 100 миллионов записей, а удалить требуется, например, 80 миллионов, такой запрос гарантированно приведет к следующим проблемам:

Переполнение лога транзакций. Оператор DELETE является логируемым. СУБД обязана записать каждую удаляемую строку в транзакционный лог (файл .ldf). Это вызовет лавинообразный рост файла лога, который может занять все свободное место на диске и остановить работу сервера.
Эскалация блокировок. При удалении большого количества строк SQL Server наложит монопольную блокировку на всю таблицу. Ни один пользователь не сможет провести документ или сформировать отчет, использующий этот регистр.
Гигантское время выполнения. Процесс построчного удаления и записи в лог может занять долгие часы.

Рассмотрим два правильных и быстрых альтернативных метода решения этой задачи.

Метод №1: Перенос актуальных данных через временную таблицу (Самый быстрый)

Этот метод основан на использовании оператора TRUNCATE TABLE. Этот оператор мгновенно очищает всю таблицу без детального логирования каждой строки, поскольку он просто освобождает страницы данных. Однако у TRUNCATE есть ограничение: его нельзя использовать с условием WHERE.

Чтобы обойти это ограничение, мы применим изящный алгоритм: сохраним те данные, которые нам нужно оставить, во временную таблицу, полностью очистим основную таблицу, а затем вернем сохраненные данные обратно.

Проанализируем пошаговый алгоритм выполнения этой операции:

Создаем копию структуры таблицы и переносим туда записи, дата которых больше даты нашей очистки (то есть те данные, которые мы хотим сохранить).
Выполняем быструю и безопасную очистку основной таблицы регистра с помощью команды TRUNCATE TABLE.
Возвращаем сохраненные записи из временной таблицы обратно в основную таблицу с помощью операции прямой вставки INSERT INTO.
Удаляем временную таблицу, чтобы освободить место в базе данных.

Посмотрим на пример SQL-скрипта, реализующего данный подход:


-- Шаг 1. Переносим актуальные данные (все, что позже 01.01.2024) во временную таблицу
SELECT * INTO _AccumRg1234_Temp 
FROM _AccumRg1234 
WHERE _Period >= '2024-01-01 00:00:00';

-- Шаг 2. Мгновенно и без логов очищаем основную таблицу регистра
TRUNCATE TABLE _AccumRg1234;

-- Шаг 3. Возвращаем актуальные данные обратно в основную таблицу
INSERT INTO _AccumRg1234 
SELECT * FROM _AccumRg1234_Temp;

-- Шаг 4. Удаляем временную таблицу из базы данных
DROP TABLE _AccumRg1234_Temp;

Этот метод выполняется в десятки и сотни раз быстрее, чем обычный DELETE, так как операции записи в лог транзакций минимизированы.

Метод №2: Порционное удаление данных (Если не хватает места на диске)

Если на вашем сервере нет достаточного количества свободного дискового пространства для создания временной копии таблицы, метод с TRUNCATE может не подойти. В таком случае мы можем использовать порционное удаление данных в цикле. Мы будем удалять данные небольшими "пакетами" (например, по 50 000 строк) в рамках отдельных транзакций.

При использовании простой модели восстановления базы данных (Simple recovery model) лог транзакций будет автоматически очищаться после фиксации каждой порции данных, что предотвратит раздувание файла .ldf и защитит базу от монопольных блокировок.

Рассмотрим пример SQL-запроса для порционного удаления:


DECLARE @RowsDeleted INT;
SET @RowsDeleted = 1;

WHILE (@RowsDeleted > 0)
BEGIN
    BEGIN TRANSACTION;
    
    -- Удаляем данные порциями по 50 000 записей за один проход
    DELETE TOP (50000) 
    FROM _AccumRg1234 
    WHERE _Period < '2024-01-01 00:00:00';
    
    SET @RowsDeleted = @@ROWCOUNT;
    
    COMMIT TRANSACTION;
    
    -- Небольшая пауза для снижения нагрузки на дисковую подсистему
    WAITFOR DELAY '00:00:01'; 
END;

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

Критически важный шаг: Управление итогами на стороне 1С

Очистка основной таблицы движений регистра накопления средствами SQL — это лишь половина дела. Если мы просто удалим движения, таблицы итогов (_AccumRgT<X> и _AccumRgB<X>) останутся в прежнем состоянии. База данных получит рассогласование: движения за старые периоды исчезнут, но остатки по ним все еще будут числиться в таблицах итогов. При построении отчетов пользователи увидят недостоверные или пустые данные.

Чтобы избежать этой проблемы, нам необходимо правильно выполнить отключение и последующий пересчет итогов средствами самой платформы 1С. Выясним, как сделать это наиболее эффективно.

Перед выполнением SQL-скрипта очистки нам нужно полностью отключить использование итогов для очищаемого регистра. Это действие на уровне СУБД полностью очистит таблицы итогов и снимет лишние триггеры, что также колоссально ускорит наш SQL-запрос на удаление. После завершения работы с SQL мы снова включим итоги — платформа автоматически пересчитает их с нуля на основании оставшихся в основной таблице движений.

Разберем по шагам код на языке 1С, который необходимо выполнить в обработке:


// Шаг 1. Отключаем использование итогов для регистра. 
// Это автоматически очистит таблицы итогов в SQL.
РегистрыНакопления.ИмяВашегоРегистра.УстановитьИспользованиеИтогов(Ложь);

// Шаг 2. Здесь мы запускаем наш SQL-скрипт очистки основной таблицы движений.
// Вы можете выполнить его через ADOConnection или запустить внешнюю утилиту.
ВыполнитьОчисткуТаблицыДвиженийНаУровнеSQL();

// Шаг 3. Включаем использование итогов обратно.
// Платформа 1С создаст новые чистые таблицы итогов и рассчитает их заново 
// только по тем движениям, которые остались в базе данных.
РегистрыНакопления.ИмяВашегоРегистра.УстановитьИспользованиеИтогов(Истина);

Такой подход гарантирует 100% корректность рассчитанных остатков и оборотов после проведения агрессивной очистки СУБД.

Опасность для регистров остатков и обязательный ввод начальных остатков

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

Например, если товар поступил на склад в 2023 году, а списан в 2024 году, то после удаления движений за 2023 год у нас останется только расход в 2024 году. В результате остаток по этому товару уйдет в огромный минус.

Решение: Перед выполнением физической очистки движений в СУБД необходимо обязательно зафиксировать актуальные остатки на дату среза (дату очистки). Для этого на дату среза создается специальный документ (например, "Ввод остатков" или "Корректировка регистров"), который фиксирует текущие остатки. Движения этого корректирующего документа удалять нельзя — они станут нашей новой отправной точкой. Только после создания и проведения документов ввода остатков можно приступать к очистке исторических движений.

Очистка таблиц регистрации изменений (Планы обмена)

Если очищаемый регистр накопления участвует в обмене данными с другими информационными базами (входит в состав планов обмена), то при удалении записей напрямую через SQL на уровне платформы 1С не сработают механизмы автоматического снятия регистрации изменений. В таблице _AccumRgChrg<X> останутся "битые" ссылки, указывающие на уже несуществующие движения.

Это может привести к критическим ошибкам при очередной синхронизации данных. Если ваш регистр задействован в планах обмена, перед очисткой движений необходимо очистить также и соответствующую таблицу регистрации изменений. Сделать это можно либо программно средствами 1С (через метод ПланыОбмена.УдалитьРегистрациюИзменений()), либо выполнив аналогичный запрос TRUNCATE TABLE _AccumRgChrg<X> на уровне SQL.

Обслуживание СУБД после завершения очистки

После удаления колоссального объема данных (например, 100 миллионов записей) физическая структура таблиц и индексов на диске сервера СУБД подвергается сильной деградации. Возникает критическая фрагментация индексов (она может достигать 99%), а статистика распределения данных становится абсолютно неактуальной. В результате этого любые последующие запросы к данному регистру будут выполняться крайне медленно.

Поэтому сразу после завершения очистки и пересчета итогов в 1С необходимо запустить процедуры регламентного обслуживания СУБД для измененных таблиц:

Перестроение индексов (Index Rebuild). Это физически переупорядочит данные таблицы на диске, устранит фрагментацию и сожмет свободное пространство внутри файлов базы данных.
Обновление статистики (Update Statistics). Это позволит оптимизатору запросов SQL Server строить эффективные и быстрые планы выполнения запросов на основе новой, актуальной картины распределения данных в таблице.

Выполнение этих простых шагов вернет производительность вашей СУБД на прежний высокий уровень. Для проведения регламентных процедур подойдёт скрипт адаптивного обновления индексов MS SQL.

Важное примечание: Любое прямое изменение данных в таблицах СУБД в обход стандартных механизмов платформы нарушает лицензионное соглашение с компанией 1С и лишает вашу информационную базу официальной технической поддержки. Подобные операции должны выполняться квалифицированными специалистами, исключительно на копиях баз данных и под вашу личную ответственность.