Таблица значений как параметр запроса в 1С

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

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

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

Как передать таблицу значений в параметр запроса

Минимальный сценарий состоит из трёх шагов: создать и заполнить таблицу значений, установить её как параметр запроса, описать в тексте запроса выборку из параметра во временную таблицу. Платформа сама сериализует таблицу значений и передаёт её на сервер СУБД как набор строк.

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

ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТЗ.Колонки.Добавить("Количество", Новый ОписаниеТипов("Число", Новый КвалификаторыЧисла(15, 3)));

Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ТаблицаТоваров", ТЗ);
Запрос.Текст =
"ВЫБРАТЬ
|	ТаблицаТоваров.Номенклатура КАК Номенклатура,
|	ТаблицаТоваров.Количество КАК Количество
|ПОМЕСТИТЬ ВТ_Товары
|ИЗ
|	&ТаблицаТоваров КАК ТаблицаТоваров
|;
|ВЫБРАТЬ
|	ВТ_Товары.Номенклатура,
|	ВТ_Товары.Количество,
|	ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
|ИЗ
|	ВТ_Товары КАК ВТ_Товары
|		ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
|		ПО ВТ_Товары.Номенклатура = Остатки.Номенклатура";
Результат = Запрос.Выполнить().Выгрузить();

Обратите внимание: параметр &ТаблицаТоваров используется только в секции ИЗ первого пакета запроса. Напрямую соединять параметр-таблицу с таблицами базы нельзя — сначала она обязана попасть во временную таблицу через ПОМЕСТИТЬ.

Требования к колонкам и типам данных

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

Практические правила:

• всегда задавайте тип колонки явно через Новый ОписаниеТипов(...);
• для ссылочных колонок используйте конкретный тип ссылки, а не составной «любая ссылка»;
• для строк указывайте длину через КвалификаторыСтроки — строка неограниченной длины в условиях соединения работает плохо или не работает вовсе;
• для чисел задавайте разрядность и точность, совместимые с полем, с которым будет соединение.

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

Соединение временной таблицы с данными базы

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

Для фильтрации эффективнее использовать внутреннее соединение, а не условие В (ВЫБРАТЬ ...) — оптимизатор СУБД, как правило, лучше строит план запроса по соединению:

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

Если по одной и той же временной таблице выполняется несколько соединений, имеет смысл проиндексировать её поле соединения через предложение ИНДЕКСИРОВАТЬ ПО в конструкции ПОМЕСТИТЬ. Это заметно ускоряет запросы на таблицах от нескольких тысяч строк.

Передача табличной части документа

Частая задача — передать в запрос табличную часть объекта. У табличной части есть метод Выгрузить(), который возвращает таблицу значений с типизированными колонками. Это самый надёжный способ: типы колонок берутся из метаданных конфигурации и совместимы с полями базы.

Запрос.УстановитьПараметр("Товары", Объект.Товары.Выгрузить());

Если нужны не все колонки, передайте их список вторым параметром: Объект.Товары.Выгрузить(, "Номенклатура, Количество"). Меньше колонок — меньше объём передаваемых на сервер данных.

Альтернатива без выгрузки — обратиться к табличной части напрямую в запросе через Документ.РеализацияТоваровУслуг.Товары с отбором по ссылке. Этот вариант предпочтительнее, когда документ уже записан в базу: не тратится время на передачу данных, и запрос работает с актуальной версией табличной части.

Маршрут решения

Как выбрать способ передачи данных

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

Данные сформированы программно или пришли извне (файл, обмен, ввод пользователя)Передавайте таблицу значений параметром и помещайте во временную таблицу

Нужно отфильтровать по небольшому списку ссылок (до нескольких десятков)Достаточно массива в условии В (&Массив) — без таблицы значений

Список большой или нужны дополнительные колонки для соединенияИспользуйте таблицу значений с типизированными колонками и индексированием

Производительность и ограничения

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

Что влияет на скорость:

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

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

Проверка результата и отладка

При отладке запроса с таблицей-параметром удобно сначала проверить содержимое временной таблицы отдельным запросом: выполнить пакет до ПОМЕСТИТЬ и выгрузить ВЫБРАТЬ * ИЗ ВТ_Товары. Так видно, в том ли виде данные попали на сервер.

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

Контрольные точки

Как проверить результат

Содержимое временной таблицы

Норма

Число строк и значения колонок совпадают с исходной таблицей значений

Тревожный признак

Строк меньше, значения NULL или обрезаны — проблема с типами колонок

Соединение с таблицей базы

Норма

Каждая строка ВТ нашла пару, дублей нет

Тревожный признак

Строки размножились (дубли ключа в ВТ) или потерялись (тип ключа не совпал)

Время выполнения

Норма

Запрос отрабатывает за время, сопоставимое с аналогичными запросами без параметра-таблицы

Тревожный признак

Резкий рост времени при увеличении строк — нет индекса или лишние колонки

Частые вопросы

Можно ли передать таблицу значений в условие ГДЕ без временной таблицы?

Нет. Параметр с таблицей значений допустим только как источник данных в секции ИЗ с обязательным помещением во временную таблицу через ПОМЕСТИТЬ. Для фильтрации по списку одиночных значений используйте массив или список значений в условии В.

Что лучше — массив в условии В или таблица значений?

Для короткого списка однотипных значений (ссылки, коды) достаточно массива: ГДЕ Номенклатура В (&Массив). Таблица значений нужна, когда строк много, когда по каждой строке есть дополнительные данные для соединения или когда требуется точное соединение по нескольким полям одновременно.

Почему запрос с таблицей-параметром работает медленно?

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

Как передать таблицу значений в запрос на СКД?

В схеме компоновки данных таблицу передают как внешний источник данных: в наборе данных типа «Объект» указывают имя, а при формировании отчёта передают таблицу через коллекцию внешних наборов данных в ПроцессорКомпоновкиДанных.Инициализировать(). Параметром запроса внутри СКД таблица значений не передаётся.

Есть ли ограничение на размер передаваемой таблицы?

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

Нужно ли уничтожать временную таблицу после запроса?

Если запрос выполнен без явного менеджера временных таблиц, таблицы удаляются автоматически. Если вы создавали МенеджерВременныхТаблиц и используете его для нескольких запросов, уничтожайте ненужные таблицы командой УНИЧТОЖИТЬ, чтобы не держать память на СУБД и избежать конфликтов имён.