При написании запросов в 1С часто возникает задача отфильтровать данные по набору значений. Например, выбрать все документы по определенному списку контрагентов. Здесь перед разработчиком встает выбор: передать этот список как параметр в условие ГДЕ ... В (&СписокЗначений) или поместить его во временную таблицу и использовать конструкцию ГДЕ ... В (ВЫБРАТЬ ... ИЗ ВременнаяТаблица). На первый взгляд, первый вариант кажется проще и быстрее, но так ли это на самом деле? Давайте разберемся в этом вопросе подробно.
Рассмотрим типичную ситуацию: есть основной запрос, который нужно отфильтровать по списку из 100-300 элементов. Что выбрать для максимальной производительности?
Многие разработчики интуитивно предполагают, что передача готового списка значений в параметр запроса &спис должна работать быстрее. Ведь мы не создаем лишнюю таблицу, а просто передаем данные. Однако практические тесты показывают обратную картину. Как отметили на форуме, при проверке на большом объеме данных (например, 100 тысяч записей) вариант с временной таблицей (ВТ) оказался значительно быстрее. На малых объемах (до 1000 элементов) разница может быть незаметна, но с ростом количества записей преимущество ВТ становится очевидным.
Почему так происходит? Причина кроется в том, как платформа 1С взаимодействует с SQL-сервером и как СУБД строит планы выполнения запросов.
Чтобы понять, почему временная таблица часто выигрывает в производительности, проанализируем несколько ключевых аспектов работы СУБД.
1. План выполнения запроса и статистика
Когда мы передаем в запрос параметр &Список, для оптимизатора SQL-сервера это "черный ящик". Он не знает, сколько элементов содержится в этом списке — 10 или 10 000. Из-за этой неопределенности СУБД может построить неоптимальный план выполнения запроса. Если количество элементов в списке постоянно меняется, SQL-сервер будет вынужден перестраивать план при каждом выполнении, что создает дополнительную нагрузку.
Временная таблица — совсем другое дело. После создания и заполнения ВТ SQL-сервер собирает по ней статистику: количество записей, распределение данных и т.д. Имея эту информацию, оптимизатор может точно оценить объем данных и построить наиболее эффективный план для их обработки. Это делает производительность запроса более стабильной и предсказуемой.
2. Соединение (`INNER JOIN`) эффективнее фильтра (`IN`)
Хотя конструкция ГДЕ Поле В (ВЫБРАТЬ Поле ИЗ ВТ) работает, гораздо более производительным подходом является использование явного соединения:
ВЫБРАТЬ
ОсновнаяТаблица.Поле1,
ОсновнаяТаблица.Поле2
ИЗ
ОсновнаяТаблица КАК ОсновнаяТаблица
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СписокФильтра КАК ВТ_СписокФильтра
ПО ОсновнаяТаблица.ПолеДляФильтра = ВТ_СписокФильтра.Поле
Оператор INNER JOIN — одна из самых оптимизированных операций в SQL. Оптимизатор, зная статистику обеих таблиц, может выбрать наилучшую стратегию соединения (например, Hash Join, Merge Join или Nested Loops). В случае с IN его возможности по выбору стратегии часто более ограничены.
3. Минимизация обмена данными с сервером
Рассмотрим еще один важный аспект. Часто список значений для фильтрации сам по себе является результатом другого запроса. Если мы используем подход с &Список, наш алгоритм выглядит так:
Этот процесс создает как минимум один лишний обмен данными между сервером 1С и SQL-сервером ("запрос-ответ").
При использовании пакетного запроса с временной таблицей все операции происходят на стороне СУБД в рамках одного пакета:
Такой подход устраняет лишние издержки на передачу данных между серверами и зачастую работает значительно быстрее.
4. Индексация временной таблицы
Для еще большего ускорения запроса по временной таблице можно создать индекс. Если в ВТ много записей, а соединение идет по ключевому полю, добавление индекса может ускорить выполнение в разы.
ВЫБРАТЬ
ИсточникДанных.Номенклатура,
ИсточникДанных.Характеристика
ПОМЕСТИТЬ ВТ_НоменклатураДляОтбора
ИЗ
РегистрСведений.КакиеТоДанные КАК ИсточникДанных
ГДЕ
ИсточникДанных.Отбор = &КакойТоОтбор
;
////////////////////////////////////////////////////////////////////////////////
СОЗДАТЬ ИНДЕКС ИНД_ВТ_НоменклатураДляОтбора
ПО
ВТ_НоменклатураДляОтбора (Номенклатура, Характеристика)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Остатки.Номенклатура,
Остатки.КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&Дата, ) КАК Остатки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_НоменклатураДляОтбора КАК ВТ_НоменклатураДляОтбора
ПО Остатки.Номенклатура = ВТ_НоменклатураДляОтбора.Номенклатура
Несмотря на все преимущества временных таблиц, есть ситуации, когда использование параметра &Список оправдано и даже предпочтительно.
Подведем итог и сформулируем четкие правила, которые помогут сделать правильный выбор.
Используйте условие ГДЕ ... В (&Список), если:
Используйте Временную Таблицу и ВНУТРЕННЕЕ СОЕДИНЕНИЕ, если:
Таким образом, для задачи автора, где в списке от 100 до 300 записей, однозначно рекомендуется переписать запрос на использование временной таблицы с соединением. Это не только, скорее всего, ускорит выполнение, но и сделает производительность системы более предсказуемой при изменении объемов данных.