Как оптимизировать запрос получения остатков и цен в 1С (УТ 10.3)?

← На главную

В практике разработки отчетов на платформе 1С:Предприятие часто встречается задача объединения данных об остатках товаров на складах с их текущими ценами. Казалось бы, логика проста: берем остатки и присоединяем срез последних цен. Однако, если реализовать это "в лоб", производительность запроса может оказаться неприемлемо низкой, особенно при росте базы данных. Рассмотрим подробно, почему возникает проблема замедления и разберем два основных способа оптимизации: через синтаксис построителя отчета и через временные таблицы.

Анализ проблемы: почему запрос работает медленно?

Давайте взглянем на типичную ошибку. Разработчик пытается соединить виртуальную таблицу РегистрНакопления.ТоварыНаСкладах.Остатки с виртуальной таблицей РегистрСведений.ЦеныНоменклатуры.СрезПоследних. При этом отбор по номенклатуре накладывается либо в секции ГДЕ основного запроса, либо полагаясь на настройки построителя отчета, которые по умолчанию фильтруют уже итоговую выборку.

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

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

Сначала рассчитать срез последних цен для абсолютно всей номенклатуры, которая есть в базе данных. Это огромная нагрузка.
Затем получить остатки (возможно, тоже по всем товарам, если отбор не передан в параметры остатков).
Соединить эти два огромных массива данных.
И только в самом конце отсечь лишнее по условию ГДЕ.

Наша цель — заставить систему фильтровать данные до их обработки и соединения.

Решение 1: Использование расширений языка запросов для Построителя отчета

Если вы работаете с объектом ПостроительОтчета (что характерно для конфигураций на обычных формах, таких как УТ 10.3), существует элегантный способ пробросить отборы внутрь виртуальных таблиц автоматически. Для этого используется специальный синтаксис с фигурными скобками {}.

Давайте разберем итоговый оптимизированный текст запроса, который решает проблему производительности:


ВЫБРАТЬ
    ОстаткиТоваров.Номенклатура КАК Номенклатура,
    ОстаткиТоваров.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ОстаткиТоваров.Номенклатура.ЕдиницаХраненияОстатков КАК ЕдиницаХранения,
    ОстаткиТоваров.Качество,
    ОстаткиТоваров.КоличествоОстаток КАК Количество,
    ОстаткиТоваров.КоличествоОстаток * ЕСТЬNULL(ЦеныНоменклатурыСрезПоследних.Цена, 0) КАК Сумма
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментДокумента, 
        Склад = &Склад {(Номенклатура) КАК Номенклатура}) КАК ОстаткиТоваров
        
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&МоментДокумента, 
        ТипЦен = &Закупочная {(Номенклатура) КАК Номенклатура}) КАК ЦеныНоменклатурыСрезПоследних
    ПО ОстаткиТоваров.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
        И ОстаткиТоваров.ХарактеристикаНоменклатуры = ЦеныНоменклатурыСрезПоследних.ХарактеристикаНоменклатуры

{ГДЕ
    ОстаткиТоваров.Номенклатура.* КАК Номенклатура,
    ОстаткиТоваров.Номенклатура.НоменклатурнаяГруппа.* КАК НоменклатурнаяГруппа}

Обратим внимание на ключевые изменения. В параметрах виртуальных таблиц появилась конструкция:

{(Номенклатура) КАК Номенклатура}

Что это означает?
Эта инструкция говорит Построителю отчета: "Если пользователь установил отбор по полю 'Номенклатура', то этот отбор нужно поместить прямо сюда, внутрь параметров виртуальной таблицы".

Благодаря этому, при формировании SQL-запроса, 1С генерирует запрос, в котором таблица СрезПоследних рассчитывается только для тех товаров, которые выбрал пользователь. Это сокращает объем выборки в сотни и тысячи раз, обеспечивая мгновенное выполнение.

Решение 2: Использование временных таблиц (Классический подход)

Если мы не используем автоматику ПостроительОтчета или задача требует более сложной логики (например, СКД или прямая выборка в коде), лучшей практикой является использование временных таблиц.

Алгоритм действий следующий:

Выбираем интересующую нас номенклатуру во временную таблицу (ВТ).
Индексируем эту таблицу.
Используем эту ВТ как фильтр в параметрах виртуальных таблиц остатков и цен.

Рассмотрим пример кода для такого подхода:


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

////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Получаем остатки, фильтруя их по нашей ВТ
ВЫБРАТЬ
    Остатки.Номенклатура,
    Остатки.ХарактеристикаНоменклатуры,
    Остатки.КоличествоОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментВремени, 
        Склад = &Склад 
        И Номенклатура В (ВЫБРАТЬ Т.Номенклатура ИЗ ВТ_СписокНоменклатуры КАК Т)) КАК Остатки
ИНДЕКСИРОВАТЬ ПО
    Остатки.Номенклатура,
    Остатки.ХарактеристикаНоменклатуры
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3. Получаем цены, фильтруя их по той же ВТ
ВЫБРАТЬ
    Цены.Номенклатура,
    Цены.ХарактеристикаНоменклатуры,
    Цены.Цена
ПОМЕСТИТЬ ВТ_Цены
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&МоментВремени, 
        ТипЦен = &ТипЦен 
        И Номенклатура В (ВЫБРАТЬ Т.Номенклатура ИЗ ВТ_СписокНоменклатуры КАК Т)) КАК Цены
ИНДЕКСИРОВАТЬ ПО
    Цены.Номенклатура,
    Цены.ХарактеристикаНоменклатуры
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 4. Финальное соединение
ВЫБРАТЬ
    ВТ_Остатки.Номенклатура,
    ВТ_Остатки.КоличествоОстаток,
    ЕСТЬNULL(ВТ_Цены.Цена, 0) КАК Цена,
    ВТ_Остатки.КоличествоОстаток * ЕСТЬNULL(ВТ_Цены.Цена, 0) КАК Сумма
ИЗ
    ВТ_Остатки КАК ВТ_Остатки
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Цены КАК ВТ_Цены
        ПО ВТ_Остатки.Номенклатура = ВТ_Цены.Номенклатура
        И ВТ_Остатки.ХарактеристикаНоменклатуры = ВТ_Цены.ХарактеристикаНоменклатуры

Важные нюансы оптимизации

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

1. Функция ЕСТЬNULL (ISNULL)
При левом соединении с таблицей цен возможна ситуация, когда цена не найдена (вернется NULL). Любая арифметическая операция с NULL (например, умножение на количество) даст в результате NULL. Поэтому обязательно оборачивайте поле цены: ЕСТЬNULL(Цены.Цена, 0).

2. Момент времени
Использование параметра &МоментДокумента для получения остатков корректно и необходимо для контроля отрицательных остатков при проведении. Однако для среза цен это может быть избыточно. Если цены меняются не чаще раза в день, иногда имеет смысл получать цены на начало дня или на текущую дату, чтобы улучшить кэширование планов запросов СУБД, но это зависит от специфики бизнес-процессов.

3. ГДЕ против Параметров
Запомним золотое правило: фильтровать данные нужно как можно раньше.
Плохо: ВЫБРАТЬ ... ИЗ Регистр.Остатки ГДЕ Номенклатура = &Товар (сначала читаем все, потом фильтруем).
Хорошо: ВЫБРАТЬ ... ИЗ Регистр.Остатки(..., Номенклатура = &Товар) (читаем только нужное).

4. Индексы во временных таблицах
Если вы используете временные таблицы для последующего соединения, всегда используйте конструкцию ИНДЕКСИРОВАТЬ ПО. Отсутствие индекса может привести к тому, что соединение таблиц будет выполняться перебором (Table Scan), что уничтожит весь выигрыш от оптимизации.

Резюме

Мы выяснили, что основная причина замедления запросов при соединении с виртуальными таблицами (особенно СрезПоследних) — это отсутствие отборов внутри параметров этих таблиц. Для построителя отчетов в УТ 10.3 идеальным решением является использование конструкции {(Поле) КАК Поле}, которая динамически встраивает отбор. Для более сложных случаев следует использовать подход с временными таблицами и обязательным индексированием полей соединения.