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

Программист 1С v8.3 (Управляемые формы) 1С:ERP Управление предприятием Управленческий учет Торговля и дистрибуция

При разработке и доработке систем на платформе 1С:Предприятие мы часто сталкиваемся с задачами, требующими проверки принадлежности элемента справочника к определенной иерархии групп. Типичный пример – это проверка, разрешено ли изменять количество номенклатуры, если она относится к определенным группам. Изначальный запрос, который мы рассмотрим, предназначен для решения именно такой задачи. Он возвращает булево значение (Да/Нет) на основе довольно сложной логики:


ВЫБРАТЬ
    ВЫБОР
        КОГДА Номенклатура.Ссылка = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)
            ТОГДА ЛОЖЬ
        ИНАЧЕ ИСТИНА
    КОНЕЦ КАК Разрешено
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.ЭтоГруппа = ЛОЖЬ
    И Номенклатура.Родитель В ИЕРАРХИИ
        (ВЫБРАТЬ
            Конт1_ОбходБлокировки.Номенклатура КАК Номенклатура
        ИЗ
            РегистрСведений.Конт1_ОбходБлокировки КАК Конт1_ОбходБлокировки
        ГДЕ
            Конт1_ОбходБлокировки.Разрешено = ИСТИНА
            И Конт1_ОбходБлокировки.Номенклатура.ЭтоГруппа = ИСТИНА)
    И Номенклатура.Ссылка = &Ссылка

Первоначальный запрос имеет несколько потенциальных проблем, которые могут привести к неэффективности и усложнить поддержку:

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

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

Оптимизация запроса для получения булевого результата

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

Метод 1: Использование агрегатной функции `КОЛИЧЕСТВО()`

Мы обернем основной запрос в еще один, внешний запрос, где используем агрегатную функцию КОЛИЧЕСТВО(). Если количество найденных записей больше нуля, это означает, что условие выполнилось, и мы возвращаем ИСТИНА. В противном случае — ЛОЖЬ.

Рассмотрим пример:


ВЫБРАТЬ
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(ИсходныйЗапрос.Ссылка) > 0
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК Разрешено
ИЗ
    (ВЫБРАТЬ
        Номенклатура.Ссылка КАК Ссылка
    ИЗ
        Справочник.Номенклатура КАК Номенклатура
    ГДЕ
        Номенклатура.ЭтоГруппа = ЛОЖЬ
        И Номенклатура.Родитель В ИЕРАРХИИ
            (ВЫБРАТЬ
                Конт1_ОбходБлокировки.Номенклатура
            ИЗ
                РегистрСведений.Конт1_ОбходБлокировки КАК Конт1_ОбходБлокировки
            ГДЕ
                Конт1_ОбходБлокировки.Разрешено = ИСТИНА
                И Конт1_ОбходБлокировки.Номенклатура.ЭтоГруппа = ИСТИНА)
        И Номенклатура.Ссылка = &Ссылка) КАК ИсходныйЗапрос

В этом варианте, если внутренний запрос не возвращает ни одной строки, КОЛИЧЕСТВО(ИсходныйЗапрос.Ссылка) будет равно 0, и внешний запрос корректно вернет ЛОЖЬ.

Метод 2: Использование `ОБЪЕДИНИТЬ ВСЕ` с заведомо ложным значением

Этот метод гарантирует, что запрос всегда вернет хотя бы одну строку. Мы объединяем основной запрос, который возвращает ИСТИНА при нахождении записи, с дополнительным запросом, который всегда возвращает ЛОЖЬ. Затем из объединенного результата мы выбираем максимум или первое значение с определенным порядком, чтобы ИСТИНА имела приоритет.

Посмотрим на реализацию:


ВЫБРАТЬ
    МАКСИМУМ(ТаблицаРезультатов.Разрешено) КАК Разрешено
ИЗ
    (ВЫБРАТТЬ
        ИСТИНА КАК Разрешено,
        1 КАК Порядок
    ИЗ
        Справочник.Номенклатура КАК Номенклатура
    ГДЕ
        Номенклатура.ЭтоГруппа = ЛОЖЬ
        И Номенклатура.Родитель В ИЕРАРХИИ
            (ВЫБРАТЬ
                Конт1_ОбходБлокировки.Номенклатура
            ИЗ
                РегистрСведений.Конт1_ОбходБлокировки КАК Конт1_ОбходБлокировки
            ГДЕ
                Конт1_ОбходБлокировки.Разрешено = ИСТИНА
                И Конт1_ОбходБлокировки.Номенклатура.ЭтоГруппа = ИСТИНА)
        И Номенклатура.Ссылка = &Ссылка

    ОБЪЕДИНИТЬ ВСЕ

    ВЫБРАТЬ
        ЛОЖЬ,
        2
    ) КАК ТаблицаРезультатов

Здесь мы используем поле Порядок, чтобы в случае объединения ИСТИНА и ЛОЖЬ при выборе МАКСИМУМ (или ПЕРВЫЕ 1 с соответствующей сортировкой) всегда выбиралась ИСТИНА. Если же первый пакет не возвращает ничего, то второй пакет (с ЛОЖЬ) обеспечит одну строку в объединенной таблице, и внешний запрос вернет ЛОЖЬ.

Метод 3: Использование `ЛЕВОЕ СОЕДИНЕНИЕ` для проверки существования

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

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

Оптимизация производительности запроса с `В ИЕРАРХИИ` с использованием временных таблиц

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

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

Шаг 1: Формирование временной таблицы разрешенных групп

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


// Пакет 1: Получаем разрешенные группы
ВЫБРАТЬ РАЗЛИЧНЫЕ
    Конт1_ОбходБлокировки.Номенклатура КАК Группа
ПОМЕСТИТЬ ВтРазрешенныеГруппы
ИЗ
    РегистрСведений.Конт1_ОбходБлокировки КАК Конт1_ОбходБлокировки
ГДЕ
    Конт1_ОбходБлокировки.Разрешено = ИСТИНА
    И Конт1_ОбходБлокировки.Номенклатура.ЭтоГруппа = ИСТИНА
;

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

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


// Пакет 2: Основной запрос с использованием ВтРазрешенныеГруппы
ВЫБРАТЬ
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(Номенклатура.Ссылка) > 0
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК Разрешено
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.ЭтоГруппа = ЛОЖЬ
    И Номенклатура.Родитель В ИЕРАРХИИ (ВЫБРАТЬ Группа ИЗ ВтРазрешенныеГруппы)
    И Номенклатура.Ссылка = &Ссылка

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

Шаг 3: Полное устранение `В ИЕРАРХИИ` для проверки одной номенклатуры (продвинутый сценарий)

Если нам критически важно избавиться от В ИЕРАРХИИ для проверки одной конкретной номенклатуры (как в случае с параметром &Ссылка), мы можем вручную собрать всю иерархию родителей для этой номенклатуры, а затем проверить пересечение с ВтРазрешенныеГруппы.

Пакет 1: Получаем разрешенные группы (аналогично предыдущему)


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Конт1_ОбходБлокировки.Номенклатура КАК Группа
ПОМЕСТИТЬ ВтРазрешенныеГруппы
ИЗ
    РегистрСведений.Конт1_ОбходБлокировки КАК Конт1_ОбходБлокировки
ГДЕ
    Конт1_ОбходБлокировки.Разрешено = ИСТИНА
    И Конт1_ОбходБлокировки.Номенклатура.ЭтоГруппа = ИСТИНА
;

Пакет 2: Получаем всех родителей текущей номенклатуры (включая саму номенклатуру)

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

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


// Пакет 2: Получаем родителя текущей номенклатуры
ВЫБРАТЬ
    Номенклатура.Родитель КАК РодительТекущейНоменклатуры
ПОМЕСТИТЬ ВтРодитель
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка = &Ссылка
    И Номенклатура.ЭтоГруппа = ЛОЖЬ
;

// Пакет 3: Проверяем, входит ли родитель в иерархию разрешенных групп
ВЫБРАТЬ
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(ВтРодитель.РодительТекущейНоменклатуры) > 0
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК Разрешено
ИЗ
    ВтРодитель КАК ВтРодитель
ГДЕ
    ВтРодитель.РодительТекущейНоменклатуры В ИЕРАРХИИ (ВЫБРАТЬ Группа ИЗ ВтРазрешенныеГруппы)
    И НЕ ВтРодитель.РодительТекущейНоменклатуры = ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)

Это по-прежнему использует В ИЕРАРХИИ, но уже с одним параметром, что часто работает быстрее. Если же требуется полностью избавиться от В ИЕРАРХИИ в запросах, то это более сложная задача, требующая построения иерархии вручную в коде или использования рекурсивных запросов, если СУБД это поддерживает (и 1С их транслирует).

Более продвинутый способ, если нужен список всех предков текущей номенклатуры для проверки, может быть таким:


// Пакет 2: Формируем список предков для &Ссылка
ВЫБРАТЬ
    &Ссылка КАК ЭлементИлиПредок
ПОМЕСТИТЬ ВтПредки
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка = &Ссылка;

// Итеративно добавляем родителей, пока не достигнем корня или пустой ссылки
// (Этот цикл выполняется в коде 1С, а не в самом запросе)
// Пример логики в коде 1С:
// ТекущийРодитель = НоменклатураСсылка.Родитель;
// Пока ТекущийРодитель <> Справочники.Номенклатура.ПустаяСсылка Цикл
//    Добавить ТекущийРодитель во ВтПредки;
//    ТекущийРодитель = ТекущийРодитель.Родитель;
// КонецЦикла;

// Пакет 3: Проверяем пересечение предков с разрешенными группами
ВЫБРАТЬ
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(ВтПредки.ЭлементИлиПредок) > 0
            ТОГДА ИСТИНА
        ИНАЧЕ ЛОЖЬ
    КОНЕЦ КАК Разрешено
ИЗ
    ВтПредки КАК ВтПредки
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтРазрешенныеГруппы КАК ВтРазрешенныеГруппы
        ПО ВтПредки.ЭлементИлиПредок = ВтРазрешенныеГруппы.Группа

Данный подход, где сбор иерархии происходит программно или через множественные пакеты запросов, позволяет полностью избежать В ИЕРАРХИИ в финальной проверке, заменив его простым соединением.

Архитектурные подходы к решению проблемы

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

1. Использование дополнительного реквизита или свойства номенклатуры

Один из самых эффективных способов ускорить проверку — это добавить к справочнику Номенклатура новый булевый реквизит, например, РазрешеноКИзменению. Затем мы можем поддерживать актуальность этого реквизита следующим образом:

Регламентное задание: Настроим регламентное задание, которое периодически (например, раз в час или раз в день) будет пересчитывать значение этого реквизита для всех элементов номенклатуры, основываясь на правилах вхождения в разрешенные группы.
Подписки на события: Создадим подписки на события для справочника Номенклатура (например, при записи элемента или группы, при изменении родителя) и для регистра сведений Конт1_ОбходБлокировки. При изменении разрешенных групп или иерархии номенклатуры, соответствующий код в подписке будет обновлять реквизит РазрешеноКИзменению для затронутых элементов.

Преимущества:

Запрос проверки становится максимально простым и быстрым:


ВЫБРАТЬ
    Номенклатура.РазрешеноКИзменению
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка = &Ссылка

Избавляемся от сложных иерархических запросов в критичных точках кода.

Недостатки:

Требуется дополнительная логика для поддержания актуальности реквизита.
Первоначальное заполнение или массовое изменение правил может быть трудоемким для большого объема номенклатуры (например, 50 тысяч позиций).

2. Использование механизма "Сегменты номенклатуры" (для 1С:ERP, 1С:Управление торговлей)

Если мы работаем с конфигурациями, такими как 1С:ERP или 1С:Управление торговлей, у нас есть мощный типовой механизм "Сегменты номенклатуры". Этот механизм разработан специально для гибкой классификации товаров по различным правилам, включая иерархические. Сегменты могут быть:

Динамическими: правила формирования сегмента задаются в конструкторе и сегмент пересчитывается при обращении к нему или регламентным заданием.
Статическими: состав сегмента определяется вручную или однократно по правилам.

Для нашей задачи мы можем создать динамический сегмент номенклатуры, который будет включать все элементы, чьи родители (или предки) попадают в список разрешенных групп из регистра сведений. Правила формирования такого сегмента можно настроить с использованием условий "Входит в группу/группы" или "Не входит в группу/группы".

Как это работает:

Создаем новый элемент справочника СегментыНоменклатуры, например, "Разрешенная к изменению номенклатура".
Настраиваем правила формирования этого сегмента, используя конструктор условий. Мы можем указать, что в сегмент должна входить номенклатура, у которой "Родитель В ГРУППЕ ИЗ СПИСКА" (где список групп берется из нашего регистра Конт1_ОбходБлокировки или просто задается).
Настраиваем расписание обновления сегмента (регламентное задание), чтобы состав сегмента всегда был актуальным.

Использование в запросе:

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


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

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

Обобщенные рекомендации по оптимизации запросов 1С

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

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

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

← На главную