В практике разработки на платформе 1С:Предприятие часто возникает задача, когда необходимо отфильтровать данные в одной таблице (часто очень большой) на основе значений, полученных из другой таблицы. Например, когда нам нужно выбрать записи из регистра сведений объемом в 20 миллионов строк, ограничив их периодом «от минимума до максимума», который вычисляется динамически по другому регистру. Проблема заключается в том, что язык запросов 1С не позволяет напрямую подставить результат одного запроса в параметры другого внутри одного пакета без использования временных таблиц или промежуточного кода.
В этой статье мы подробно разберем несколько эффективных способов решения этой задачи, проанализируем их влияние на производительность и выясним, как СУБД (SQL Server) обрабатывает такие конструкции.
Это самый «чистый» и производительный способ решения задачи в рамках одного объекта Запрос. Мы разделим логику на два этапа внутри пакета запросов. Сначала вычислим необходимые нам границы (минимум и максимум), поместим их во временную таблицу, а затем используем эту таблицу для фильтрации основного массива данных. В качестве наглядного образца можно использовать базовый пример отчета с фильтрацией данных через временные таблицы.
Рассмотрим этот алгоритм по шагам:
ВТ_ГраницыПериода. Важно понимать, что в этой таблице будет всего одна строка.Проанализируем пример кода такого запроса:
// Первый запрос пакета: вычисляем границы
ВЫБРАТЬ
МИНИМУМ(РегистрИсточик.Период) КАК МинПериод,
МАКСИМУМ(РегистрИсточик.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_ГраницыПериода
ИЗ
РегистрСведений.МалыйРегистр КАК РегистрИсточик;
////////////////////////////////////////////////////////////////////////////////
// Второй запрос пакета: используем границы для фильтрации
ВЫБРАТЬ
БольшойРегистр.Период КАК Период,
БольшойРегистр.Номенклатура КАК Номенклатура,
БольшойРегистр.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК БольшойРегистр,
ВТ_ГраницыПериода КАК ВТ_ГраницыПериода
ГДЕ
БольшойРегистр.Период МЕЖДУ ВТ_ГраницыПериода.МинПериод И ВТ_ГраницыПериода.МаксПериод;
Обратим внимание на важный нюанс: использование перечисления таблиц через запятую в секции ИЗ фактически создает CROSS JOIN (перекрестное соединение). Поскольку во временной таблице всего одна строка, это не приведет к дублированию записей, но позволит оптимизатору SQL эффективно применить условие МЕЖДУ (или BETWEEN в терминах SQL).
Иногда логика вычисления параметров настолько сложна, что её невозможно (или нецелесообразно) реализовывать внутри одного запроса. В этом случае мы можем вычислить данные первым запросом, выгрузить их в ТаблицаЗначений на стороне сервера 1С, а затем передать эту таблицу в качестве параметра во второй запрос.
Рассмотрим, как это реализовать правильно, чтобы не потерять в производительности:
// 1. Получаем данные первым запросом
Запрос1 = Новый Запрос("ВЫБРАТЬ МИНИМУМ(Период) КАК МинПериод, МАКСИМУМ(Период) КАК МаксПериод ИЗ РегистрСведений.МалыйРегистр");
ТаблицаПараметров = Запрос1.Выполнить().Выгрузить();
// 2. Подготавливаем второй запрос
Запрос2 = Новый Запрос;
Запрос2.Текст =
"ВЫБРАТЬ * ПОМЕСТИТЬ ВТ_Параметры ИЗ &ВнешняяТаблица КАК ВнешняяТаблица;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ ОсновнаяТаблица.*
|ИЗ РегистрСведений.ЦеныНоменклатуры КАК ОсновнаяТаблица
|ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Параметры КАК ВТ_Параметры
|ПО ОсновнаяТаблица.Период >= ВТ_Параметры.МинПериод
| И ОсновнаяТаблица.Период <= ВТ_Параметры.МаксПериод";
// Устанавливаем таблицу как параметр
Запрос2.УстановитьПараметр("ВнешняяТаблица", ТаблицаПараметров);
Результат = Запрос2.Выполнить();
Важные правила при использовании этого метода:
& (амперсанд).ТаблицаЗначений должны в точности совпадать с именами, используемыми в запросе.Многих разработчиков волнует вопрос: не будет ли соединение с временной таблицей работать медленнее, чем прямая подстановка дат через УстановитьПараметр()? Проанализируем ситуацию с точки зрения СУБД.
Если мы посмотрим на план выполнения (Query Plan), то увидим, что для SQL-сервера оба варианта практически идентичны. Когда мы используем ВТ_ГраницыПериода с одной записью, оптимизатор применяет операцию Nested Loops (вложенные циклы). Так как «внутренняя» таблица (наша времянка) содержит всего одну строку, SQL-сервер берет эти значения и выполняет Index Seek (поиск по индексу) в большой таблице 20 млн записей.
Статистика выполнения в обоих случаях (прямой параметр или временная таблица) будет показывать одинаковое количество RecordsScanned и ExecuteTime. Это происходит потому, что поле Период в регистрах сведений 1С всегда входит в состав кластерного индекса, и условие МЕЖДУ является SARGable (Search-Argumentable) — то есть оно позволяет СУБД не сканировать всю таблицу целиком, а сразу перейти к нужному диапазону страниц данных.
Выясним, почему выбор типа соединения критически важен при передаче параметров через временные таблицы. Рассмотрим ситуацию, когда первый запрос (вычисляющий границы) вернул пустой результат (например, в малом регистре нет данных).
Если мы используем Левое Соединение (LEFT JOIN):
Большая таблица (20 млн записей) будет выбрана полностью. Поскольку в правой таблице (границы) нет записей, условие фильтрации в соединении не отсечет строки, а просто вернет NULL в полях границ. Если после этого в ГДЕ стоит проверка, это может привести к непредсказуемым результатам или полной выгрузке данных в память сервера приложений.
Если мы используем Внутреннее Соединение (INNER JOIN) или CROSS JOIN с фильтром:
Если временная таблица границ пуста, итоговый запрос не вернет ни одной строки. Это логически более верно и безопасно для производительности системы.
Важно помнить, что передать границы периода через соединение в параметры виртуальных таблиц (таких как СрезПоследних или ОстаткиИОбороты) напрямую невозможно. Параметры виртуальных таблиц вычисляются СУБД в первую очередь, еще до выполнения соединений основного запроса.
Если вам нужно ограничить именно виртуальную таблицу динамическими границами, у вас есть два пути:
Запрос.УстановитьПараметр().ГДЕ итогового запроса, но помнить, что это может работать медленнее, чем фильтрация внутри параметров виртуальной таблицы, так как СУБД сначала получит данные из виртуальной таблицы, а уже потом начнет их отсеивать.Проанализируем ситуацию, когда расчет границ и выборка данных происходят в разных процедурах или даже разных модулях. В этом случае мы не можем использовать один пакет запросов. Решением станет объект МенеджерВременныхТаблиц.
МВТ = Новый МенеджерВременныхТаблиц;
Запрос1 = Новый Запрос;
Запрос1.МенеджерВременныхТаблиц = МВТ;
Запрос1.Текст = "ВЫБРАТЬ МИН(Период) КАК Мин, МАКС(Период) КАК Макс ПОМЕСТИТЬ ВТ_Границы ИЗ РегистрСведений.Источник";
Запрос1.Выполнить();
// ... где-то в другом месте кода ...
Запрос2 = Новый Запрос;
Запрос2.МенеджерВременныхТаблиц = МВТ; // Используем тот же менеджер
Запрос2.Текст = "ВЫБРАТЬ * ИЗ РегистрСведений.БольшойРегистр КАК Рег
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Границы КАК Гр ПО Рег.Период МЕЖДУ Гр.Мин И Гр.Макс";
Результат = Запрос2.Выполнить();
Такой подход позволяет эффективно передавать промежуточные результаты расчетов между разными частями программы, не гоняя данные через оперативную память сервера приложений в виде таблиц значений. Для отладки подобных механизмов между разными информационными базами можно использовать специальную консоль запросов с поддержкой общего менеджера временных таблиц.
Мы рассмотрели способы передачи результатов одного запроса в условия другого. Наиболее оптимальным с точки зрения архитектуры 1С и производительности SQL является использование пакета запросов с временной таблицей. Это позволяет сохранить типизацию, эффективно использовать индексы СУБД и сделать код читаемым. При работе с экстремально большими таблицами всегда отдавайте предпочтение Внутреннему соединению или условию ГДЕ с агрегатами из временной таблицы, чтобы избежать случайного полного сканирования (Full Table Scan) при отсутствии данных в фильтрующей таблице.