Как безопасно и эффективно удалить большие объемы данных из базы 1С v7 прямым SQL-запросом частями, избегая "падения" системы?

← На главную

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

Выясняем причину "падения" 1С при массовом удалении

Исходная проблема, с которой сталкивается автор, заключается в "падении" 1С-ки при выполнении следующего запроса:


ТекстЗапроса = "
|DELETE
|   _1SUPDTS
|FROM
|   _1SUPDTS as U          
|INNER JOIN 
|   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID and U.DBSIGN = тбУдалять.DBSIGN";
RS.ВыполнитьИнструкцию(ТекстЗапроса);

Когда мы выполняем такой запрос без ограничений на объем удаляемых данных, СУБД (например, SQL Server) пытается удалить все соответствующие записи за одну транзакцию. Это может привести к следующим проблемам:

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

Для решения этих проблем мы должны выполнять удаление данных не за один раз, а частями, итеративно, небольшими порциями.

Решение проблемы: удаление данных частями с использованием DELETE TOP N

Самый распространенный и эффективный способ избежать вышеуказанных проблем при массовом удалении — это выполнять операцию DELETE по частям. Рассмотрим, как это можно реализовать.

1. Использование оператора DELETE TOP N

Для удаления данных частями в SQL Server мы можем использовать оператор TOP N в сочетании с DELETE. Это позволяет удалить только заданное количество строк за один раз. Первоначальная идея автора была верна в этом направлении:


ТекстЗапроса = "
|DELETE TOP 1000
|   _1SUPDTS
|FROM
|   _1SUPDTS as U          
|INNER JOIN 
|   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID and U.DBSIGN = тбУдалять.DBSIGN";

Здесь TOP 1000 указывает, что будет удалено не более 1000 строк, соответствующих условию INNER JOIN. Это значительно снижает нагрузку на СУБД и 1С, так как каждая операция становится короткой и менее ресурсоемкой.

2. Корректная обработка результата функции ВыполнитьИнструкцию в 1С v7

Ключевым моментом для итеративного удаления является правильное определение, когда операция завершена. Автор первоначально предполагал, что функция RS.ВыполнитьИнструкцию() при DELETE возвращает таблицу, и использовал тб.КоличествоСтрок() для проверки. Однако, для операций DML (DELETE, UPDATE, INSERT) функция RS.ВыполнитьИнструкцию() в 1С v7 возвращает не таблицу, а число обработанных записей.

Следовательно, цикл должен быть построен таким образом:


// ... (предварительная подготовка #тбУдалять) ...

КоличествоУдаленныхСтрок = -1; // Инициализируем для входа в цикл

Пока КоличествоУдаленныхСтрок <> 0 Цикл
    ТекстЗапроса = "
    |DELETE TOP 1000
    |   _1SUPDTS
    |FROM
    |   _1SUPDTS as U          
    |INNER JOIN 
    |   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID and U.DBSIGN = тбУдалять.DBSIGN";
    
    Попытка
        КоличествоУдаленныхСтрок = RS.ВыполнитьИнструкцию(ТекстЗапроса);
        Сообщить("Удалено " + КоличествоУдаленныхСтрок + " строк.");
    Исключение
        Сообщить("Ошибка при удалении: " + ОписаниеОшибки());
        Прервать; // Прерываем цикл в случае ошибки
    КонецПопытки;
    
    // Возможно, добавить ЗафиксироватьТранзакцию() и СоздатьОбъект("ODBC.Connection")
    // внутри цикла для управления транзакциями, если это не делается автоматически.
    // Однако, каждая инструкция SQL является отдельной транзакцией по умолчанию в ODBC/ADO,
    // если только не был явно открыт RS.НачатьТранзакцию().
КонецЦикла;

// ... (очистка #тбУдалять, если необходимо) ...

В этом цикле мы продолжаем выполнять запрос до тех пор, пока RS.ВыполнитьИнструкцию() не вернет 0, что означает, что больше строк, соответствующих условию, не найдено и удаление завершено.

Критически важный аспект: корректность идентификаторов в 1С v7 (_1SUPDTS)

Помимо механизма удаления частями, крайне важно обеспечить корректность самого запроса, особенно когда мы работаем с системными таблицами 1С v7, такими как _1SUPDTS. Эта таблица хранит значения периодических реквизитов объектов 1С.

Обратим внимание на сообщение 9, которое указывает на серьезную потенциальную ошибку в исходном запросе автора. Поле OBJID само по себе не является уникальным идентификатором объекта в 1С v7. Для документов он сквозной для всех видов документов, а для справочников уникален только в пределах конкретного справочника. Это означает, что один и тот же OBJID может принадлежать совершенно разным типам объектов (например, элементу справочника "Номенклатура" и документу "Расходная накладная").

Полный уникальный идентификатор объекта в 1С v7, как правило, состоит из трех полей:

DBSIGN: Идентификатор базы данных (используется в распределенных ИБ).
TYPEID: Идентификатор типа объекта (например, справочника, документа).
OBJID: Идентификатор экземпляра объекта внутри своего типа.

Если мы удаляем записи из _1SUPDTS, используя только DBSIGN и OBJID, мы рискуем удалить периодические реквизиты, относящиеся к другим объектам, которые имеют тот же OBJID, но другой TYPEID. Это может привести к серьезному нарушению целостности данных.

Для безопасного и корректного удаления необходимо модифицировать условие JOIN, включив в него поле TYPEID:


ТекстЗапроса = "
|DELETE TOP 1000
|   _1SUPDTS
|FROM
|   _1SUPDTS as U          
|INNER JOIN 
|   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID 
|                            and U.DBSIGN = тбУдалять.DBSIGN
|                            and U.TYPEID = тбУдалять.TYPEID"; // КЛЮЧЕВОЕ ИЗМЕНЕНИЕ

Перед тем как начать удаление, убедитесь, что ваша временная таблица #тбУдалять также содержит поле TYPEID для всех объектов, которые вы собираетесь удалять, и что вы правильно формируете этот идентификатор в вашей логике.

Дополнительные рекомендации по оптимизации и безопасности

Мы предлагаем рассмотреть ряд дополнительных мер, которые помогут сделать процесс удаления больших объемов данных еще более эффективным и безопасным.

1. Использование SET NOCOUNT ON

Команда SET NOCOUNT ON в SQL Server предотвращает возврат сообщений о количестве затронутых строк после выполнения каждой DML-операции. Это может существенно уменьшить сетевой трафик между 1С и SQL Server, особенно в цикле, где запрос выполняется многократно. Мы рекомендуем добавлять эту команду в начале вашего SQL-запроса:


ТекстЗапроса = "
|SET NOCOUNT ON;
|
|DELETE TOP 1000
|   _1SUPDTS
|FROM
|   _1SUPDTS as U          
|INNER JOIN 
|   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID 
|                            and U.DBSIGN = тбУдалять.DBSIGN
|                            and U.TYPEID = тбУдалять.TYPEID";

2. Оптимизация с помощью индексов

Производительность операций DELETE, особенно с условиями JOIN, сильно зависит от наличия и эффективности индексов на участвующих таблицах. Убедитесь, что на таблице _1SUPDTS существуют индексы по полям, используемым в условии JOIN: DBSIGN, TYPEID и OBJID. Если таких индексов нет или они не оптимальны, их создание значительно ускорит выполнение запроса. Аналогично, временная таблица #тбУдалять также должна иметь индексы по этим полям для быстрого соединения.

Мы можем создать временный индекс на #тбУдалять, если он не создается автоматически, например:


// Предполагаем, что #тбУдалять уже создана и заполнена
ТекстЗапросаИндекса = "
|CREATE CLUSTERED INDEX IX_тбУдалять_ID ON #тбУдалять (DBSIGN, TYPEID, OBJID);";
RS.ВыполнитьИнструкцию(ТекстЗапросаИндекса);

// ... далее основной цикл удаления ...

// После завершения удаления, удалить временный индекс
ТекстЗапросаУдаленияИндекса = "DROP INDEX IX_тбУдалять_ID ON #тбУдалять;";
RS.ВыполнитьИнструкцию(ТекстЗапросаУдаленияИндекса);

3. Управление размером порции (N)

Значение N в DELETE TOP N (в нашем примере 1000) следует подбирать экспериментально. Слишком маленькое значение приведет к большому количеству обращений к СУБД, что увеличит накладные расходы. Слишком большое значение может снова вызвать проблемы с блокировками и журналом транзакций. Оптимальное значение часто находится в диапазоне от нескольких сотен до нескольких тысяч, в зависимости от конфигурации сервера, скорости дисков и активности других пользователей. Мы рекомендуем начать с 1000 и корректировать по мере тестирования.

4. Оборачивание операций в транзакцию

Хотя каждая операция DELETE TOP N по умолчанию выполняется как отдельная микротранзакция, для более сложных сценариев или при необходимости объединить несколько операций удаления в одну логическую единицу, мы всегда рекомендуем явно оборачивать действия в транзакцию 1С (RS.НачатьТранзакцию(), RS.ЗафиксироватьТранзакцию(), RS.ОтменитьТранзакцию()). Однако, в случае итеративного удаления частями, это может потребовать управления транзакциями для каждой порции, что иногда усложняет логику, но обеспечивает большую отказоустойчивость. Если не используется явное управление, каждая инструкция ВыполнитьИнструкцию будет атомарной.

5. Альтернативные подходы к выбору порций

Помимо DELETE TOP N с INNER JOIN на временную таблицу, существуют и другие методы выбора порций для удаления:

Выбор первых 1000 ID во временную таблицу: Мы можем сначала выбрать TOP N уникальных идентификаторов в другую временную таблицу, а затем использовать ее для JOIN. Это может быть полезно, если условие JOIN очень сложное. Пример, как предлагает Сообщение 2:


// Выбираем OBJID, DBSIGN, TYPEID первых 1000 строк для удаления
ТекстЗапросаВыбора = "
|SELECT TOP 1000 U.OBJID, U.DBSIGN, U.TYPEID
|INTO #тбУдалитьПорцию
|FROM _1SUPDTS as U          
|INNER JOIN 
|   #тбУдалять as тбУдалять ON U.OBJID = тбУдалять.OBJID 
|                            and U.DBSIGN = тбУдалять.DBSIGN
|                            and U.TYPEID = тбУдалять.TYPEID;
|
|DELETE _1SUPDTS
|FROM _1SUPDTS as U
|INNER JOIN #тбУдалитьПорцию as тбУдалитьПорцию 
|   ON U.OBJID = тбУдалитьПорцию.OBJID 
|   and U.DBSIGN = тбУдалитьПорцию.DBSIGN
|   and U.TYPEID = тбУдалитьПорцию.TYPEID;
|
|DROP TABLE #тбУдалитьПорцию;";
    
// Этот запрос выполняется в цикле, возвращая количество удаленных строк
// КоличествоУдаленныхСтрок = RS.ВыполнитьИнструкцию(ТекстЗапросаВыбора);

Данный подход гарантирует, что TOP N применяется именно к уникальным объектам для удаления, а не к результату JOIN, что иногда может давать отличающийся результат.

Использование общего табличного выражения (CTE): Для более сложных логик удаления можно использовать CTE. Однако, для пакетных операций с очень большими объемами данных временные таблицы часто показывают лучшую производительность, так как их можно индексировать.

6. Особенности работы с архитектурой БД 1С v7

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

Мы должны быть особенно осторожны при работе с системными таблицами (например, _1SCONST, _1SJOURN, _1SUIDCTL, _1SOPER, _1SENTRY) и таблицами документов/справочников (DH*, DT*, SC*), всегда проверяя, какие поля формируют уникальный ключ.

Заключение

Мы установили, что успешное и безопасное удаление больших объемов данных из базы 1С v7 прямыми SQL-запросами требует не только использования итерационного подхода с DELETE TOP N, но и глубокого понимания структуры данных 1С и особенностей работы СУБД. Корректное определение уникальных идентификаторов (включая DBSIGN, TYPEID и OBJID), применение индексов и использование SET NOCOUNT ON являются критически важными шагами для предотвращения "падений" системы и обеспечения целостности данных. Мы призываем всегда тестировать такие операции на тестовых базах данных перед применением на рабочих системах.