Как в запросе 1С исключить номенклатуру, входящую в определенную группу, без потери производительности?

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

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

Проблема использования условия НЕ В ИЕРАРХИИ

Интуитивно первое решение, которое приходит в голову, выглядит следующим образом: добавить в секцию ГДЕ условие НЕ Номенклатура В ИЕРАРХИИ(&Группа). Посмотрим на пример исходного запроса к табличной части документа:

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

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

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

Оптимальное решение: использование временной таблицы и левого соединения

Проанализируем наиболее эффективный способ решения задачи. Чтобы избежать полного перебора и снять нагрузку с СУБД, запрос разделяется на два этапа:

1. Сначала в отдельную временную таблицу отбираются все элементы, которые входят в исключаемую группу.
2. Затем выполняется выборка основных данных (например, строк документа), к которой левым соединением подключается сформированная временная таблица. В секции ГДЕ проверяется отсутствие связи через условие ЕСТЬ NULL.

Посмотрим на практический пример реализации пакета запросов:

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ДокументОснованиеТовары.НомерСтроки КАК НомерСтроки,
    ДокументОснованиеТовары.Номенклатура КАК Номенклатура,
    ДокументОснованиеТовары.ЕдиницаИзмерения КАК ЕдиницаИзмерения,
    ДокументОснованиеТовары.Количество КАК Количество,
    ДокументОснованиеТовары.Цена КАК Цена,
    ДокументОснованиеТовары.Сумма КАК Сумма
ИЗ
    Документ.ОтчетОРозничныхПродажах.Товары КАК ДокументОснованиеТовары
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеЭлементы КАК ВТ_ИсключаемыеЭлементы
        ПО ДокументОснованиеТовары.Номенклатура = ВТ_ИсключаемыеЭлементы.Ссылка
ГДЕ
    ДокументОснованиеТовары.Ссылка = &Ссылка
    И ВТ_ИсключаемыеЭлементы.Ссылка ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
    НомерСтроки

Рассмотрим подробнее важные нюансы этого запроса:

1. Индексация временной таблицы: Конструкция ИНДЕКСИРОВАТЬ ПО Ссылка позволяет СУБД мгновенно сопоставлять строки при выполнении операции ЛЕВОЕ СОЕДИНЕНИЕ.
2. Проверка на NULL: Для отсечения строк, попавших в исключаемую группу, используется условие ВТ_ИсключаемыеЭлементы.Ссылка ЕСТЬ NULL. Это стандартный и наиболее быстрый синтаксис языка запросов 1С. Конструкция ЕстьNULL(ВТ_ИсключаемыеЭлементы.Ссылка, 0) = 0 также работоспособна, но является избыточной.

Нюансы получения группы через встроенный язык

Разберем особенности программного поиска исключаемой группы перед передачей её в параметры запроса. Использование метода НайтиПоНаименованию требует аккуратности:

// Неточный поиск может вернуть не ту группу при наличии похожих названий
Группа = Справочники.Номенклатура.НайтиПоНаименованию("Материалы");

// Безопасный вариант с точным соответствием наименования:
Группа = Справочники.Номенклатура.НайтиПоНаименованию("Материалы", Истина);

Важные рекомендации по выбору элементов:

1. Поиск по наименованию чувствителен к изменению названий пользователями и наличию дублей. По возможности используйте поиск по уникальному коду (НайтиПоКоду) либо механизм предопределенных элементов.
2. Если иерархия справочника одноуровневая (все позиции лежат строго внутри группы без подпапок), условие в запросе можно упростить до прямого сравнения: ГДЕ Номенклатура.Родитель <> &Группа, что исключит необходимость использования оператора В ИЕРАРХИИ.
3. Если дерево группы невелико, можно предварительно программно выгрузить список ссылок в массив и передать его в параметр с условием НЕ Номенклатура В (&МассивИсключений).