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

← На главную

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

В этой статье разберем, почему возникает эта проблема, как быстро исправить ее в настройках СКД и какие альтернативные архитектурные решения существуют.

Причина низкого быстродействия при связи наборов данных

Рассмотрим механики работы платформы 1С при связывании двух наборов данных в СКД по параметрам. Выясним причину падения производительности:

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

Решение 1. Передача номенклатуры в параметр виртуальной таблицы (Быстрый способ)

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


ВЫБРАТЬ
    ПродажиОбороты.Менеджер КАК Менеджер,
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.Период КАК Дата,
    СУММА(ПродажиОбороты.КоличествоОборот) КАК Кол,
    СУММА(ПродажиОбороты.СтоимостьОборот) КАК Сумма
ИЗ
    РегистрНакопления.Продажи.Обороты(&НП, &КП, Авто, ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
    ПродажиОбороты.Номенклатура,
    ПродажиОбороты.Менеджер,
    ПродажиОбороты.Период

Во втором наборе данных необходимо обязательно указать параметр Номенклатура прямо внутри условий виртуальной таблицы СрезПоследних:


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

Разберем по шагам настройку вкладки «Связи наборов данных» в схеме компоновки:

Укажем Источник связи: НаборДанных1, Приемник связи: НаборДанных2.
Установим выражение связи для даты: Условие связи — Дата, Параметр — Дата.
Добавим вторую строку связи: Условие связи — Номенклатура, Параметр — Номенклатура.

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

Решение 2. Использование единого пакетного запроса (Максимальная производительность)

Хотя передача параметров в СКД решает проблему скорости на средних объемах, наилучшую производительность на больших объемах данных обеспечивает отказ от двух наборов данных в пользу одного набора данных типа «Запрос» с использованием пакетного запроса.

Рассмотрим структуру такого запроса:


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

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

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

Посмотрим на преимущества единого пакетного запроса:

Отсутствуют вызовы циклов на стороне платформы 1С. Все вычисления происходят целиком внутри СУБД.
Используется физическая таблица регистра сведений РегистрСведений.ЦеныНоменклатуры вместо дорогостоящей виртуальной таблицы СрезПоследних.
Поиск даты последней цены происходит с помощью соединения по условию ДатаПродажи >= ПериодЦены и группировки МАКСИМУМ(Период).

Резюме

Для оптимизации получения цен на каждую дату продажи в СКД выделим два подхода:

Быстрое исправление: во втором наборе данных передать Номенклатура = &Номенклатура в параметры виртуальной таблицы СрезПоследних.
Кардинальная оптимизация: объединить наборы в один пакетный запрос, соединив таблицу продаж с физической таблицей регистра цен по максимальному периоду.