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

← На главную

Довольно часто разработчики сталкиваются с ситуацией, когда запрос к базе данных превосходно отрабатывает на небольшом объеме строк, но при незначительном росте объема входных данных время выполнения возрастает в сотни раз. Например, при расчете наценки по 45 позициям запрос выполняется за несколько секунд, а при 46 строках зависает на десятки минут. Проанализируем причины такого поведения и разберем варианты решения данной проблемы.

Анализ исходного запроса и причин деградации производительности

Рассмотрим исходный вариант запроса, вызвавший существенное замедление:


ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    Товары.Цена КАК ЦенаПродажи
ПОМЕСТИТЬ ТаблицаТоваров
ИЗ
    &ТаблицаТоваров КАК Товары
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ПериодыЦен.Номенклатура КАК Номенклатура,
    ВЫБОР
        КОГДА НЕ &ЦенаСНДС
            ТОГДА МАКСИМУМ(ЕСТЬNULL(ПоследниеЦеныЗакупокСрезПоследних.ЦенаБезНДС, 0))
        ИНАЧЕ МАКСИМУМ(ЕСТЬNULL(ПоследниеЦеныЗакупокСрезПоследних.ЦенаСНДС, 0))
    КОНЕЦ КАК ЦенаЗакупки
ПОМЕСТИТЬ ЦеныЗакупки
ИЗ
    (ВЫБРАТЬ
        ТаблицаТоваров.Номенклатура КАК Номенклатура,
        МАКСИМУМ(ПоследниеЦеныЗакупокСрезПоследних.Период) КАК Период
    ИЗ
        ТаблицаТоваров КАК ТаблицаТоваров
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПоследниеЦеныЗакупок.СрезПоследних(&Дата, ВидДоговора = &ВидДоговора) КАК ПоследниеЦеныЗакупокСрезПоследних
            ПО ТаблицаТоваров.Номенклатура = ПоследниеЦеныЗакупокСрезПоследних.Номенклатура
    СГРУППИРОВАТЬ ПО
        ТаблицаТоваров.Номенклатура) КАК ПериодыЦен
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПоследниеЦеныЗакупок.СрезПоследних(&Дата, ВидДоговора = &ВидДоговора) КАК ПоследниеЦеныЗакупокСрезПоследних
        ПО ПериодыЦен.Номенклатура = ПоследниеЦеныЗакупокСрезПоследних.Номенклатура
            И ПериодыЦен.Период = ПоследниеЦеныЗакупокСрезПоследних.Период
СГРУППИРОВАТЬ ПО
    ПериодыЦен.Номенклатура
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    Товары.ЦенаПродажи КАК ЦенаПродажи,
    ЦеныЗакупки.ЦенаЗакупки КАК ЦенаЗакупки,
    ВЫБОР
        КОГДА ЕСТЬNULL(ЦеныЗакупки.ЦенаЗакупки, 0) = 0
            ТОГДА 999999999
        ИНАЧЕ (Товары.ЦенаПродажи - ЦеныЗакупки.ЦенаЗакупки) / ЦеныЗакупки.ЦенаЗакупки * 100
    КОНЕЦ КАК Наценка
ИЗ
    ТаблицаТоваров КАК Товары
        ЛЕВОЕ СОЕДИНЕНИЕ ЦеныЗакупки КАК ЦеныЗакупки
        ПО Товары.Номенклатура = ЦеныЗакупки.Номенклатура
ГДЕ
    ЕСТЬNULL(ЦеныЗакупки.ЦенаЗакупки, 0) <> 0

Выясним причину, почему этот код работает неэффективно:

Многократное обращение к виртуальной таблице: в пакете дважды вызывается тяжелая виртуальная таблица СрезПоследних без отбора по номенклатуре в параметрах таблицы. В СУБД это разворачивается в расчет среза по всему объему регистра.
Соединение с вложенным запросом: внешнее соединение подзапроса ПериодыЦен с виртуальной таблицей среза мешает оптимизатору СУБД построить эффективный план.
Избыточная индексация малых таблиц: индексация временной таблицы, в которой находится несколько десятков строк (менее 1000 записей), создает дополнительную нагрузку на создание временных объектов в tempdb и не дает преимуществ.
Смена плана выполнения при переходе порога: при передаче небольшого списка значений СУБД использует перечисление аргументов и точечные поиски по индексам. При росте числа элементов платформа 1С меняет способ передачи параметров или структуру соединения, в результате чего оптимизатор СУБД переключается на полное сканирование таблицы регистра (Table Scan / Index Scan) с соединением вложенными циклами (Nested Loops).
Неявные преобразования типов: если в переданной таблице значений поле Номенклатура не типизировано строго ссылочным типом, на стороне СУБД происходит неявное приведение типов (Implicit Conversion), что исключает использование индексов.

Решение 1. Оптимизация среза последних и устранение дублирования

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

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


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

Разберем внесенные изменения:

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

Решение 2. Отказ от виртуальной таблицы в пользу реальной таблицы регистра

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

Рассмотрим, как реализовать этот подход:


ВЫБРАТЬ
    ВЫРАЗИТЬ(Товары.Номенклатура КАК Справочник.Номенклатура) КАК Номенклатура,
    МАКСИМУМ(Цены.Период) КАК Период
ПОМЕСТИТЬ МаксимальныеПериоды
ИЗ
    &ТаблицаТоваров КАК Товары
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПоследниеЦеныЗакупок КАК Цены
        ПО (Цены.Период <= &Дата)
            И (Цены.ВидДоговора = &ВидДоговора)
            И (Товары.Номенклатура = Цены.Номенклатура)
СГРУППИРОВАТЬ ПО
    Товары.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Периоды.Номенклатура КАК Номенклатура,
    ВЫБОР
        КОГДА НЕ &ЦенаСНДС
            ТОГДА МАКСИМУМ(Цены.ЦенаБезНДС)
        ИНАЧЕ МАКСИМУМ(Цены.ЦенаСНДС)
    КОНЕЦ КАК ЦенаЗакупки
ПОМЕСТИТЬ ИтоговыеЦены
ИЗ
    МаксимальныеПериоды КАК Периоды
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПоследниеЦеныЗакупок КАК Цены
        ПО Периоды.Номенклатура = Цены.Номенклатура
            И Периоды.Период = Цены.Период
            И (Цены.ВидДоговора = &ВидДоговора)
СГРУППИРОВАТЬ ПО
    Периоды.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    Товары.Цена КАК ЦенаПродажи,
    ИтоговыеЦены.ЦенаЗакупки КАК ЦенаЗакупки,
    (Товары.Цена - ИтоговыеЦены.ЦенаЗакупки) / ИтоговыеЦены.ЦенаЗакупки * 100 КАК Наценка
ИЗ
    &ТаблицаТоваров КАК Товары
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ИтоговыеЦены КАК ИтоговыеЦены
        ПО Товары.Номенклатура = ИтоговыеЦены.Номенклатура
ГДЕ
    ИтоговыеЦены.ЦенаЗакупки > 0

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

Решение 3. Использование оконных функций (для платформы 8.3.20 и выше)

Начиная с версии платформы 1С 8.3.20, доступен механизм оконных функций, позволяющий получить последнюю запись без дополнительных самосоединений и виртуальных таблиц с максимальной скоростью:


ВЫБРАТЬ
    Товары.Номенклатура КАК Номенклатура,
    Товары.Цена КАК ЦенаПродажи,
    ВЫБОР
        КОГДА НЕ &ЦенаСНДС ТОГДА Цены.ЦенаБезНДС
        ИНАЧЕ Цены.ЦенаСНДС
    КОНЕЦ КАК ЦенаЗакупки,
    ROW_NUMBER() OVER (
        PARTITION BY Цены.Номенклатура 
        ORDER BY Цены.Период УБЫВ
    ) КАК НомерСтрокиЦены
ПОМЕСТИТЬ ДанныеСреза
ИЗ
    &ТаблицаТоваров КАК Товары
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ПоследниеЦеныЗакупок КАК Цены
        ПО Товары.Номенклатура = Цены.Номенклатура
            И (Цены.Период <= &Дата)
            И (Цены.ВидДоговора = &ВидДоговора)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Данные.Номенклатура КАК Номенклатура,
    Данные.ЦенаПродажи КАК ЦенаПродажи,
    Данные.ЦенаЗакупки КАК ЦенаЗакупки,
    (Данные.ЦенаПродажи - Данные.ЦенаЗакупки) / Данные.ЦенаЗакупки * 100 КАК Наценка
ИЗ
    ДанныеСреза КАК Данные
ГДЕ
    Данные.НомерСтрокиЦены = 1
    И Данные.ЦенаЗакупки > 0

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

Анализ исходного запроса и причин деградации производительности

Решение 1. Оптимизация среза последних и устранение дублирования

Решение 2. Отказ от виртуальной таблицы в пользу реальной таблицы регистра

Решение 3. Использование оконных функций (для платформы 8.3.20 и выше)

Рекомендации по структуре данных и СУБД

Читайте также