Как ускорить выполнение запроса к остаткам при массовом перепроведении документов в 1С?

← На главную

При работе с крупными информационными базами, такими как 1С:Управление торговым предприятием (УТП), часто возникает задача массового перепроведения документов за длительный период. Одной из самых ресурсоемких операций в этом процессе является выполнение запросов к виртуальным таблицам остатков внутри цикла проведения. Рассмотрим подробнее, как можно оптимизировать этот процесс, почему возникают задержки и какие архитектурные подходы помогут сократить время выполнения задачи в разы.

Анализируем проблему: почему запрос в цикле — это плохо?

Выполнение любого запроса в цикле (для каждого документа) — это классическая антипаттерн-ситуация в разработке на платформе 1С. Разберем по шагам, что происходит на техническом уровне:

Обращение к СУБД: Каждый вызов метода Запрос.Выполнить() инициирует сетевой пакет от сервера приложений к SQL-серверу. При перепроведении 10 000 документов мы получаем 10 000 лишних сетевых взаимодействий.
Расчет итогов "на лету": Платформа 1С хранит итоги регистров на начало каждого месяца. Если запрос выполняется на середину месяца (на МоментВремени документа), система вынуждена брать актуальные итоги на начало месяца и прибавлять к ним все движения из физической таблицы регистра до указанной даты. Чем дальше документ от начала месяца, тем медленнее работает запрос.
План запроса: Для каждого нового вызова SQL-сервер должен заново подбирать оптимальный план выполнения, что также создает нагрузку на процессор сервера БД.

Шаг 1: Оптимизация структуры и текста запроса

Проанализируем исходный запрос и уберем из него избыточные конструкции, которые замедляют работу. Виртуальные таблицы остатков в 1С спроектированы так, что они уже возвращают сгруппированные данные по измерениям. Использование ключевого слова СГРУППИРОВАТЬ ПО и ИТОГИ ПО в таких запросах чаще всего избыточно.

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


ВЫБРАТЬ
    Остатки.Счет КАК СчетУчета,
    Остатки.Субконто1 КАК Номенклатура,
    Остатки.Субконто2 КАК Склад,
    Остатки.Организация КАК Организация,
    Остатки.НалоговоеНазначение КАК НалоговоеНазначение,
    Остатки.КоличествоОстатокДт КАК Количество,
    Остатки.СуммаОстатокДт КАК Стоимость,
    Остатки.СуммаНУОстатокДт КАК СтоимостьНУ
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(
        &МоментКон, 
        Счет В (&СчетаУчетаНС), 
        &ВидыСубконтоНС,
        Организация = &Организация 
        И Субконто1 В (&СписокНоменклатуры) 
        И Субконто2 В (&СписокСкладов)
    ) КАК Остатки

Важные замечания по тексту запроса:

Мы удалили СГРУППИРОВАТЬ ПО, так как виртуальная таблица Остатки уже выполняет свертку данных по выбранным измерениям (Субконто).
Мы удалили расчет ВЫБОР КОГДА ... ЕСТЬ NULL. В виртуальных таблицах остатков поля ресурсов обычно не принимают значение NULL, если запись существует.
Поле Организация является разделителем данных. Крайне важно передавать его в параметры виртуальной таблицы, чтобы SQL-сервер мог эффективно использовать кластерный индекс.

Шаг 2: Использование Временных Таблиц вместо длинных списков в параметрах

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

Проанализируем пример использования Временной Таблицы (ВТ):


// 1. Помещаем номенклатуру во временную таблицу
Запрос.Текст = 
"ВЫБРАТЬ Ссылка ПОМЕСТИТЬ ВТ_Номенклатура ИЗ &МассивНоменклатуры КАК Ссылка;
|ИНДЕКСИРОВАТЬ ПО ВТ_Номенклатура.Ссылка;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ Остатки.Субконто1, Остатки.КоличествоОстатокДт 
|ИЗ РегистрБухгалтерии.Хозрасчетный.Остатки(&Момент, Счет = &Счет, , ) КАК Остатки
|ГДЕ Остатки.Субконто1 В (ВЫБРАТЬ Ссылка ИЗ ВТ_Номенклатура)";

Индексация временной таблицы позволяет оптимизатору SQL применить операцию Nested Loops или Hash Join, что значительно ускоряет выборку данных.

Шаг 3: Радикальное ускорение — кэширование остатков в Таблице Значений

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

Разберем алгоритм реализации этого метода:

Перед началом цикла перепроведения (или для пачки документов) мы одним запросом собираем всю необходимую номенклатуру и склады из всех документов.
Получаем остатки по всем этим позициям на начальную дату проведения.
Выгружаем результат в ТаблицаЗначений и индексируем её по ключевым полям (Счет, Номенклатура, Склад).
Внутри цикла вместо обращения к базе данных мы ищем строки в этой таблице с помощью метода НайтиСтроки().
После обработки каждого документа мы программно обновляем (корректируем) данные в нашей ТаблицеЗначений на величину движений, которые сделал документ.

Такой подход позволяет выполнить всего один запрос к базе данных вместо тысяч, что сокращает время работы обработки в десятки раз. Для передачи этой таблицы в контекст документа (если используется стандартная обработка проведения) можно использовать ДополнительныеСвойства объекта.

Шаг 4: Проверка актуальности итогов регистра

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

Рекомендуется выполнить следующие действия перед началом массовых работ:

Убедиться, что в 1С включено использование итогов (через Операции — Управление итогами).
Установить период рассчитанных итогов на актуальную дату.
Выполнить регламентную операцию "Пересчет итогов" для регистра Хозрасчетный.
На уровне SQL-сервера выполнить UPDATE STATISTICS для таблиц регистра, чтобы оптимизатор имел актуальные данные о распределении значений.

Итоги

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