Как оптимизировать запрос с условием НЕ В в 1С

← На главную

При разработке сложных отчетов или обработок на платформе 1С:Предприятие программисты часто сталкиваются с ситуацией, когда запрос начинает работать медленно. Одной из классических причин низкой производительности является использование конструкции НЕ В (аналог NOT IN в SQL). В этой статье мы подробно разберем, почему это происходит, какими способами можно оптимизировать такие запросы и какие нюансы СУБД следует учитывать.

Проблема использования конструкции НЕ В

Конструкция ГДЕ Поле НЕ В (ВЫБРАТЬ ... ) часто приводит к тому, что оптимизатор СУБД (особенно в PostgreSQL) выбирает не самый эффективный план выполнения, например, вложенные циклы (Nested Loops) или полное сканирование таблиц. Кроме того, существует фундаментальная проблема трехзначной логики SQL: если подзапрос внутри НЕ В вернет хотя бы одну строку со значением NULL, то весь основной запрос вернет пустой результат. Это заставляет СУБД выполнять дополнительные проверки, что замедляет работу.

Рассмотрим основные способы решения этой задачи, от классических до современных.

Метод 1: Замена на ЛЕВОЕ СОЕДИНЕНИЕ с проверкой на NULL

Это «золотой стандарт» оптимизации в 1С. Вместо проверки вхождения через условие ГДЕ, мы присоединяем таблицу, данные из которой нужно исключить, и отфильтровываем те строки, для которых соединение не удалось.

Рассмотрим пример: Нам нужно найти всю номенклатуру, по которой не было продаж (нет в табличной части документов реализации).


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

Почему это работает быстрее? При таком подходе СУБД может использовать алгоритм Hash Anti Join. Вместо того чтобы для каждой строки справочника проверять весь список продаж, СУБД строит хеш-таблицу в памяти, что значительно ускоряет сопоставление данных.

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

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

Разберем по шагам:

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Продажи.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_ИсключаемаяНоменклатура
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК Продажи
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

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

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

Метод 3: Использование оператора ИСКЛЮЧАЯ (EXCEPT)

В современных версиях платформы (8.3.11+) появился оператор ИСКЛЮЧАЯ. Он позволяет вычесть одно множество данных из другого на уровне синтаксиса языка запросов, что делает код более читаемым и часто транслируется в эффективные команды СУБД.


ВЫБРАТЬ Ссылка ИЗ Справочник.Номенклатура
ИСКЛЮЧАЯ
ВЫБРАТЬ Номенклатура ИЗ Документ.РеализацияТоваровУслуг.Товары

Этот метод избавляет разработчика от необходимости вручную прописывать ЛЕВОЕ СОЕДИНЕНИЕ и проверку на NULL, при этом обеспечивая высокую производительность.

Метод 4: Эмуляция NOT EXISTS через коррелированный подзапрос

В некоторых случаях, когда нужно проверить условие по нескольким полям одновременно, удобно использовать конструкцию с ИСТИНА В (...). Это аналог EXISTS в SQL, который в версии с отрицанием работает как NOT EXISTS.


ВЫБРАТЬ
    ДД.Ссылка
ИЗ
    Справочник.Номенклатура КАК ДД
ГДЕ
    НЕ ИСТИНА В (
        ВЫБРАТЬ ПЕРВЫЕ 1
            ИСТИНА
        ИЗ
            Документ.РеализацияТоваровУслуг.Товары КАК ТТ
        ГДЕ
            ТТ.Номенклатура = ДД.Ссылка
    )

По результатам тестов на СУБД PostgreSQL, такой метод может уступать левому соединению, но он все равно значительно быстрее, чем классический НЕ В (ВЫБРАТЬ Поле ...), так как оптимизатору проще «понять» намерение программиста.

Оптимизация в виртуальных таблицах

Проанализируем работу с виртуальными таблицами, такими как Остатки или ОстаткиИОбороты. Если вам нужно получить остатки по всей номенклатуре, кроме определенного списка, использование НЕ В в параметрах виртуальной таблицы может замедлить запрос, так как СУБД не сможет эффективно использовать индексы итогов.

Рекомендация: Если список исключений велик, лучше получить все остатки (без фильтра по номенклатуре) во временную таблицу, а затем отсечь ненужное левым соединением на этапе выборки из этой временной таблицы.

Сравнение производительности

Рассмотрим результаты замеров (на примере выборки 16 000 записей из справочника объемом 17 000 записей):

НЕ В (подзапрос к физической таблице): ~1.0 сек.
ЛЕВОЕ СОЕДИНЕНИЕ + NULL: ~0.9 сек. (самый быстрый).
Коррелированный подзапрос (эмуляция NOT EXISTS): ~6.9 сек. (медленно из-за построчного выполнения).
Использование временной таблицы + НЕ В: ~1.0 сек.

Вывод: Для большинства задач наиболее стабильным и быстрым решением остается ЛЕВОЕ СОЕДИНЕНИЕ с фильтром по NULL. Оно одинаково хорошо работает как на MS SQL Server, так и на PostgreSQL, обеспечивая предсказуемое время выполнения.

Помните, что прежде чем слепо следовать рекомендациям по созданию временных таблиц на каждый запрос, стоит посмотреть на объем данных. Если в таблице 5–10 записей, обычное условие В (&СписокЗначений), переданное через параметры, будет работать быстрее всего, так как не требует накладных расходов на создание объектов в базе данных.