Временные таблицы и пакетные запросы

Учимся дробить неподъёмный запрос на понятные этапы: собрали промежуточный результат во временную таблицу — и опираемся на него дальше.

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

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

Зачем это нужно на практике

Представьте типовую задачу: отчёт «продажи по менеджерам за месяц, но только по тем клиентам, у которых есть задолженность». Тут два независимых расчёта — продажи и долги — которые потом надо свести. Если запихнуть всё в один запрос, вы получите монстра с повторяющимися подзапросами. А если посчитать долги во временную таблицу, а продажи соединить уже с ней, каждый шаг становится простым и проверяемым по отдельности. Это как разложить длинную функцию на несколько маленьких: каждую видно целиком.

Менеджер временных таблиц

Чтобы платформа знала, где хранить временные таблицы и как связать несколько запросов между собой, ей нужен МенеджерВременныхТаблиц — специальный объект, который живёт на время выполнения пакета. Создаём его и передаём в объект запроса:

МенеджерВТ = Новый МенеджерВременныхТаблиц;

Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
Запрос.УстановитьПараметр("НачалоМесяца", НачалоМесяца(ТекущаяДата()));
Запрос.УстановитьПараметр("КонецМесяца",  КонецМесяца(ТекущаяДата()));

Пока в тексте запроса не встретится инструкция ПОМЕСТИТЬ, менеджер стоит без дела. Как только встретится — платформа материализует результат и запомнит его под именем таблицы.

Оператор ПОМЕСТИТЬ

Ключевое слово ПОМЕСТИТЬ ИмяТаблицы ставится между списком полей и ИЗ. Оно говорит: «результат этого ВЫБРАТЬ не возвращай мне сразу, а сложи во временную таблицу с таким именем». Соберём во временную таблицу список должников:

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

Обратите внимание: после ПОМЕСТИТЬ ВТДолжники запрос ничего не «отдаёт» в код — он просто наполняет временную таблицу. Забирать данные мы будем следующим запросом пакета.

Пакетный запрос

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

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

////////////////////////////////////////////////////////////

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

Первый запрос отобрал должников и сложил их во ВТДолжники. Второй соединился с этой таблицей по внутреннему соединению — значит в результат попадут только продажи должникам. Двойной слэш // — это комментарий в языке 1С; разделительная линия из слэшей просто помогает глазами отделить один запрос пакета от другого.

Как забрать результат пакета

Метод Выполнить() запускает весь пакет. Обычный .Выбрать() вернёт выборку по последнему запросу пакета — как правило, это и есть то, что нам нужно:

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

Пока Выборка.Следующий() Цикл
    Сообщить(Выборка.Менеджер.Наименование + ": " + Выборка.Выручка);
КонецЦикла;

Результат:

Иванов И.И.: 152000
Петрова А.С.: 98500
Сидоров П.П.: 41000

Если же нужны промежуточные таблицы (например, для отладки хочется посмотреть и должников, и продажи), вызывайте ВыполнитьПакет() — он вернёт массив результатов, по одному на каждый запрос пакета.

Как это работает

Под капотом временная таблица 1С превращается в настоящую временную таблицу СУБД (в SQL это конструкция вроде #ВТДолжники в SQL Server). Платформа материализует её: физически считает строки и кладёт на диск сервера СУБД. У этого две важные стороны.

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

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

Жизненный цикл менеджера тоже стоит понимать: временные таблицы существуют, пока жив МенеджерВременныхТаблиц. Как только переменная с менеджером выходит из области видимости (процедура завершилась), платформа удаляет временные таблицы с сервера. Явно почистить одну таблицу досрочно можно инструкцией УНИЧТОЖИТЬ ИмяТаблицы отдельным запросом пакета.

Частые ошибки

• Забыли назначить менеджер. Написали ПОМЕСТИТЬ, но не присвоили Запрос.МенеджерВременныхТаблиц. Платформа выдаст ошибку, что временные таблицы недоступны. Менеджер обязателен, если в пакете есть хоть один ПОМЕСТИТЬ, результат которого нужен другому запросу.
• Обращение к таблице до её создания. Запросы в пакете выполняются строго по порядку. Нельзя во втором запросе сослаться на временную таблицу, которую создаёт третий, — её ещё нет.
• Временная таблица ради одной строки. Новички, узнав про приём, начинают заворачивать во временные таблицы даже мелкие справочные выборки. Материализация тут только замедляет. Ориентир: временная таблица оправдана, когда её результат крупный, переиспользуется несколько раз или резко сужает следующее соединение.
• Дубли из-за пропущенного условия уникальности. Если временная таблица получилась с повторами по ключу соединения, последующее соединение размножит строки. Следите, чтобы в поле, по которому соединяете, значения были уникальны (при необходимости добавьте СГРУППИРОВАТЬ ПО при наполнении).

Итоги

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