При разработке на платформе 1С:Предприятие часто возникает вопрос: зачем усложнять код, создавая таблицу значений (ТЗ) и передавая её в запрос, если можно использовать простые параметры или циклы? — для этого подойдёт набор инструментов разработчика с консолью запросов и СКД. Однако, использование ТЗ в запросах — это мощный паттерн, который решает задачи производительности и сложной логики, недоступные другими способами. Рассмотрим подробно, зачем это нужно, как это работает и разберем примеры кода.
Проанализируем ситуации, когда без передачи таблицы значений в запрос обойтись сложно или невозможно.
Представьте, что нам нужно отобрать записи не просто по списку товаров, а по уникальным парам, например, "Контрагент + Договор" или "ИНН + КПП". Обычный оператор В (&Список) здесь не сработает, так как он фильтрует поля независимо друг от друга. Передача ТЗ позволяет сделать внутреннее соединение (JOIN) по нескольким полям одновременно.
Часто мы получаем данные из Excel, XML, JSON или веб-сервисов (поможет ETL-инструмент для трансформации данных из внешних источников). Эти данные находятся в оперативной памяти (в ТЗ). Чтобы обогатить их данными из базы (например, найти ссылки на справочники по наименованиям или получить остатки товара), эффективнее всего "забросить" эту таблицу в запрос и соединить с реальными таблицами базы данных.
Классический пример — проведение документа. В момент проведения данные табличной части документа уже находятся в памяти, но еще могут быть не зафиксированы финально в регистрах (или нам нужно сравнить их с текущими остатками). Мы выгружаем табличную часть в ТЗ, передаем в запрос и соедининем с таблицей остатков регистра.
Выполнение одного запроса, соединяющего переданную ТЗ с данными базы, работает в сотни раз быстрее, чем перебор строк ТЗ в цикле и выполнение отдельного запроса Стр.Найти(...) для каждой строки ("Запрос в цикле").
Самый простой способ — использовать колонку таблицы значений как список для условия В. Это работает, если вам нужна фильтрация только по одному полю.
Рассмотрим пример, где нам нужно получить данные только по сотрудникам, находящимся в нашей таблице значений.
// Создаем и заполняем таблицу значений (например, из файла или обработки)
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Сотрудник", Новый ОписаниеТипов("СправочникСсылка.Сотрудники"));
// ... заполнение ТЗ ...
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Начисления.Сотрудник,
| Начисления.Сумма
|ИЗ
| РегистрНакопления.Начисления КАК Начисления
|ГДЕ
| Начисления.Сотрудник В (&СписокСотрудников)";
// Платформа 1С умеет самостоятельно извлекать колонку из ТЗ,
// если передать её в параметр для оператора "В"
Запрос.УстановитьПараметр("СписокСотрудников", ТЗ);
Результат = Запрос.Выполнить();
Обратите внимание: в этом случае мы не помещаем таблицу во временную таблицу SQL, платформа просто берет массив значений из первой колонки (или единственной подходящей) и подставляет их в условие WHERE Field IN (...).
Это наиболее профессиональный и гибкий подход. Он позволяет использовать ТЗ как полноценную таблицу: соединять её с другими таблицами, группировать данные и выбирать поля из самой ТЗ.
Важный нюанс: Типизация. SQL-серверу необходимо точно знать типы данных в колонках. Если в 1С таблица значений может содержать в одной колонке и Число, и Строку, и Ссылку (составной тип), то для SQL это недопустимо. Поэтому перед передачей в запрос обязательно нужно типизировать колонки ТЗ. Правильное использование ОписаниеТипов и квалификаторов гарантирует, что СУБД корректно обработает входящий набор данных.
Рассмотрим пример поиска ссылок на контрагентов по их ИНН, полученных из внешней системы:
// 1. Подготовка Таблицы Значений со строгой типизацией
ТЗ = Новый ТаблицаЗначений;
// Обязательно указываем КвалификаторыСтроки, иначе SQL может создать поле типа text/nvarchar(max),
// по которому нельзя делать соединения или оно будет работать медленно.
ТЗ.Колонки.Добавить("ИНН", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(12)));
ТЗ.Колонки.Добавить("Задолженность", Новый ОписаниеТипов("Число",,, Новый КвалификаторыЧисла(15, 2)));
// Заполняем данными (условно)
НовСтр = ТЗ.Добавить();
НовСтр.ИНН = "7701234567";
НовСтр.Задолженность = 1000;
// 2. Формирование запроса
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.Текст =
"ВЫБРАТЬ
| ВходящиеДанные.ИНН КАК ИНН,
| ВходящиеДанные.Задолженность КАК Задолженность
|ПОМЕСТИТЬ ВТ_ВходящиеДанные
|ИЗ
| &ТЗ КАК ВходящиеДанные
|
|ИНДЕКСИРОВАТЬ ПО
| ИНН
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_ВходящиеДанные.ИНН,
| ВТ_ВходящиеДанные.Задолженность,
| ЕСТЬNULL(СправочникКонтрагенты.Ссылка, ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка)) КАК КонтрагентСсылка
|ИЗ
| ВТ_ВходящиеДанные КАК ВТ_ВходящиеДанные
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК СправочникКонтрагенты
| ПО ВТ_ВходящиеДанные.ИНН = СправочникКонтрагенты.ИНН";
// Передаем ТЗ как параметр
Запрос.УстановитьПараметр("ТЗ", ТЗ);
Результат = Запрос.Выполнить();
ТаблицаСРезультатом = Результат.Выгрузить();
Разберем ключевые моменты этого кода:
ПОМЕСТИТЬ ВТ_ВходящиеДанные — эта команда создает временную таблицу в базе данных и загружает туда содержимое нашей ТЗ.&ТЗ КАК ВходящиеДанные — обращение к параметру.ИНДЕКСИРОВАТЬ ПО — критически важно для производительности. Если в вашей ТЗ тысячи строк, грамотная индексация в tempdb позволит избежать сканирования таблиц (Table Scan) и существенно ускорит соединение с реальными таблицами базы данных.Иногда мы не можем строго типизировать ТЗ на этапе создания (например, ТЗ пришла из другой процедуры). В этом случае можно попытаться привести типы прямо внутри запроса с помощью функции ВЫРАЗИТЬ. Однако, если типы совсем не совпадают, запрос может упасть с ошибкой.
Запрос.Текст =
"ВЫБРАТЬ
| ВЫРАЗИТЬ(ТЗ.Сотрудник КАК Справочник.Сотрудники) КАК Сотрудник,
| ВЫРАЗИТЬ(ТЗ.Сумма КАК ЧИСЛО(15, 2)) КАК Сумма
|ПОМЕСТИТЬ ВТ_Данные
|ИЗ
| &ТЗ КАК ТЗ";
Этот метод считается менее надежным, чем явное создание ОписаниеТипов при формировании колонок ТЗ, но он часто используется для быстрого написания кода. Удобнее через инструментарий поиска по метаданным и проверки синтаксиса.
Если у вас есть универсальный алгоритм, который должен принимать любую ТЗ и помещать её во временную таблицу, можно использовать готовые функции для динамического формирования текстов запросов на основе структуры переданной таблицы. Рассмотрим пример такого приема:
Функция ПолучитьТекстЗапросаДляТЗ(ИмяПараметраТЗ, ИмяВременнойТаблицы, ТаблицаДанных)
ТекстЗапроса = "ВЫБРАТЬ ";
Для Каждого Колонка Из ТаблицаДанных.Колонки Цикл
// Добавляем имя колонки
ТекстЗапроса = ТекстЗапроса + Символы.ПС +
" ТЗ." + Колонка.Имя + " КАК " + Колонка.Имя + ",";
КонецЦикла;
// Удаляем последнюю запятую
ТекстЗапроса = Лев(ТекстЗапроса, СтрДлина(ТекстЗапроса) - 1);
ТекстЗапроса = ТекстЗапроса + Символы.ПС +
"ПОМЕСТИТЬ " + ИмяВременнойТаблицы + Символы.ПС +
"ИЗ &" + ИмяПараметраТЗ + " КАК ТЗ";
Возврат ТекстЗапроса;
КонецФункции
Работая с передачей ТЗ в запрос, необходимо помнить о механике клиент-серверного взаимодействия и особенностях SQL:
МенеджерВременныхТаблиц. Это позволит загрузить ТЗ в tempdb SQL-сервера один раз. Более того, понимание того, как эффективно использовать менеджер временных таблиц, поможет правильно передавать данные между различными процедурами и функциями. Для этой задачи есть инструмент пошаговой отладки кода и вычисления выражений.Передача таблицы значений в запрос — это стандарт индустрии для 1С-разработчика. Это позволяет перенести логику обработки данных ("джойны", фильтры) на уровень СУБД, который оптимизирован для операций над множествами, освобождая сервер приложений от медленных цикличных алгоритмов.