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

← На главную

При разработке отчетов и обработок часто возникает задача извлечь несколько дополнительных реквизитов или сведений по объекту (например, справочнику Номенклатура). Если соединять табличную часть ДополнительныеРеквизиты через отдельное левое соединение для каждого свойства, текст запроса становится громоздким, а нагрузка на СУБД существенно возрастает. Разберем, как правильно и производительно реализовать такую выборку.

Анализ проблемы исходного запроса

Рассмотрим типичный неоптимальный подход. Для каждого свойства создается подзапрос с левым соединением:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    Номенклатура.Описание КАК Описание,
    Номенклатура.Страна КАК Страна,
    Номенклатура.Производитель КАК Производитель,
    ТутДлина.Значение КАК Длина,
    ТутВысота.Значение КАК Высота,
    Номенклатура.Производитель.ПроцентСкидкиДилеру КАК ПроизводительПроцентСкидкиДилеру
ИЗ
    Справочник.Номенклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
        НоменклатураДополнительныеРеквизиты.Ссылка КАК Ссылка,
        НоменклатураДополнительныеРеквизиты.Значение КАК Значение
    ИЗ
        Справочник.Номенклатура.ДополнительныеРеквизиты КАК НоменклатураДополнительныеРеквизиты
    ГДЕ
        НоменклатураДополнительныеРеквизиты.Свойство.Код = &КодДлина) КАК ТутДлина
    ПО Номенклатура.Ссылка = ТутДлина.Ссылка
    ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
        НоменклатураДополнительныеРеквизиты.Ссылка КАК Ссылка,
        НоменклатураДополнительныеРеквизиты.Значение КАК Значение
    ИЗ
        Справочник.Номенклатура.ДополнительныеРеквизиты КАК НоменклатураДополнительныеРеквизиты
    ГДЕ
        НоменклатураДополнительныеРеквизиты.Свойство.Код = &КодВысота) КАК ТутВысота
    ПО Номенклатура.Ссылка = ТутВысота.Ссылка
ГДЕ
    Номенклатура.Ссылка = &Ссылка

В приведенном коде допущен ряд серьезных ошибок оптимизации:

Множественные соединения: если требуется получить 8–10 свойств, формируется соответствующее количество тяжелых подзапросов и соединений к одной и той же табличной части.
Неявные соединения через точку: конструкция Свойство.Код = &КодДлина выполняет разыменование ссылки, что заставляет платформу строить лишний неявный LEFT JOIN к таблице плана видов характеристик. То же самое происходит при обращении Номенклатура.Производитель.ПроцентСкидкиДилеру.
Составной тип поля Значение: без явного ограничения типа СУБД вынуждена обрабатывать все потенциальные таблицы ссылочных типов, входящих в составной тип значения реквизита.

Способ 1. Разворот строк в колонки с помощью ВЫБОР и группировки (Классический Pivot)

Разберем оптимальный вариант для текста запроса. Мы соединяем табличную часть ДополнительныеРеквизиты всего один раз, фильтруем список свойств по условию В (&СписокСвойств), а затем разворачиваем строки в колонки через конструкцию ВЫБОР КОГДА с агрегатной функцией МАКСИМУМ и группировкой.

Посмотрим на пример правильного запроса:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    Номенклатура.Описание КАК Описание,
    Номенклатура.Страна КАК Страна,
    Номенклатура.Производитель КАК Производитель,
    Производители.ПроцентСкидкиДилеру КАК ПроизводительПроцентСкидкиДилеру,
    МАКСИМУМ(ВЫБОР
        КОГДА ДопРеквизиты.Свойство = &СвойствоДлина
            ТОГДА ДопРеквизиты.Значение
        ИНАЧЕ NULL
    КОНЕЦ) КАК Длина,
    МАКСИМУМ(ВЫБОР
        КОГДА ДопРеквизиты.Свойство = &СвойствоВысота
            ТОГДА ДопРеквизиты.Значение
        ИНАЧЕ NULL
    КОНЕЦ) КАК Высота
ИЗ
    Справочник.Номенклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Производители КАК Производители
        ПО Номенклатура.Производитель = Производители.Ссылка
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура.ДополнительныеРеквизиты КАК ДопРеквизиты
        ПО Номенклатура.Ссылка = ДопРеквизиты.Ссылка
        И ДопРеквизиты.Свойство В (&СписокСвойств)
ГДЕ
    Номенклатура.Ссылка = &Ссылка
СГРУППИРОВАТЬ ПО
    Номенклатура.Ссылка,
    Номенклатура.Описание,
    Номенклатура.Страна,
    Номенклатура.Производитель,
    Производители.ПроцентСкидкиДилеру

Разберем по шагам ключевые детали этой схемы:

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

Способ 2. Оптимизация пакетных выборок через временные таблицы и типизацию

Если запрос строится не по одной номенклатуре, а по массиву товаров или по всему справочнику, разберем более производительный подход с использованием временных таблиц и функции ВЫРАЗИТЬ.


ВЫБРАТЬ
    ДопРеквизиты.Ссылка КАК Номенклатура,
    ДопРеквизиты.Свойство КАК Свойство,
    ДопРеквизиты.Значение КАК Значение
ПОМЕСТИТЬ ВТ_ДополнительныеРеквизиты
ИЗ
    Справочник.Номенклатура.ДополнительныеРеквизиты КАК ДопРеквизиты
ГДЕ
    ДопРеквизиты.Ссылка В (&МассивНоменклатуры)
    И ДопРеквизиты.Свойство В (&СписокСвойств)

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Свойство
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура,
    Номенклатура.Описание КАК Описание,
    МАКСИМУМ(ВЫБОР
        КОГДА ВТ_Доп.Свойство = &СвойствоДлина
            ТОГДА ВЫРАЗИТЬ(ВТ_Доп.Значение КАК Число(15, 2))
        ИНАЧЕ 0
    КОНЕЦ) КАК Длина,
    МАКСИМУМ(ВЫБОР
        КОГДА ВТ_Доп.Свойство = &СвойствоВысота
            ТОГДА ВЫРАЗИТЬ(ВТ_Доп.Значение КАК Число(15, 2))
        ИНАЧЕ 0
    КОНЕЦ) КАК Высота
ИЗ
    Справочник.Номенклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДополнительныеРеквизиты КАК ВТ_Доп
        ПО Номенклатура.Ссылка = ВТ_Доп.Номенклатура
ГДЕ
    Номенклатура.Ссылка В (&МассивНоменклатуры)
СГРУППИРОВАТЬ ПО
    Номенклатура.Ссылка,
    Номенклатура.Описание

Важный момент: использование конструкции ВЫРАЗИТЬ(Значение КАК Число(15, 2)) или приведение к конкретному типу справочника существенно оптимизирует план выполнения запроса, так как СУБД не тратит ресурсы на анализ всех типов составного поля.

Способ 3. Использование механизма характеристик в СКД

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

В схеме компоновки данных перейдем на вкладку Характеристики.
Добавим описание характеристик для объекта Справочник.Номенклатура, указав источник видов характеристик (План видов характеристик ДополнительныеРеквизитыИСведения) и таблицу значений характеристик (табличную часть ДополнительныеРеквизиты).
В результате платформа 1С автоматически добавит все дополнительные реквизиты в доступные поля отчета. При этом соединения к табличной части будут формироваться СКД динамически и только для тех колонок, которые пользователь включил в отчет.