При разработке отчетов в системе компоновки данных (СКД) часто возникает задача: вывести данные о продажах и для каждой строки продажи получить актуальную цену из регистра сведений на момент совершения операции. Начинающие разработчики часто решают эту задачу «в лоб», создавая два набора данных и связывая их по параметрам &Дата и &Номенклатура. Однако такой подход ведет к критическому падению производительности. Разберем, почему это происходит и как правильно оптимизировать такой запрос.
Рассмотрим стандартную ситуацию. В первом наборе данных мы выбираем обороты продаж, а во втором — срез последних цен. Чтобы получить цену на дату каждой продажи, мы настраиваем связь наборов, где в параметр виртуальной таблицы СрезПоследних передается дата из первой таблицы. Проанализируем работу системы в этом случае: 1С формирует отдельный SQL-запрос к базе данных для каждой строки из первого набора. Если в отчете 10 000 строк продаж, система выполнит 10 000 обращений к СУБД. Это классическая проблема «запроса в цикле», которая делает отчет неработоспособным на больших объемах данных.
Чтобы отчет «летал», нам необходимо отказаться от виртуальной таблицы СрезПоследних в пользу работы с физической таблицей регистра сведений. Выясним причину, почему это эффективнее: физическая таблица позволяет нам получить все нужные данные одним пакетным запросом и соединить их на уровне СУБД, что в сотни раз быстрее выполнения множества мелких запросов.
Рассмотрим подробнее алгоритм получения цен на каждую дату продажи через временные таблицы:
МЕНЬШЕ ИЛИ РАВНО дате продажи. Сгруппируем результат и найдем МАКСИМУМ(Период). Это и будет дата последней установки цены перед продажей.Цена.Посмотрим на пример кода, который реализует описанную логику. Мы объединим продажи и получение цен в один набор данных типа «Запрос».
ВЫБРАТЬ
ПродажиОбороты.Номенклатура КАК Номенклатура,
ПродажиОбороты.Период КАК ПериодПродажи,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.СтоимостьОборот КАК Стоимость
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи.Обороты(&НачПериода, &КонПериода, День, ) КАК ПродажиОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Продажи.Номенклатура КАК Номенклатура,
ВТ_Продажи.ПериодПродажи КАК ПериодПродажи,
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_Продажи.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_Продажи.ПериодПродажи >= ЦеныНоменклатуры.Period
И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
СГРУППИРОВАТЬ ПО
ВТ_Продажи.Номенклатура,
ВТ_Продажи.ПериодПродажи
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_МаксимальныеДаты.Номенклатура КАК Номенклатура,
ВТ_МаксимальныеДаты.ПериодПродажи КАК ПериодПродажи,
МАКСИМУМ(ЦеныНоменклатуры.Цена) КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныНаДату
ИЗ
ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_МаксимальныеДаты.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ВТ_МаксимальныеДаты.ПериодЦены = ЦеныНоменклатуры.Период
И (ЦеныНоменклатуры.ТипЦен = &ТипЦен)
СГРУППИРОВАТЬ ПО
ВТ_МаксимальныеДаты.Номенклатура,
ВТ_МаксимальныеДаты.ПериодПродажи
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Продажи.Номенклатура,
ВТ_Продажи.ПериодПродажи,
ВТ_Продажи.Количество,
ВТ_Продажи.Стоимость,
ЕСТЬNULL(ВТ_ЦеныНаДату.Цена, 0) КАК ЦенаПрайса
ИЗ
ВТ_Продажи КАК ВТ_Продажи
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНаДату КАК ВТ_ЦеныНаДату
ПО ВТ_Продажи.Номенклатура = ВТ_ЦеныНаДату.Номенклатура
И ВТ_Продажи.ПериодПродажи = ВТ_ЦеныНаДату.ПериодПродажи
Разберем ситуацию, когда в регистре цен на один и тот же момент времени (например, 31.12.2018 23:59:59) введено несколько записей с разными ценами. Это часто случается при технических сбоях или специфической загрузке данных. В этом случае обычное соединение по периоду выдаст несколько строк на одну продажу, что приведет к задвоению итогов.
Для решения этой проблемы мы использовали в коде выше конструкцию МАКСИМУМ(ЦеныНоменклатуры.Цена) вместе с СГРУППИРОВАТЬ ПО. Проанализируем ситуацию: если система находит три записи на одну и ту же секунду, функция МАКСИМУМ (или СРЕДНЕЕ, в зависимости от бизнес-логики) схлопнет их в одну строку. Это гарантирует, что количество строк в финальном отчете будет строго соответствовать количеству строк в таблице продаж.
При использовании одного набора данных важно помнить о механизмах автозаполнения СКД. Чтобы отборы по номенклатуре или периоду, наложенные пользователем в настройках отчета, проникали внутрь временных таблиц, необходимо использовать фигурные скобки {}. Это позволит СКД модифицировать запрос «на лету».
Например, во второй временной таблице полезно добавить условие:
ГДЕ {Номенклатура.* КАК Номенклатура}
Это заставит систему применить пользовательский фильтр по номенклатуре непосредственно в момент выборки данных из регистра сведений, а не после того, как будут выбраны все цены из базы.
Подведем итоги нашего исследования производительности:
ИНДЕКСИРОВАТЬ ПО. Индексация по полям Номенклатура и Период в промежуточных таблицах существенно ускорит соединение больших массивов данных.Применяя эти методы, мы превращаем тяжелый отчет, который мог формироваться минутами, в быстрый инструмент, выдающий результат практически мгновенно. Совместная работа с физическими таблицами и временными объектами — это золотой стандарт разработки профессиональных отчетов на платформе 1С.