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