Как правильно использовать условие «В ИЕРАРХИИ» в запросах 1С для справочников с иерархией элементов и оптимизировать его производительность

Программист 1С v8.3 (Управляемые формы) 1С:Зарплата и Управление Персоналом IT и автоматизация бизнеса

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

Разбираем работу оператора «В ИЕРАРХИИ» для иерархии элементов

Справочники в 1С могут иметь два типа иерархии: «Иерархия групп и элементов» и «Иерархия элементов». Во втором случае разделения на папки и элементы нет — родителем для одного элемента выступает другой полнофункциональный элемент справочника (классический пример — справочник Подразделения).

Когда мы пишем простое равенство в условиях запроса:


ГДЕ
    ПлановыеНачисления.Подразделение = &Подразделение

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

Для решения этой задачи в языке запросов 1С предусмотрен специализированный оператор В ИЕРАРХИИ. Напишем правильное базовое условие:


ГДЕ
    ПлановыеНачисления.Подразделение В ИЕРАРХИИ(&Подразделение)

Рассмотрим, как это работает «под капотом». Для платформы 1С нет разницы, какой тип иерархии настроен в метаданных справочника. При выполнении этого оператора платформа автоматически транслирует его в SQL-запрос, который обращается к служебным таблицам связей или строит рекурсивный запрос (в зависимости от СУБД) для получения списка всех подчиненных элементов (включая сам выбранный элемент). В результате мы получаем полную выборку по всей ветке иерархии.

Проблема пустого параметра: почему «ВЫБОР КОГДА» в секции «ГДЕ» — это плохая практика

Очень часто возникает сопутствующая задача: если пользователь не выбрал конкретное подразделение в отчете (параметр пустой), запрос должен вывести данные по абсолютно всем подразделениям компании.

На форумах часто встречается решение с использованием конструкции ВЫБОР КОГДА в секции ГДЕ (как в Сообщении 5 нашей темы):


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

Внимание! Использовать такую конструкцию в реальных базах данных крайне не рекомендуется. Проанализируем причины:

Проблема оптимизатора СУБД. Наличие условной логики CASE WHEN (которая получается при трансляции ВЫБОР КОГДА) внутри секции WHERE лишает оптимизатор SQL-сервера возможности использовать индексы по полю Подразделение. СУБД вынуждена прибегнуть к полному сканированию таблицы (Table Scan) вместо быстрого поиска по индексу (Index Seek). На больших базах это приведет к колоссальным задержкам.
Проблема пустой ссылки в операторе «В ИЕРАРХИИ». Согласно стандартам разработки фирмы «1С», передача пустой ссылки в качестве параметра оператора В ИЕРАРХИИ заставляет СУБД проверять на соответствие вообще все элементы справочника. Это приводит к избыточной нагрузке на сервер.

Три правильных способа обработки пустого параметра

Рассмотрим, как решить задачу обхода пустого значения параметра красиво, быстро и по стандартам разработки 1С.

Способ 1. Использование Системы компоновки данных (СКД)

Если вы разрабатываете отчет на СКД, вам вообще не нужно писать логику проверки на пустоту в самом тексте запроса. Достаточно оставить чистое условие:


ГДЕ
    ПлановыеНачисления.Подразделение В ИЕРАРХИИ(&Подразделение)

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

Способ 2. Динамическое формирование текста запроса в коде

Если запрос выполняется программно в модуле, лучшим решением будет динамическая сборка текста. Проанализируем пример кода:


ТекстЗапроса = 
    "ВЫБРАТЬ
    |	ПлановыеНачисления.Сотрудник КАК Сотрудник,
    |	ПлановыеНачисления.Размер КАК Размер
    |ИЗ
    |	РегистрСведений.ПлановыеНачисления КАК ПлановыеНачисления";

Если ЗначениеЗаполнено(ПараметрПодразделение) Тогда
    ТекстЗапроса = ТекстЗапроса + "
    |ГДЕ
    |	ПлановыеНачисления.Подразделение В ИЕРАРХИИ(&Подразделение)";
    Запрос.УстановитьПараметр("Подразделение", ПараметрПодразделение);
КонецЕсли;

Запрос.Текст = ТекстЗапроса;

При таком подходе СУБД получает кристально чистый план выполнения в обоих сценариях работы программы.

Способ 3. Использование оператора «ИЛИ» (компромиссный вариант)

Если текст запроса должен оставаться жестко статичным, замените конструкцию ВЫБОР КОГДА на логическое ИЛИ:


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

Современные оптимизаторы СУБД гораздо лучше справляются с оператором ИЛИ, успешно строя план запроса с использованием индексов (Index Spool / Index Seek), в отличие от конструкций с ВЫБОР.

Почему пропали надбавки: ловушка левого соединения с иерархическим фильтром

Перейдем к разбору проблемы из сообщений 7 и 8. Пользователь пожаловался, что после внедрения фильтра по иерархии у него пропали надбавки/выплаты, которые ранее выводились. Это классическая архитектурная ошибка, связанная с логикой работы соединений таблиц.

Выясним причину. Изначально в запросе выполнялось ЛЕВОЕ СОЕДИНЕНИЕ основной таблицы (например, списка сотрудников или выплат) со вспомогательной таблицей (ПлановыеНачисления):


ВЫБРАТЬ
    Выплаты.Сотрудник,
    Выплаты.ВидВыплаты,
    ПлановыеНачисления.Подразделение,
    ПлановыеНачисления.Размер
ИЗ
    Справочник.Сотрудники КАК Выплаты
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПлановыеНачисления КАК ПлановыеНачисления
        ПО Выплаты.Ссылка = ПлановыеНачисления.Сотрудник

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

Что происходит, когда мы добавляем условие фильтрации в секцию ГДЕ?


ГДЕ
    ПлановыеНачисления.Подразделение В ИЕРАРХИИ(&Подразделение)

Секция ГДЕ применяется к результату соединения после его выполнения. Для тех строк, где плановых начислений не было, в поле ПлановыеНачисления.Подразделение находится значение NULL. Условие NULL В ИЕРАРХИИ (&Подразделение) возвращает результат ЛОЖЬ (или UNKNOWN).

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

Как обойти ограничение левого соединения с «В ИЕРАРХИИ»

В стандартном языке SQL для решения этой проблемы достаточно перенести условие фильтрации из секции WHERE непосредственно в секцию связи ON (левое соединение по условию). Но в платформе 1С действует жесткое синтаксическое ограничение: в условиях связи таблиц (секция ПО) запрещено использовать оператор В ИЕРАРХИИ. Попытка написать такой запрос вызовет ошибку компиляции.

Посмотрим на правильное решение этой проблемы через механизм Временных таблиц. Мы разобьем один сложный запрос на два простых и последовательных шага.

Шаг 1. Предварительный отбор во временную таблицу. Мы выберем нужные нам данные из правой таблицы и наложим фильтр по иерархии подразделений еще до момента всяких соединений:


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

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

Шаг 2. Левое соединение с подготовленной временной таблицей. Теперь мы можем безопасно соединить основную таблицу с нашей временной таблицей. Поскольку фильтрация уже произошла, левое соединение отработает корректно и данные не пропадут:


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

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

← На главную