Как оптимизировать сложный запрос по остаткам, продажам и ценам для отчета в 1С?

← На главную

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

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

Проанализируем исходный вариант запроса и выделим ключевые узкие места, которые приводят к существенному падению производительности СУБД:

Использование виртуальной таблицы «ОстаткиИОбороты» вместо таблицы «Остатки»: Если для построения логики отчета требуются только конечные остатки, использование таблицы остатков и оборотов создает колоссальную избыточную нагрузку на сервер, так как СУБД вынуждена рассчитывать промежуточные движения за весь период.
Разыменование полей через точку в условиях и соединениях: Конструкции вида Склад.Подразделение.ТипПодразделения и Продажи.Подразделение.Склад заставляют платформу генерировать неявные соединения (LEFT JOIN) с таблицами справочников, что усложняет план выполнения запроса.
Использование вложенных подзапросов в параметрах виртуальных таблиц: Фильтрация вида Номенклатура В (ВЫБРАТЬ ТСОИО.Номенклатура ИЗ ТСОИО) внутри параметров виртуальных таблиц (оборотов, срезов последних) часто мешает оптимизатору СУБД использовать индексы таблиц итогов.
Отсутствие индексации временных таблиц: Создание промежуточных временных таблиц без предложения ИНДЕКСИРОВАТЬ ПО приводит к полному сканированию данных (Table Scan) в базе tempdb при каждом последующем соединении.

Шаг 1. Предварительная подготовка списков фильтрации

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


ВЫБРАТЬ
    Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВТ_Склады
ИЗ
    Справочник.Склады КАК Склады
ГДЕ
    Склады.Подразделение.ТипПодразделения = &ТипПодразделения

ИНДЕКСИРОВАТЬ ПО
    Склад
;

Шаг 2. Формирование базовой таблицы номенклатуры и складов

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


ВЫБРАТЬ
    ВЗ.Номенклатура КАК Номенклатура,
    ВЗ.Склад КАК Склад
ПОМЕСТИТЬ ВТ_НоменклатураСклад
ИЗ
    (ВЫБРАТЬ
        ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
        ТоварыНаСкладахОстатки.Склад КАК Склад
    ИЗ
        РегистрНакопления.ТоварыНаСкладах.Остатки(
                &КонецПериода,
                Номенклатура В ИЕРАРХИИ (&ГруппаНоменклатуры)
                    И Склад В (ВЫБРАТЬ ВТ_Склады.Склад ИЗ ВТ_Склады)) КАК ТоварыНаСкладахОстатки

    ОБЪЕДИНИТЬ

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

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

Шаг 3. Получение уникального списка номенклатуры

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ_НоменклатураСклад.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Товары
ИЗ
    ВТ_НоменклатураСклад КАК ВТ_НоменклатураСклад

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

Шаг 4. Сборка итогового запроса с плоскими соединениями

Теперь соберем все данные воедино. Обратите внимание на расчет остатков основного склада: они не должны размножаться по магазинам через неверную группировку, а должны соединяться строго по полю Номенклатура.


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

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

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

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

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

Оптимизация средствами Системы компоновки данных (СКД)

Если отчет строится с использованием СКД, монолитный пакет запросов можно оптимизировать еще эффективнее:

Разделение на несколько наборов данных: Создадим отдельные наборы данных-запросы для остатков, цен, точки заказа и продаж. Свяжем их на вкладке «Связи наборов данных». В этом случае СКД будет опрашивать только те таблицы, поля которых пользователь выбрал в настройках структуры отчета.
Использование вычисляемых полей: Логические конструкции ВЫБОР КОГДА ... ТОГДА 1 ИНАЧЕ 0 и функции ЕСТЬNULL перенесем в настройки вычисляемых полей или выражений ресурсов СКД. Это снизит объем вычислений на стороне СУБД.
Фигурные скобки СКД: При использовании единого запроса обернем необязательные поля и условия в расширения языка запросов компоновки: {ВЫБРАТЬ ...}, {ГДЕ ...}, что позволит СКД автоматически удалять неиспользуемые левые соединения из итогового SQL-текста.