Как реализовать условный отбор по необязательному подразделению в запросе внешней обработки 1С?

Анализируем задачу: условный отбор по необязательному параметру

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

Если подразделение выбрано, то запрос корректируется таким образом, чтобы заполнение табличной части происходило только с учетом выбранного подразделения за указанный период.
Если подразделение не выбрано (поле пустое), то запрос должен заполняться по всем подразделениям за выбранный период, как и ранее.

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

Вариант 1: Использование конструкции ВЫБОР КОГДА в секции ГДЕ запроса

Один из первых подходов, который может прийти в голову при необходимости условного отбора, — это использование конструкции ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ непосредственно в секции ГДЕ вашего запроса. Этот метод позволяет встроить логику условия непосредственно в текст запроса, что делает его на первый взгляд довольно элегантным.

Вот как мог бы выглядеть фрагмент условия ГДЕ, предложенный одним из участников обсуждения:


ВЫБОР КОГДА &Подразделение = ЗНАЧЕНИЕ(Справочник.Подразделение.ПустаяСсылка) ТОГДА ИСТИНА ИНАЧЕ Подразделение = &Подразделение КОНЕЦ

Мы передаем в запрос параметр &Подразделение. Если этот параметр равен пустой ссылке справочника «Подразделение» (что означает, что пользователь не выбрал подразделение на форме), то условие ВЫБОР КОГДА возвращает ИСТИНА, тем самым не накладывая никаких ограничений на выборку по подразделениям. В противном случае, если &Подразделение заполнено, условие превращается в Подразделение = &Подразделение, фильтруя данные по выбранному подразделению.

Анализируем недостатки:

Несмотря на кажущуюся простоту, данный подход имеет существенные недостатки, особенно когда речь идет о производительности на больших объемах данных. Мы выяснили, что:

Проблемы с использованием индексов: СУБД часто испытывает трудности с эффективным использованием индексов, когда в условии ГДЕ применяется функция или сложная конструкция вроде ВЫБОР КОГДА. Для оптимизатора запросов это условие становится менее прозрачным. Это может привести к тому, что СУБД вместо использования быстрого поиска по индексу будет вынуждена выполнять полное сканирование всей таблицы.
Рекомендации 1С: Разработчики 1С не рекомендуют использовать ВЫБОР КОГДА в основном условии секции ГДЕ. Его следует применять только в дополнительных условиях, когда выборка уже значительно ограничена другими, более простыми и индексируемыми условиями. Это связано с тем, что основное условие должно максимально быстро отсекать ненужные данные.
Потенциальное замедление: На больших таблицах такое условие может значительно замедлить выполнение запроса, поскольку каждая строка будет обрабатываться логикой ВЫБОР КОГДА. Это особенно критично, если поле Подразделение не является частью ведущего индекса или условие применяется к большому количеству данных.

Таким образом, хотя этот вариант и решает поставленную задачу, он не является оптимальным с точки зрения производительности и рекомендуется избегать его в критически важных или высоконагруженных запросах.

Вариант 2: Использование логического ИЛИ в секции ГДЕ запроса

Другой распространенный способ реализации условного отбора включает использование логического оператора ИЛИ. Этот подход также позволяет встроить логику выбора непосредственно в текст запроса, используя булевый параметр для управления условием.

Пример условия ГДЕ, предложенный в теме:

ГДЕ НЕ &ЕстьОтборПоПодразделению ИЛИ Подразделение = &Подразделение

В этом варианте мы вводим новый булевый параметр &ЕстьОтборПоПодразделению. Если пользователь выбрал подразделение, мы устанавливаем этот параметр в ИСТИНА и передаем выбранное подразделение в &Подразделение. В таком случае, если &ЕстьОтборПоПодразделению равно ИСТИНА, то первая часть условия НЕ &ЕстьОтборПоПодразделению становится ложной, и запрос будет фильтроваться по второй части: Подразделение = &Подразделение.

Если же подразделение не выбрано, мы устанавливаем &ЕстьОтборПоПодразделению в ЛОЖЬ. Тогда первая часть условия НЕ &ЕстьОтборПоПодразделению становится ИСТИНА, и, благодаря оператору ИЛИ, все строки будут включены в выборку, независимо от значения поля Подразделение, так как ИСТИНА ИЛИ (что-угодно) всегда дает ИСТИНА.

Анализируем недостатки:

Хотя этот метод кажется более простым, чем ВЫБОР КОГДА, он также имеет свои подводные камни, связанные с производительностью:

Ограничения использования индексов: Оператор ИЛИ в секции ГДЕ часто является серьезным препятствием для эффективного использования индексов СУБД. В большинстве случаев, когда вы используете ИЛИ для соединения условий, относящихся к разным полям или сложным выражениям, оптимизатор запросов может решить, что ему проще выполнить полное сканирование таблицы, чем пытаться комбинировать результаты поиска по разным индексам.
Рекомендации 1С по ИЛИ: Рекомендации 1С по оптимизации запросов прямо указывают на то, что использование ИЛИ в секции ГДЕ следует по возможности избегать. Если условие ИЛИ затрагивает одно и то же поле (например, Поле = Значение1 ИЛИ Поле = Значение2), его часто можно переписать с использованием оператора В (Поле В (&Значения)), который обычно более эффективен. Однако в нашем случае, когда одна часть условия полностью отключает отбор (НЕ &ЕстьОтборПоПодразделению), а другая фильтрует по полю, оператор В не применим напрямую.
Потенциальное замедление: Как и в случае с ВЫБОР КОГДА, на больших объемах данных такой запрос с ИЛИ может выполняться медленно из-за полного сканирования таблицы. Для каждой строки данных СУБД должна будет оценить обе части условия ИЛИ, что может быть неэффективно.

В редких случаях, когда одна из частей условия ИЛИ заведомо отсекает подавляющее большинство данных или когда поля хорошо индексированы и СУБД может эффективно объединить два плана выполнения, ИЛИ может быть приемлемым. Но для универсального решения, где требуется замер производительности контура 1С, мы рассмотрим более надежный вариант.

Вариант 3: Динамическое формирование текста запроса

Самый гибкий и часто наиболее производительный подход к решению задачи условного отбора — это динамическое формирование текста запроса в зависимости от того, заполнен ли необязательный параметр. Этот метод позволяет создавать запрос, который будет максимально простым и оптимизированным для конкретной ситуации, будь то отбор по подразделению или по всем подразделениям.

Объясняем преимущества:

Мы выяснили, что динамическое формирование текста запроса дает нам ряд значительных преимуществ:

Оптимальное использование индексов: Когда в запросе присутствует только необходимое условие (например, ГДЕ Подразделение = &Подразделение), СУБД может построить наиболее эффективный план выполнения, полностью используя доступные индексы. Если условие не требуется, его просто нет в запросе, что исключает любые накладные расходы на его оценку.
Высокая производительность: Отсутствие сложных логических конструкций (ВЫБОР КОГДА, ИЛИ) в основном условии секции ГДЕ позволяет СУБД работать быстрее, особенно на больших базах данных.
Гибкость и настраиваемость: Этот подход легко масштабируется для множества необязательных условий. Мы можем добавлять или удалять условия из запроса по мере необходимости, создавая очень гибкий механизм фильтрации.
Простота чтения: Сформированный динамически запрос часто более читаем, поскольку он точно отражает логику, которую мы хотим применить.

Разбираем по шагам реализацию динамического запроса:

Мы можем реализовать этот подход следующим образом:

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

Посмотрим на пример кода, предложенный в обсуждении, который прекрасно иллюстрирует этот подход:


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

МассивУсловий = Новый Массив;

// Устанавливаем параметры для обязательных условий
Запрос.УстановитьПараметр("НачалоПериода", НачалоДня(ДатаНачала));
Запрос.УстановитьПараметр("КонецПериода", КонецДня(ДатаОкончания));

// Проверяем необязательный параметр "Подразделение"
Если ЗначениеЗаполнено(Подразделение) Тогда
    МассивУсловий.Добавить("НекийДокумент.Подразделение = &Подразделение");
    Запрос.УстановитьПараметр("Подразделение", Подразделение);
КонецЕсли;

// Здесь мы можем добавить другие необязательные условия
// Например, по статьям затрат или контрагентам:
// Если ЗначениеЗаполнено(СтатьяЗатрат) Тогда
//     МассивУсловий.Добавить("НекийДокумент.СтатьяЗатрат = &СтатьяЗатрат");
//     Запрос.УстановитьПараметр("СтатьяЗатрат", СтатьяЗатрат);
// КонецЕсли;

// Формируем окончательный текст запроса
Если МассивУсловий.Количество() > 0 Тогда
    Запрос.Текст = СтрЗаменить(Запрос.Текст, "&Условие", " И " + СтрСоединить(МассивУсловий, " И "));
Иначе
    // Если дополнительных условий нет, просто убираем &Условие
    Запрос.Текст = СтрЗаменить(Запрос.Текст, " &Условие", "");
КонецЕсли;

РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();

// Далее заполняем табличную часть обработки
// Пока Выборка.Следующий() Цикл
//     НоваяСтрока = ТабличнаяЧастьОбработки.Добавить();
//     НоваяСтрока.НомерДокумента = Выборка.НомерДокумента;
//     НоваяСтрока.Подразделение = Выборка.Подразделение;
//     // ... и так далее
// КонецЦикла;

В этом примере мы видим, что базовый запрос содержит обязательные условия (фильтрация по дате) и специальный маркер &Условие. В зависимости от того, заполнено ли Подразделение, в МассивУсловий добавляется строка "НекийДокумент.Подразделение = &Подразделение". Затем, с помощью функции СтрСоединить(), все собранные условия объединяются в одну строку через " И " и подставляются вместо маркера &Условие в текст запроса. Если условий нет, маркер просто удаляется.

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

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

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

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

Выводы

В ходе нашего анализа мы рассмотрели различные подходы к реализации условного отбора по необязательному параметру в запросах 1С. Мы выяснили, что методы с использованием ВЫБОР КОГДА и ИЛИ непосредственно в секции ГДЕ запроса, хотя и кажутся простыми, могут приводить к значительным проблемам с производительностью из-за неэффективного использования индексов и затруднений для оптимизатора СУБД.

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

Всегда помните о важности индексов, правильной работе с виртуальными таблицами и избегайте неоптимальных конструкций, чтобы ваши внешние обработки и отчеты в 1С работали быстро и стабильно.

← На главную