Как оптимально использовать ВЫРАЗИТЬ и соединения в запросах 1С для составных типов данных?

← На главную

Работа с полями составного типа данных в запросах платформы 1С:Предприятие — одна из самых частых причин падения производительности и зависания СУБД на крупных внедрениях. Неявные соединения, некорректная фильтрация и избыточные таблицы в планах запросов могут превратить простой отчет в настоящую проблему для всей системы. В этой статье мы подробно разберем, как правильно использовать оператор ВЫРАЗИТЬ, как СУБД обрабатывает обращения к составным полям через точку, и какое решение является эталонным с точки зрения стандартов разработки фирмы «1С».

В чем сложность работы с составными типами данных?

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

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

Если в составной тип входит 10 видов документов, ваш SQL-запрос мгновенно обрастет десятью тяжелыми операторами LEFT JOIN. Это приводит к следующим проблемам:

Перегрузка оптимизатора СУБД. Оптимизатору (MS SQL, PostgreSQL и др.) становится невероятно сложно построить оптимальный план выполнения запроса.
Избыточное чтение страниц данных. СУБД тратит ресурсы на сканирование индексов и таблиц, которые физически не содержат нужных нам данных.
Проблемы с RLS (ограничение доступа на уровне записей). Если у пользователя настроены права RLS, система добавит условия проверки прав для каждой из этих десяти таблиц, что может полностью заблокировать работу базы из-за переполнения стека парсера SQL.

Вариант 1. Использование ВЫРАЗИТЬ в конструкции ГДЕ

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


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

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

На уровне СУБД вместо веера левых соединений будет сгенерировано ровно одно левое соединение с таблицей возвратов. Это огромный шаг вперед по сравнению с обычным обращением через точку. Однако у этого подхода есть недостатки:

Левое соединение все равно создается. Если в таблице реализаций миллионы записей, а возвратов из них — единицы, СУБД сначала выполнит левое соединение для всех записей реализаций, а затем отфильтрует их по менеджеру.
В поле Менеджер придут пустые значения для тех строк, где тип ссылки не совпал с возвратом, что требует дополнительного анализа на стороне разработчика.

Вариант 2. Конструкция ССЫЛКА + обращение через точку

В ходе обсуждения часто предлагается альтернативный вариант, представленный ниже:


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

Давайте выясним причину, почему этот вариант не является оптимальным, несмотря на кажущуюся логичность. Оператор ССЫЛКА работает на уровне СУБД очень быстро и эффективно, так как он анализирует только внутренний идентификатор типа ссылки (поле _TRef в структурах баз 1С), не обращаясь к физическим таблицам документов.

Однако добавление условия Реализация.ДопоставкаПоРеализации.Менеджер полностью нивелирует эту оптимизацию. Платформа 1С не анализирует логическую цепочку условий на предмет того, что «раз тип уже проверен, то можно соединяться только с одной таблицей». Транслятор запросов все равно сгенерирует неявные левые соединения со всеми таблицами составного типа, что приведет к тем же самым проблемам с производительностью.

Вариант 3. Использование подзапросов в условии ГДЕ

Некоторые специалисты предлагают использовать подзапросы в секции ГДЕ с использованием оператора В:


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

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

Вариант 4. Лучшая практика по стандартам фирмы «1С» (Использование ВТ и индексации)

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

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

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

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

Посмотрим на пример реализации этого эталонного подхода:


ВЫБРАТЬ
    ВозвратТоваров.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ОтфильтрованныеВозвраты
ИЗ
    Документ.ВозвратТоваровОтКлиента КАК ВозвратТоваров
ГДЕ
    ВозвратТоваров.Менеджер = &Менеджер

ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

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

Почему это решение работает быстрее всего?

Проанализируем технические преимущества данного подхода:

Минимальный объем данных. Временная таблица ВТ_ОтфильтрованныеВозвраты содержит крайне малый объем данных (только ссылки документов возвратов конкретного менеджера), что позволяет СУБД держать её целиком в оперативной памяти.
Использование индекса СУБД. Благодаря индексации поля Ссылка во временной таблице, операция внутреннего соединения (ВНУТРЕННЕЕ СОЕДИНЕНИЕ) выполняется мгновенно с использованием эффективных алгоритмов СУБД (Index Seek, Merge Join или Hash Match).
Полное отсутствие избыточных соединений. Запрос не содержит обращений «через точку» к составным полям. Соответственно, СУБД не строит ни одного лишнего левого соединения.
Оптимальность для RLS. Ограничения доступа будут накладываться точечно: на этапе выборки возвратов — правила для возвратов, на этапе выборки реализаций — правила для реализаций. Исключается перегрузка оптимизатора из-за объединения множества цепочек условий RLS.

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