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

← На главную

При разработке и поддержке крупных конфигураций на платформе 1С:Предприятие (таких как «Управление торговлей», «ERP Управление предприятием» или «Комплексная автоматизация») разработчики часто сталкиваются с проблемой резкого падения производительности запросов. Особенно остро этот вопрос встает при попытке связать документы через реквизиты составного типа данных. Запросы, которые ранее выполнялись мгновенно, начинают вызывать длительные зависания системы или вовсе аварийно завершаться по таймауту СУБД. Для диагностики подобных проблем на уровне базы данных можно использовать обработку для анализа нагрузки и запросов на SQL-сервере.

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

Анализ исходной проблемы и ложные пути оптимизации

Рассмотрим ситуацию, с которой обратился автор темы. Ему потребовалось связать документ ЗаказПоставщику с документом РеализацияТоваровУслуг. Прямой связи между ними нет, а единственная цепочка связывания идет через составной реквизит ДокументОснование (который ссылается на ЗаказКлиента).

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

Вариант 1 (простая логическая цепочка):


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

Вариант 2 (использование конструкции ВЫБОР):


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

Выясним, какой из этих способов предпочтительнее и сработает ли здесь классическое правило «короткой схемы» вычислений (Short-circuit evaluation), когда при ложности первого условия СУБД не проверяет остальные условия в связке И (AND).

В отличие от объектного встроенного языка 1С, где логические выражения вычисляются строго слева направо до первого ложного значения, оптимизатор СУБД (MS SQL Server, PostgreSQL) не гарантирует порядок вычисления условий в секции ПО (ON) или ГДЕ (WHERE). Оптимизатор строит план запроса на основе статистических данных, стоимости операций и наличия индексов. Он вполне может начать проверку с последнего условия, если сочтет его более простым или селективным.

Попытка «помочь» оптимизатору с помощью конструкции ВЫБОР КОГДА (Вариант 2) приводит к серьезной ошибке. Такие конструкции делают условие соединения не-SARGable (Search Argument Able). Это означает, что СУБД принципиально не сможет использовать индексный поиск (Index Seek) по таблице реализаций и перейдет к полному сканированию индексов или таблиц (Scan). Таким образом, Вариант 2 гарантированно будет работать медленнее и потреблять больше ресурсов сервера.

Главный «косяк» запроса: разыменование составных полей и неявные соединения

Проанализируем полный текст запроса, который привел к зависаниям в системе (сообщение №27). Обратим внимание на то, как именно автор пытается связать таблицы и какие поля он выбирает во втором пакете запроса:


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

Здесь кроются сразу две критические архитектурные ошибки, каждая из которых способна «положить» базу данных при росте объема информации:

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

Пошаговая инструкция по оптимизации запроса

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

Шаг 1: Получаем и типизируем составное поле в первой временной таблице

Избавимся от неявных соединений и обращений через точку во втором запросе пакета. Перенесем получение поля ДокументОснование в первый запрос (где мы формируем временную таблицу ВТ_Даты). Поскольку нам для связи необходим только ЗаказКлиента, мы жестко ограничим тип этого реквизита с помощью функции ВЫРАЗИТЬ.

Посмотрим, как изменится первый запрос пакета:


ВЫБРАТЬ
    ЗаказПоставщикуТовары.Ссылка КАК Ссылка,
    ЗаказПоставщикуТовары.ДатаПоступления КАК ДатаДоставки,
    // Явно получаем и типизируем ДокументОснование на этапе сборки ВТ
    ВЫРАЗИТЬ(ЗаказПоставщикуТовары.Ссылка.ДокументОснование КАК Документ.ЗаказКлиента) КАК ЗаказКлиента
ПОМЕСТИТЬ ВТ_Даты
ИЗ
    Документ.ЗаказПоставщику.Товары КАК ЗаказПоставщикуТовары
ГДЕ
    ЗаказПоставщикуТовары.Ссылка.Проведен
    И ЗаказПоставщикуТовары.Ссылка.СамоВывоз
    И ВЫБОР
            КОГДА &ТолькоМаркетПлейс
                ТОГДА ЗаказПоставщикуТовары.Ссылка.МаркетПлейс
            ИНАЧЕ ИСТИНА
        КОНЕЦ
    И ЗаказПоставщикуТовары.Ссылка.Склад В(&Склад)
    И ЗаказПоставщикуТовары.ДатаПоступления <= &ДатаДо
    И ЗаказПоставщикуТовары.ДатаПоступления >= &ДатаC
СГРУППИРОВАТЬ ПО
    ЗаказПоставщикуТовары.ДатаПоступления,
    ЗаказПоставщикуТовары.Ссылка,
    // Не забываем добавить поле в группировку
    ВЫРАЗИТЬ(ЗаказПоставщикуТовары.Ссылка.ДокументОснование КАК Документ.ЗаказКлиента)
;

Благодаря использованию функции ВЫРАЗИТЬ(... КАК Документ.ЗаказКлиента) платформа выполнит соединение только с одной таблицей ЗаказКлиента, полностью игнорируя остальные типы документов составного реквизита. Поле ЗаказКлиента в нашей временной таблице ВТ_Даты теперь имеет строго определенный, одиночный ссылочный тип. Для автоматического выявления таких мест в конфигурации подойдёт обработка для проверки запросов на разыменование полей составного типа.

Шаг 2: Индексируем временную таблицу

Поскольку временная таблица ВТ_Даты далее будет активно использоваться в левых соединениях с реальными таблицами документов (где миллионы записей), СУБД жизненно необходим индекс по связующим полям. Проиндексируем временную таблицу по полям Ссылка и ЗаказКлиента. Для этого добавим инструкцию индексации в первый пакет:


ИНДЕКСИРОВАТЬ ПО
    Ссылка,
    ЗаказКлиента
;

Шаг 3: Упрощаем условие левого соединения

Теперь, когда во временной таблице ВТ_Даты у нас есть готовое, проиндексированное и строго типизированное поле ЗаказКлиента, мы можем кардинально упростить условие левого соединения во втором запросе пакета. Нам больше не нужны сложные конструкции проверки типа ССЫЛКА и сравнения с пустой ссылкой.

Проанализируем новое условие соединения:


ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ПО ВТ_Даты.ЗаказКлиента = РеализацияТоваровУслуг.ЗаказКлиента
    И (РеализацияТоваровУслуг.Проведен)

Такое условие является максимально «чистым» для оптимизатора СУБД. Оно полностью удовлетворяет правилам SARGable и позволяет СУБД выполнить быстрое сопоставление данных с использованием индексов обеих таблиц.

Шаг 4: Избавляемся от разыменования номера заказа клиента

Вместо проблемной конструкции получения номера через две точки от составного типа (ЗаказПоставщику.ДокументОснование.Номер), мы также воспользуемся типизированным полем из временной таблицы. Для этого нам потребуется связать временную таблицу с таблицей Документ.ЗаказКлиента в явном виде:


ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента КАК ЗаказКлиента
ПО ВТ_Даты.ЗаказКлиента = ЗаказКлиента.Ссылка

И в секции ВЫБРАТЬ мы безболезненно и быстро получим номер заказа напрямую через явное соединение:

ЗаказКлиента.Номер КАК НомерЗаказа,

Итоговый оптимизированный вариант запроса

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


ВЫБРАТЬ
    ЗаказПоставщикуТовары.Ссылка КАК Ссылка,
    ЗаказПоставщикуТовары.ДатаПоступления КАК ДатаДоставки,
    ВЫРАЗИТЬ(ЗаказПоставщикуТовары.Ссылка.ДокументОснование КАК Документ.ЗаказКлиента) КАК ЗаказКлиента
ПОМЕСТИТЬ ВТ_Даты
ИЗ
    Документ.ЗаказПоставщику.Товары КАК ЗаказПоставщикуТовары
ГДЕ
    ЗаказПоставщикуТовары.Ссылка.Проведен
    И ЗаказПоставщикуТовары.Ссылка.СамоВывоз
    И ВЫБОР
            КОГДА &ТолькоМаркетПлейс
                ТОГДА ЗаказПоставщикуТовары.Ссылка.МаркетПлейс
            ИНАЧЕ ИСТИНА
        КОНЕЦ
    И ЗаказПоставщикуТовары.Ссылка.Склад В(&Склад)
    И ЗаказПоставщикуТовары.ДатаПоступления <= &ДатаДо
    И ЗаказПоставщикуТовары.ДатаПоступления >= &ДатаC
СГРУППИРОВАТЬ ПО
    ЗаказПоставщикуТовары.ДатаПоступления,
    ЗаказПоставщикуТовары.Ссылка,
    ВЫРАЗИТЬ(ЗаказПоставщикуТовары.Ссылка.ДокументОснование КАК Документ.ЗаказКлиента)

ИНДЕКСИРОВАТЬ ПО
    Ссылка,
    ЗаказКлиента
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Даты.Ссылка КАК Распоряжение,
    ВТ_Даты.ДатаДоставки КАК Дата,
    ЗаказПоставщику.ЖелаемоеВремяДоставки КАК Время,
    ЗаказПоставщику.АдресСкладаПоставщика КАК АдресДоставки,
    ЗаказПоставщику.Склад КАК Склад,
    ЗаказПоставщику.Склад КАК Получатель,
    ЗаказПоставщику.Партнер КАК Партнер,
    ЗаказКлиента.Номер КАК НомерЗаказа,
    ЕСТЬNULL(РеализацияТоваровУслуг.Ссылка, ЗНАЧЕНИЕ(Документ.РеализацияТоваровУслуг.ПустаяСсылка)) КАК Реализация
ПОМЕСТИТЬ ВТ_Распоряжения
ИЗ
    ВТ_Даты КАК ВТ_Даты
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказПоставщику КАК ЗаказПоставщику
        ПО ВТ_Даты.Ссылка = ЗаказПоставщику.Ссылка
        
        // Явное соединение по типизированному и проиндексированному полю ВТ
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
        ПО ВТ_Даты.ЗаказКлиента = РеализацияТоваровУслуг.ЗаказКлиента
            И (РеализацияТоваровУслуг.Проведен)
            
        // Явное соединение для безопасного получения номера заказа клиента
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказКлиента КАК ЗаказКлиента
        ПО ВТ_Даты.ЗаказКлиента = ЗаказКлиента.Ссылка
;

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

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

Резюмируем ключевые выводы. Чтобы ваши запросы всегда выполнялись быстро, придерживайтесь трех золотых правил:

Никогда не обращайтесь через точку к реквизитам составных полей. Заменяйте это явным использованием функции ВЫРАЗИТЬ и последующим левым соединением с конкретной таблицей.
Переносите всю тяжелую фильтрацию, разыменования и приведения типов в промежуточные временные таблицы.
Обязательно индексируйте временные таблицы по полям связей, прежде чем соединять их с другими таблицами в последующих пакетах запроса.