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

← На главную

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

В этой статье мы подробно проанализируем, почему возникает эта проблема, разберем физику процессов на уровне СУБД и реализуем оптимальный пакетный запрос, который ускорит формирование отчета в десятки и сотни раз. Также мы рассмотрим методологическую сторону вопроса: как правильно исправить учетные данные, чтобы избежать использования «тяжелых» корректирующих алгоритмов в отчетах.

В чем причина низкой производительности связи наборов данных по параметрам?

Рассмотрим исходную архитектуру отчета, которая привела к зависанию. Разработчик создал два набора данных:

Основной набор данных (набор-запрос): выбирает данные о продажах и себестоимости из регистра накопления ПродажиСебестоимость за период.
Вспомогательный набор данных (набор-запрос): обращается к виртуальной таблице РегистрСведений.ЦеныНоменклатурыКонтрагентов.СрезПоследних(&Дата, Номенклатура = &Номенклатура).

В настройках СКД на вкладке «Связи наборов данных» была настроена связь по полям Номенклатура и Дата, где в качестве параметров связи передавались &Номенклатура и &Дата.

Что происходит на уровне СУБД при такой настройке?

Платформа «1С:Предприятие» не может объединить эти два запроса в один на сервере СУБД. Вместо этого она сначала выполняет первый запрос (получает продажи). Затем для каждой строки полученного результата компоновщик выполняет отдельный параметризованный запрос к виртуальной таблице среза последних цен. Если в отчете выводится 10 000 строк продаж, СУБД выполнит 10 000 отдельных запросов! Это порождает колоссальные накладные расходы на сетевой трафик, парсинг запросов СУБД и контекстные переключения. В результате отчет зависает.

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

---

Решение 1: Оптимизация запроса СКД через временные таблицы

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

Разберем алгоритм построения оптимального запроса по шагам.

Шаг 1. Получение продаж и фильтрация проблемных позиций

Сначала выберем все продажи за период. Чтобы оптимизировать расчеты, мы не будем искать цены для тех строк, где себестоимость уже успешно рассчитана и не равна нулю. Мы поместим во временную таблицу только те позиции, которые требуют заполнения цены.

Посмотрим на пример формирования первой временной таблицы:


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

Шаг 2. Получение уникальных пар Номенклатура-Дата для поиска цен

Чтобы не делать лишних соединений, выделим из таблицы продаж только уникальные сочетания номенклатуры и дат, для которых себестоимость пустая. Это значительно сократит объем обрабатываемых данных в физической таблице цен.


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Продажи.Номенклатура КАК Номенклатура,
    Продажи.ДатаПродажи КАК ДатаПродажи
ПОМЕСТИТЬ ВТ_ПродажиДляЦен
ИЗ
    ВТ_Продажи КАК Продажи
ГДЕ
    Продажи.ТребуетсяРасчетЦены = ИСТИНА
;

Шаг 3. Поиск максимальной даты установки цены (эмуляция Среза Последних)

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


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

Шаг 4. Получение значений цен на найденные максимальные даты

На предыдущем шаге мы выяснили точные даты, на которые нужно получить цену для каждой строки. Теперь соединим таблицу полученных дат ВТ_МаксимальныеДатыЦен с физической таблицей регистра цен еще раз, чтобы вытащить саму цену.


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

Шаг 5. Объединение всех данных в финальной выборке

Теперь объединим исходную таблицу продаж со справочной таблицей цен. Если фактическая себестоимость в продажах заполнена, мы берем ее. Если она равна нулю, мы рассчитываем ее как произведение количества на полученную цену из регистра цен.


ВЫБРАТЬ
    Продажи.ДатаПродажи КАК Период,
    Продажи.Номенклатура КАК Номенклатура,
    Продажи.Количество КАК Количество,
    ВЫБОР
        КОГДА Продажи.ТребуетсяРасчетЦены = ЛОЖЬ ТОГДА Продажи.Себестоимость
        ИНАЧЕ ЕСТЬNULL(Продажи.Количество * Цены.ЦенаСебестоимость, 0)
    КОНЕЦ КАК Себестоимость
ИЗ
    ВТ_Продажи КАК Продажи
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИтоговыеЦены КАК Цены
        ПО Продажи.Номенклатура = Цены.Номенклатура
        И Продажи.ДатаПродажи = Цены.ДатаПродажи
;

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

---

Решение 2: Настройка индексов регистра для ускорения запроса

Чтобы описанный выше пакетный запрос работал молниеносно, физические таблицы СУБД должны быть правильно проиндексированы. Проанализируем структуру регистра сведений ЦеныНоменклатурыКонтрагентов в конфигураторе:

Убедитесь, что для измерения Номенклатура установлено свойство Индексировать (или Индексировать с дополнительным упорядочиванием).
Если поиск цен часто происходит по конкретному типу цен (например, трансфертная или оптовая цена), измерение ТипЦен также должно быть проиндексировано.

Индексы позволяют СУБД мгновенно находить записи по условию ЦеныРегистр.Номенклатура = Продажи.Номенклатура во временных таблицах, минуя полное сканирование (Table Scan) всей таблицы регистра цен.

---

Решение 3: Методологическое исправление данных в базе

Как справедливо заметили участники обсуждения, подмена себестоимости в отчетах расчетными ценами — это «костыль», искажающий финансовую картину организации. В партионном учете (особенно при методе FIFO) себестоимость должна формироваться на основании реальных документов поступления и оприходования. Нулевая себестоимость часто возникает из-за ошибок пользователей при оприходовании излишков (например, при инвентаризации товар приходуется с нулевой стоимостью).

Проанализируем правильный методологический алгоритм исправления этой проблемы в информационной базе:

Выявление документов с ошибками: Необходимо написать или запустить готовую обработку (например, «Анализ ведения учета»), которая найдет все проведенные документы Оприходование товаров за прошлые периоды, в которых цена номенклатуры не заполнена (равна нулю).
Автоматическое заполнение цен: Напишем простую обработку, которая заполнит нулевые цены в найденных оприходованиях значениями из регистра сведений ЦеныНоменклатурыКонтрагентов (или цен номенклатуры компании), актуальными на дату проведения каждого документа, и перепроведет их.
Восстановление последовательности: После исправления первичных документов необходимо запустить регламентную обработку «Проведение документов по партиям» (восстановление последовательности партионного учета) за все исправленные периоды. Это позволит пересчитать себестоимость списания по FIFO и корректно заполнить регистр накопления ПродажиСебестоимость.
Административный запрет ошибок: Чтобы ситуация не повторилась в будущем, настройте права пользователей (или добавьте проверку в процедуру ПередЗаписью документов поступления и оприходования), запрещающую проведение этих документов с нулевой суммой или ценой.

Исправив данные в базе один раз, вы сможете использовать стандартный типовой отчет по валовому доходу, который будет работать мгновенно и показывать абсолютно корректную прибыль компании.