Что быстрее выполняется: вложенные запросы или временные таблицы в запросах 1С?

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

1. Официальные стандарты разработки фирмы «1С»

Для начала обратимся к официальным рекомендациям и требованиям к разработке. В системе стандартов фирмы «1С» существует специализированное и очень жесткое правило — Стандарт № std655 («Ограничения на соединения с вложенными запросами и виртуальными таблицами»). Согласно этому стандарту:

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

Рассмотрим пример типичного неоптимального запроса с вложенным подзапросом в соединении:

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

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

2. Почему СУБД ошибается при работе со вложенными запросами

Основная проблема вложенных запросов кроется в механизме работы оптимизатора СУБД (например, MS SQL Server или PostgreSQL). Чтобы построить эффективный план выполнения запроса, СУБД должна решить, каким именно алгоритмом соединять данные. Существует три базовых физических оператора соединения:

• Nested Loops (Вложенные циклы) — эффективен, когда одна из таблиц очень мала.
• Merge Join (Соединение слиянием) — отлично работает на предварительно отсортированных данных больших объемов.
• Hash Match (Хэш-соединение) — применяется при соединении больших неотсортированных наборов данных.

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

• Для физических таблиц СУБД использует регулярно обновляемую статистику распределения данных (гистограммы распределения), хранящуюся в базе.
• Для вложенного запроса (особенно если он содержит группировки, агрегатные функции, соединения или сложные условия ГДЕ) СУБД не имеет готовой статистики. Ей приходится оценивать количество возвращаемых строк «на лету» по математическим формулам вероятности.

В результате оптимизатор строит план практически вслепую. Если СУБД предположит, что вложенный запрос вернет 10 строк (и выберет быстрый Nested Loops), а на практике он вернет 1 000 000 строк, то запрос, который должен отрабатывать за доли секунды, «зависнет» на часы. Запросы со вложенными соединениями крайне нестабильны: они могут летать сразу после очистки кэша или обновления статистики СУБД, но резко деградировать при малейшем изменении объема данных в таблицах.

3. Где физически хранятся временные таблицы в 1С

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

• В СУБД Microsoft SQL Server они создаются в системной базе данных TempDB и получают уникальные имена вида #tt1....
• В СУБД PostgreSQL они регистрируются в специальной схеме временных объектов pg_temp.

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

4. Обратная сторона: накладные расходы на временные таблицы

Хотя временные таблицы защищают нас от ошибок оптимизатора СУБД, они не являются бесплатной «серебряной пулей». Использование временных таблиц несет в себе серьезные архитектурные накладные расходы:

1. Множественность запросов к СУБД: Когда мы пишем один запрос со вложенным подзапросом, платформа отправляет в СУБД ровно одну команду SELECT. Когда мы пишем пакетный запрос с временной таблицей, на уровне СУБД это транслируется в целую серию технологических операций:
   • Проверка существования таблицы в системном каталоге.
   • Создание структуры таблицы (CREATE TABLE #tt1...).
   • Запись данных во временную таблицу (INSERT INTO #tt1... SELECT...).
   • Основная выборка (SELECT ... FROM ... JOIN #tt1).
   • Очистка и удаление таблицы (DROP TABLE #tt1).
   Вместо одного обращения к СУБД мы получаем пять полноценных транзакционных команд.
2. Нагрузка на системную базу TempDB: При высокой интенсивности работы (например, когда сотни пользователей одновременно проводят документы или формируют оперативные отчеты) СУБД начинает испытывать колоссальную нагрузку на выделение системных страниц памяти в TempDB. Возникают специфические ожидания на системных защелках (например, задержки типа PAGELATCH_UP или PAGELATCH_SH на страницах распределения пространства PFS и SGAM). База данных начинает тормозить не из-за нехватки процессора или медленных дисков, а из-за внутренней конкуренции потоков за право создать временную таблицу в СУБД.

5. Индексирование временных таблиц — главное оружие разработчика

Почему же тогда временные таблицы в большинстве случаев предпочтительнее? Главное и неоспоримое преимущество временных таблиц перед вложенными запросами — это возможность их индексации.

В языке запросов 1С для этого используется конструкция ПОМЕСТИТЬ ... ИНДЕКСИРОВАТЬ ПО .... Давайте перепишем наш первый пример правильно, используя пакетный запрос с индексированием временной таблицы:

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

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

Что это дает на практике?

• Без явного указания индекса СУБД будет вынуждена при каждом соединении сканировать временную таблицу целиком (операция Table Scan). На больших выборках это приводит к катастрофическому падению скорости.
• Создание индекса по полям связи (в нашем примере это Номенклатура) позволяет СУБД использовать мгновенный поиск по индексу (операция Index Seek). Время поиска нужной строки сокращается логарифмически.
• Вложенный запрос, в отличие от временной таблицы, проиндексировать в принципе невозможно. СУБД вынуждена обрабатывать его «как есть».

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

Резюме: когда и что использовать

Давайте сведем все полученные знания в единую, понятную матрицу принятия решений при проектировании запросов в 1С:

Используйте вложенные запросы, если:

1. Объем промежуточных данных гарантированно мал (до 50–100 строк). В этом случае накладные расходы СУБД на создание и удаление физической таблицы в TempDB гарантированно превысят выигрыш от оптимизации плана запроса.
2. Запрос является простым, линейным и не участвует в операциях соединения (JOIN) с другими таблицами (например, простой вложенный отбор в конструкции ГДЕ Поле В (ВЫБРАТЬ...)).

Используйте пакетные запросы с временными таблицами, если:

• Требуется произвести соединение (JOIN) подзапроса с любой другой таблицей базы данных.
• Объем промежуточных данных велик (сотни, тысячи и более строк).
• Один и тот же промежуточный результат вычислений требуется использовать в пакете запросов несколько раз (чтобы избежать повторного выполнения тяжелых расчетов).
• Необходимо проиндексировать промежуточные данные по ключевым полям для ускорения последующих соединений.
• Запрос получается громоздким. Использование временных таблиц существенно повышает читаемость кода, упрощает его отладку и последующее сопровождение другими программистами.

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