При разработке отчетов на системе компоновки данных (СКД) разработчики нередко сталкиваются с ситуацией, когда запрос к остаткам и свойствам объектов выполняется слишком долго. Разберем типичные ошибки проектирования таких запросов и рассмотрим практические шаги по их устранению и ускорению выборки данных.
Проанализируем исходную структуру запроса: в нем собираются свойства номенклатуры и характеристик из регистра сведений РегистрСведений.ЗначенияСвойствОбъектов, соединяются с остатками и оборотами из РегистрНакопления.ТоварыНаСкладах, после чего несколько временных таблиц объединяются через ПОЛНОЕ СОЕДИНЕНИЕ. Выясним, какие ошибки вызывают существенное падение производительности на стороне СУБД.
В тексте запроса обращение к виртуальной таблице выглядит следующим образом: РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(, , , , ...). Даже если в СКД на уровне параметров задан период, отсутствие явных параметров внутри круглых скобок виртуальной таблицы может приводить к неоптимальной работе.
Если параметры периода не передаются непосредственно в виртуальную таблицу, СУБД рассчитывает итоги за весь период существования базы данных, а затем СКД накладывает фильтр на итоговую выборку. Рассмотрим, как правильно объявить параметры даты и условия отбора:
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
Авто,
,
ХарактеристикаНоменклатуры <> ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)
) КАК ТоварыНаСкладахОстаткиИОбороты
Важно: условие Номенклатура.ВестиУчетПоХарактеристикам = ИСТИНА, переданное через точку в параметры виртуальной таблицы, заставляет платформу строить неявное соединение с таблицей справочника Справочник.Номенклатура при расчете итогов. Это мешает использованию кластерных индексов таблиц итогов. Гораздо эффективнее отбирать записи с заполненной характеристикой напрямую, как показано в примере выше.
Поле Объект регистра сведений ЗначенияСвойствОбъектов имеет составной тип данных (множество справочников и документов). Конструкция ЗначенияСвойствОбъектов.Объект.Владелец приводит к тому, что СУБД генерирует левые соединения (LEFT JOIN) со всеми возможными таблицами объектов метаданных, а затем — с таблицами их владельцев.
Разберем способы устранения лишней нагрузки:
ВЫРАЗИТЬ для ограничения типа: ВЫРАЗИТЬ(ЗначенияСвойствОбъектов.Объект КАК Справочник.ХарактеристикиНоменклатуры).Владелец.Объект.Ссылка ССЫЛКА Справочник.... Поле Объект само по себе является ссылочным, поэтому обращение к .Ссылка порождает дополнительное соединение. Достаточно писать: ЗначенияСвойствОбъектов.Объект ССЫЛКА Справочник.ХарактеристикиНоменклатуры.ПО ЗначенияСвойствОбъектов.Объект = ТоварыНаСкладахОстаткиИОбороты.ХарактеристикаНоменклатуры), дополнительное условие по владельцу (И Объект.Владелец = Номенклатура) является избыточным, так как характеристика подчинена конкретной номенклатуре.Конструкция ПОЛНОЕ СОЕДИНЕНИЕ — одна из самых ресурсоемких операций в реляционных базах данных. При объединении трех и более таблиц по составным условиям оптимизатор СУБД строит сложные планы выполнения и создает значительную нагрузку на базу временных таблиц (TempDB).
Кроме того, во временной таблице ОтборГоризонт полное соединение фактически обесценивается условиями в блоке ГДЕ: ГДЕ Горизонталь.Свойство = &Горизонталь И Отбор.Свойство = &Отбор. Если для одной из сторон соединяемых строк нет соответствия, поля вернут NULL, и условие в ГДЕ отсечет всю строку, превратив операцию во внутреннее соединение с ненужными накладными расходами.
Рассмотрим альтернативные и более производительные подходы к получению свойств:
Подход А: Использование встроенного механизма характеристик в СКД
В схеме компоновки данных предусмотрен штатный механизм автоподстановки характеристик. При его настройке запрос строится только к регистру остатков, а все дополнительные реквизиты и сведения СКД подтягивает динамически — исключительно для тех колонок, которые пользователь включил в отчет или установил в отборе.
Подход Б: Получение свойств через агрегацию (свертку/Pivot)
Если свойства необходимо развернуть непосредственно в тексте запроса без множественных соединений, выполним однократную выборку из регистра сведений с последующей группировкой:
ВЫБРАТЬ
ВЫРАЗИТЬ(ЗСО.Объект КАК Справочник.ХарактеристикиНоменклатуры) КАК Характеристика,
ВЫРАЗИТЬ(ЗСО.Объект КАК Справочник.ХарактеристикиНоменклатуры).Владелец КАК Номенклатура,
МАКСИМУМ(ВЫБОР КОГДА ЗСО.Свойство = &Вертикаль ТОГДА ЗСО.Значение КОНЕЦ) КАК ЗначениеВертикаль,
МАКСИМУМ(ВЫБОР КОГДА ЗСО.Свойство = &Горизонталь ТОГДА ЗСО.Значение КОНЕЦ) КАК ЗначениеГоризонталь,
МАКСИМУМ(ВЫБОР КОГДА ЗСО.Свойство = &Отбор ТОГДА ЗСО.Значение КОНЕЦ) КАК ЗначениеОтбор,
МАКСИМУМ(ВЫБОР КОГДА ЗСО.Свойство = &Доп ТОГДА ЗСО.Значение КОНЕЦ) КАК ЗначениеДоп
ПОМЕСТИТЬ ВТ_СвойстваХарактеристик
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗСО
ГДЕ
ЗСО.Свойство В (&Вертикаль, &Горизонталь, &Отбор, &Доп)
И ЗСО.Объект ССЫЛКА Справочник.ХарактеристикиНоменклатуры
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(ЗСО.Объект КАК Справочник.ХарактеристикиНоменклатуры),
ВЫРАЗИТЬ(ЗСО.Объект КАК Справочник.ХарактеристикиНоменклатуры).Владелец
ИНДЕКСИРОВАТЬ ПО
Характеристика,
Номенклатура
;
Если временные таблицы содержат большой объем данных и впоследствии участвуют в соединениях, поля соединения необходимо индексировать с помощью конструкции ИНДЕКСИРОВАТЬ ПО. Это позволяет СУБД выполнять быстрое сопоставление записей по индексам вместо полного сканирования таблицы.
Применив описанные правила (передача параметров в виртуальную таблицу, приведение типов через ВЫРАЗИТЬ, замена полных соединений на группировку и индексация), мы существенно снизим нагрузку на сервер СУБД и многократно ускорим формирование отчета.