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