Как исключить выполнение подзапроса из ОБЪЕДИНИТЬ ВСЕ без динамической сборки текста

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

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

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

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

Решение 1. Использование параметров в секции ГДЕ (оптимизация СУБД)

Самый простой и элегантный способ отключить ветку объединения — использовать в условиях ГДЕ параметр булева типа. Рассмотрим этот метод подробнее.

Представим, что у нас есть три объединяемые таблицы. Мы хотим иметь возможность программно отключать выборку, например, из второй таблицы. Для этого мы пишем следующий текст запроса:


ВЫБРАТЬ
    Т1.Ссылка КАК Документ,
    Т1.СуммаДокумента КАК Сумма
ИЗ
    Документ.РеализацияТоваровУслуг КАК Т1
ГДЕ
    &ОтбиратьРеализации

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

ВЫБРАТЬ
    Т2.Ссылка,
    Т2.СуммаДокумента
ИЗ
    Документ.ПоступлениеТоваровУслуг КАК Т2
ГДЕ
    &ОтбиратьПоступления

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

ВЫБРАТЬ
    Т3.Ссылка,
    Т3.СуммаДокумента
ИЗ
    Документ.ЗаказКлиента КАК Т3
ГДЕ
    &ОтбиратьЗаказы

Выясним причину, почему это решение работает эффективно и быстро на стороне СУБД. Когда мы передаем в параметр &ОтбиратьПоступления значение Ложь, современный планировщик СУБД (будь то Microsoft SQL Server или PostgreSQL) не выполняет полный скан физической таблицы документов поступления.

Проанализируем работу оптимизатора СУБД в данном сценарии:

  1. При значении параметра Ложь (в SQL-запросе это транслируется как сравнение с ложным выражением) СУБД строит план выполнения с использованием специального механизма, который в MS SQL Server называется Startup Expression Predicate (предикат начального выражения).
  2. Перед тем как начать физическое сканирование индексов или таблиц, СУБД проверяет значение этого выражения на уровне логического фильтра. Если выражение ложно (то есть выполнение ветки не требуется), то СУБД вообще не запускает выполнение нижележащего поддерева операторов.
  3. Результат выполнения такой ветки возвращается мгновенно, количество логических и физических чтений диска для этой таблицы будет равно нулю.

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


Запрос = Новый Запрос;
Запрос.Текст = ТекстЗапроса; // Текст "одной простыней"
Запрос.УстановитьПараметр("&ОтбиратьРеализации", Истина);
Запрос.УстановитьПараметр("&ОтбиратьПоступления", Ложь); // Эта ветка не будет физически считываться СУБД
Запрос.УстановитьПараметр("&ОтбиратьЗаказы", Истина);

Результат = Запрос.Выполнить();

Важное замечание: Не нужно усложнять условие конструкциями ВЫБОР КОГДА &Параметр ТОГДА ИСТИНА ИНАЧЕ ЛОЖЬ КОНЕЦ. Достаточно просто передать булеву переменную напрямую в секцию ГДЕ, как показано в примере выше. СУБД отлично понимает такие условия.

Решение 2. Использование объекта "СхемаЗапроса" для удаления неиспользуемых веток

Если вы хотите добиться стопроцентной чистоты результирующего SQL-запроса, отправляемого на СУБД, и полностью исключить даже малейшее упоминание неиспользуемых таблиц из плана выполнения (например, во избежание проблем с Parameter Sniffing), то отличным решением станет использование платформенного объекта СхемаЗапроса.

Объект СхемаЗапроса позволяет программно разбирать текст запроса в объектную модель (дерево AST), вносить изменения в структуру и собирать его обратно в готовый текст запроса. При этом сам исходный текст в коде конфигурации остается неизменным и доступным для Конструктора запросов.

Рассмотрим по шагам, как это реализовать на встроенном языке 1С:


// 1. Инициализируем схему запроса и загружаем в нее наш исходный текст
Схема = Новый СхемаЗапроса;
Схема.УстановитьТекстЗапроса(ИсходныйТекстЗапроса);

// 2. Получаем первый пакет запроса (наш основной запрос)
Пакет = Схема.ПакетЗапросов[0];

// Предположим, по условиям алгоритма нам нужно исключить вторую ветку объединения (индекс 1)
НадоУдалитьВторуюВетку = Истина; 

Если НадоУдалитьВторуюВетку Тогда
    // Удаляем оператор объединения по индексу. 
    // Обратите внимание: первый оператор имеет индекс 0, второй - 1 и т.д.
    Пакет.Операторы.Удалить(1); 
КонецЕсли;

// 3. Выгружаем модифицированный и оптимизированный текст обратно
ОчищенныйТекстЗапроса = Схема.ПолучитьТекстЗапроса();

// 4. Передаем полученный текст в объект Запрос для выполнения
Запрос = Новый Запрос(ОчищенныйТекстЗапроса);
// Установка параметров и выполнение...

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

Решение 3. Использование легких Временных Таблиц (ВТ)

Существует еще один профессиональный архитектурный прием, который часто используется при проектировании высоконагруженных систем. Если в объединяемых ветках содержатся тяжелые запросы со сложными соединениями, то прямое наложение параметров ГДЕ &Флаг может иногда запутать оптимизатор СУБД при построении общего плана выполнения.

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

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


ВЫБРАТЬ
    Т1.Ссылка КАК Документ,
    Т1.СуммаДокумента КАК Сумма
ПОМЕСТИТЬ ВТ_Реализации
ИЗ
    Документ.РеализацияТоваровУслуг КАК Т1
ГДЕ
    &ОтбиратьРеализации
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Т2.Ссылка КАК Документ,
    Т2.СуммаДокумента КАК Сумма
ПОМЕСТИТЬ ВТ_Поступления
ИЗ
    Документ.ПоступлениеТоваровУслуг КАК Т2
ГДЕ
    &ОтбиратьПоступления
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Т3.Ссылка КАК Документ,
    Т3.СуммаДокумента КАК Сумма
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
    Документ.ЗаказКлиента КАК Т3
ГДЕ
    &ОтбиратьЗаказы

Шаг 2. В финальном запросе пакета мы просто объединяем эти временные таблицы. Это объединение будет происходить моментально, так как неиспользуемые таблицы при передаче значения Ложь будут созданы СУБД абсолютно пустыми без обращения к дисковой подсистеме.


ВЫБРАТЬ * ИЗ ВТ_Реализации КАК ВТ_Реализации

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

ВЫБРАТЬ * ИЗ ВТ_Поступления КАК ВТ_Поступления

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

ВЫБРАТЬ * ИЗ ВТ_Заказы КАК ВТ_Заказы

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

← На главную