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

← На главную

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

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

Рассмотрим стандартную ошибку: создание трех разных наборов данных (запросов) в СКД и их связывание на вкладке «Связи наборов данных». В этом случае СКД работает по принципу «N+1 запроса». Сначала выбираются документы, а затем для каждой строки основного запроса выполняется отдельный запрос к регистрам цен и валют. Если в реализации 100 строк, система сделает еще 200 запросов к базе данных. Выясним, как этого избежать.

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

Шаг 1: Нормализация дат и подготовка временной таблицы товаров

Для начала нам необходимо собрать все данные из документов во временную таблицу. Важный нюанс: если в один день оформлено несколько документов, курсы валют и цены для них, как правило, будут одинаковыми (так как периодичность регистров обычно составляет «День»). Чтобы сократить количество уникальных точек запроса данных, используем функцию НАЧАЛОПЕРИОДА(..., ДЕНЬ).

Проанализируем пример получения данных из табличной части реализации:


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

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

Шаг 2: Получение курсов валют и цен через временные таблицы

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


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

ОБЪЕДИНИТЬ

ВЫБРАТЬ
    КурсыВалют.Период,
    КурсыВалют.Курс
ИЗ
    РегистрСведений.КурсыВалют КАК КурсыВалют
ГДЕ
    КурсыВалют.Валюта = &Валюта
    И КурсыВалют.Период МЕЖДУ &НачалоПериода И &КонецПериода;

Аналогичным образом подготовим таблицу цен. Обратите внимание на оптимизацию: в секции ГДЕ или в параметрах виртуальной таблицы обязательно ограничиваем выборку только той номенклатурой, которая есть в нашей временной таблице ВТ_Товары.

Шаг 3: Сопоставление дат документов и периодов регистра

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


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

Этот подход позволяет СУБД быстро сопоставить даты без использования вложенных коррелированных подзапросов.

Шаг 4: Финальное объединение и индексация

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

Добавьте ключевое слово ИНДЕКСИРОВАТЬ ПО для полей Номенклатура и НачалоДня во всех временных таблицах.
Выполните левое соединение основной таблицы товаров с таблицами периодов, а затем с таблицами самих значений (курсов и цен).
Используйте функцию ЕСТЬNULL для обработки ситуаций, когда на определенную дату цена или курс не установлены.

Посмотрим на пример финального соединения:


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

Дополнительные советы по оптимизации

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

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