Как оптимизировать соединение в запросе 1С при использовании условий ВЫБОР или ИЛИ?

← На главную

При работе с запросами в 1С программисты часто сталкиваются с ситуациями, когда логика соединения двух таблиц зависит от заполненности тех или иных полей. Использование конструкции ВЫБОР (или CASE на уровне SQL) непосредственно в условии ПО (JOIN) является одной из самых распространенных причин низкой производительности системы. В этой статье мы подробно разберем, почему так происходит, и рассмотрим эффективные методы оптимизации таких запросов.

Проблема: Почему ВЫБОР в соединении работает медленно?

Рассмотрим типичную ситуацию. У нас есть две временные таблицы: втУслуги и втСостояниеДокументов. Нам нужно соединить их по документам Заказ и Акт. Логика такова: если Заказ пустой — соединяем только по Акту, если Акт пустой — только по Заказу, а если заполнены оба — по обоим полям сразу.

Исходный код запроса выглядел так:


втУслуги КАК втУслуги
    ЛЕВОЕ СОЕДИНЕНИЕ втСостояниеДокументов КАК втСостояниеДокументов
    ПО (ВЫБОР
            КОГДА втУслуги.Заказ = ЗНАЧЕНИЕ(Документ.ЗаказКлиентаМИС.ПустаяСсылка)
                ТОГДА втУслуги.Акт = втСостояниеДокументов.Акт
            КОГДА втУслуги.Акт = ЗНАЧЕНИЕ(Документ.АктВыполненныхРаботПоЗаказу.ПустаяСсылка)
                ТОГДА втУслуги.Заказ = втСостояниеДокументов.Заказ
            ИНАЧЕ втУслуги.Заказ = втСостояниеДокументов.Заказ
                    И втУслуги.Акт = втСостояниеДокументов.Акт
        КОНЕЦ)

Проблема этого подхода заключается в том, что такое условие не является SARGable (Search Argumentable). Для SQL-сервера это означает, что он не может заранее определить, по какому индексу искать данные. В результате вместо эффективного поиска по индексу (Index Seek), СУБД вынуждена выполнять полное сканирование таблиц (Table Scan или Index Scan) и для каждой строки вычислять условие заново. В сложных отчетах это приводит к тому, что время выполнения (duration) увеличивается в десятки и сотни раз.

Решение №1: Декомпозиция через ОБЪЕДИНИТЬ ВСЕ

Самым надежным способом оптимизации в данном случае является разделение одного сложного запроса на несколько простых, результаты которых объединяются с помощью ОБЪЕДИНИТЬ ВСЕ. Это позволяет оптимизатору SQL построить эффективный план для каждой ветки объединения.

Разберем реализацию этого метода по шагам:

Первая ветка: Выбираем данные, где Заказ пустой. Здесь мы делаем простое соединение по полю Акт.
Вторая ветка: Выбираем данные, где Заказ заполнен, но Акт пустой. Соединяем по полю Заказ.
Третья ветка: Выбираем данные, где заполнены оба поля. Соединяем по обоим полям.

Пример оптимизированного кода:


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

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

ВЫБРАТЬ ...
ИЗ втУслуги КАК втУслуги
    ЛЕВОЕ СОЕДИНЕНИЕ втСостояниеДокументов КАК втСостояниеДокументов
    ПО втУслуги.Заказ = втСостояниеДокументов.Заказ
ГДЕ втУслуги.Заказ <> ЗНАЧЕНИЕ(Документ.ЗаказКлиентаМИС.ПустаяСсылка)
    И втУслуги.Акт = ЗНАЧЕНИЕ(Документ.АктВыполненныхРаботПоЗаказу.ПустаяСсылка)

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

ВЫБРАТЬ ...
ИЗ втУслуги КАК втУслуги
    ЛЕВОЕ СОЕДИНЕНИЕ втСостояниеДокументов КАК втСостояниеДокументов
    ПО втУслуги.Заказ = втСостояниеДокументов.Заказ
    И втУслуги.Акт = втСостояниеДокументов.Акт
ГДЕ втУслуги.Заказ <> ЗНАЧЕНИЕ(Документ.ЗаказКлиентаМИС.ПустаяСсылка)
    И втУслуги.Акт <> ЗНАЧЕНИЕ(Документ.АктВыполненныхРаботПоЗаказу.ПустаяСсылка)

Важный момент: Обратите внимание на использование ОБЪЕДИНИТЬ ВСЕ вместо простого ОБЪЕДИНИТЬ. Это исключает лишнюю операцию поиска дублей, что еще немного ускоряет запрос. Также важно следить за условиями в ГДЕ, чтобы одни и те же строки не попали в разные ветки объединения.

Решение №2: Подготовка данных во временных таблицах

Если запрос все еще работает недостаточно быстро, проанализируем совет из практики: предварительное разделение данных. Мы можем сначала разбить втУслуги на три отдельные временные таблицы по тем же условиям (пустой заказ, пустой акт, оба заполнены).

Почему это может быть быстрее? Когда мы работаем с большой таблицей в UNION ALL, SQL-сервер может повторно считывать данные. Если же мы заранее отфильтруем данные в маленькие индексированные временные таблицы, последующее соединение с втСостояниеДокументов пройдет мгновенно.

Дополнительные рекомендации по оптимизации

При реализации любого из этих методов следует учитывать еще несколько технических нюансов:

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

Проанализировав ситуацию, мы видим, что замена одного сложного соединения на три простых с последующим объединением может сократить время выполнения запроса с 50 000 условных единиц до менее чем 1 000. Это наглядный пример того, как понимание работы SQL-оптимизатора помогает писать производительный код в 1С.