Почему запрос с фильтром В ИЕРАРХИИ в параметрах виртуальной таблицы работает медленнее, чем в секции ГДЕ?

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

Разбор ситуации: два варианта одного запроса

Рассмотрим два запроса к регистру накопления Продажи.Обороты. Нам необходимо получить сумму оборота по количеству, отфильтровав данные по иерархии складов и исключив определенную иерархию контрагентов.

Вариант №1: Фильтрация в секции ГДЕ

В этом случае виртуальная таблица вызывается без параметров отбора по измерениям, а фильтры накладываются уже на результат выборки.

ВЫБРАТЬ
    СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачДата, &КонДата, , ) КАК ПродажиОбороты
ГДЕ
    ПродажиОбороты.Склад В ИЕРАРХИИ(&Склад)
    И НЕ ПродажиОбороты.Контрагент В ИЕРАРХИИ(&Контрагент)

Результат: 3 секунды.

Вариант №2: Фильтрация в параметрах виртуальной таблицы

Здесь мы следуем классическому совету и передаем условия непосредственно в параметры виртуальной таблицы Обороты.

ВЫБРАТЬ
    СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот
ИЗ
    РегистрНакопления.Продажи.Обороты(
        &НачДата,
        &КонДата,
        ,
        Склад В ИЕРАРХИИ(&Склад)
            И НЕ Контрагент В ИЕРАРХИИ(&Контрагент)
    ) КАК ПродажиОбороты

Результат: 11 секунд.

Казалось бы, второй вариант должен быть быстрее, так как объем данных ограничивается «на входе». Но на практике мы видим обратную картину. Разберемся, почему так происходит.

Причина №1: Особенности трансляции оператора В ИЕРАРХИИ в SQL

Проанализируем механику работы платформы. Когда 1С встречает оператор В ИЕРАРХИИ, она не может передать его СУБД напрямую, так как в SQL нет встроенного понятия иерархии объектов 1С. Вместо этого платформа выполняет следующие действия:

1. Создает временную таблицу в базе данных (например, с именем типа #tt1).
2. Выгружает в эту таблицу все ссылки, которые входят в указанную иерархию (родительский элемент и все его дочерние узлы).
3. Заменяет условие В ИЕРАРХИИ в SQL-запросе на соединение (JOIN) с этой временной таблицей или на условие IN (SELECT...) из этой таблицы.

В первом варианте (с ГДЕ) СУБД сначала формирует результат виртуальной таблицы Обороты по индексу периода, а затем выполняет соединение с временными таблицами иерархии. Это прямолинейный и предсказуемый путь для оптимизатора SQL.

Во втором варианте условие попадает внутрь сложного подзапроса, который формирует виртуальную таблицу. Оптимизатор SQL Server часто ошибается в оценке кардинальности (Cardinality Estimation) — он не может точно угадать, сколько записей вернет иерархия (особенно если складов больше 1000). В итоге выбирается неэффективный план запроса, например, Nested Loops (вложенные циклы) вместо Hash Join, что приводит к многократному сканированию таблиц.

Причина №2: Отказ от использования таблиц итогов

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

Причина №3: Влияние отрицания (НЕ В ИЕРАРХИИ)

Оператор НЕ является «тяжелым» для любого оптимизатора запросов. В параметрах виртуальной таблицы условие НЕ Контрагент В ИЕРАРХИИ превращается в сложную проверку отсутствия записи в подзапросе (NOT EXISTS) или LEFT OUTER JOIN с проверкой на NULL. Когда такое условие применяется к виртуальной таблице, которая сама по себе является подзапросом, количество промежуточных логических чтений в SQL возрастает в геометрической прогрессии.

Как оптимизировать: правильный подход через временные таблицы

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

Шаг 1: Получение списка всех элементов иерархии во временную таблицу

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

// В коде 1С подготовим массив ссылок
МассивСкладов = Справочники.Склады.ВыбратьИерархически(Объект.Склад);

// Или в запросе через временную таблицу
ВЫБРАТЬ
    Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВТ_Склады
ИЗ
    Справочник.Склады КАК Склады
ГДЕ
    Склады.Ссылка В ИЕРАРХИИ(&Склад)
;
ИНДЕКСИРОВАТЬ ПО
    Склад

Шаг 2: Использование временной таблицы в параметрах

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

ВЫБРАТЬ
    СУММА(ПродажиОбороты.КоличествоОборот) КАК КоличествоОборот
ИЗ
    РегистрНакопления.Продажи.Обороты(
        &НачДата,
        &КонДата,
        ,
        Склад В (ВЫБРАТЬ Склад ИЗ ВТ_Склады)
            И НЕ Контрагент В (ВЫБРАТЬ Контрагент ИЗ ВТ_Контрагенты)
    ) КАК ПродажиОбороты

Выводы и рекомендации

Проанализировав ситуацию, мы можем сформулировать следующие правила оптимизации запросов с иерархией:

1. Избегайте В ИЕРАРХИИ в параметрах ВТ, если в иерархии много элементов (как в нашем примере — более 1000 складов). Это «старый прикол» платформы, связанный со сложностью построения планов запросов СУБД.
2. Используйте секцию ГДЕ для иерархических отборов, если запрос выполняется быстро (в пределах нескольких секунд). Это проще в написании и часто эффективнее для СУБД, так как фильтр накладывается на уже отобранный набор данных.
3. Применяйте временные таблицы для подготовки списков ссылок, если требуется максимальная производительность. Предварительная выборка элементов иерархии с последующей индексацией временной таблицы — самый надежный способ получить стабильное время выполнения запроса.
4. Проверяйте актуальность статистик SQL. Если разрыв в скорости между ГДЕ и параметрами ВТ аномально велик, это может свидетельствовать о том, что SQL Server опирается на устаревшие данные о распределении значений в таблицах.

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