В практике разработки на платформе 1С:Предприятие 8.3 часто возникает ситуация, когда необходимо выбрать данные из базы, ограничивая их списком идентификаторов (ссылок), хранящихся в массиве. Типовым подходом является использование оператора В в тексте запроса. Однако, когда количество элементов в массиве превышает несколько сотен или тысяч, производительность системы резко падает, а в некоторых случаях это приводит к падению рабочего процесса или ошибкам SQL Server. В рамках данной статьи мы разберем, почему это происходит, и проанализируем наиболее эффективные методы решения этой задачи.
Для начала проанализируем ситуацию с точки зрения взаимодействия 1С и СУБД. Когда мы передаем массив в параметр запроса с условием ГДЕ Ссылка В (&Массив), платформа 1С при формировании SQL-запроса преобразует этот массив в список литералов. Если в массиве 10 000 элементов, SQL-запрос будет содержать перечисление всех 10 000 значений. Это порождает несколько проблем:
Рассмотрим подробнее, как избежать этих проблем, перейдя от простого использования массива к более профессиональным методам работы с данными.
Наиболее правильным и рекомендуемым способом является использование временных таблиц. Вместо того чтобы заставлять СУБД обрабатывать гигантский список в условии ГДЕ, мы сначала загрузим наши данные в специальную временную таблицу, проиндексируем её и выполним соединение. Рассмотрим этот процесс по шагам.
Сначала нам необходимо подготовить таблицу значений, которую мы будем передавать в запрос. Важный момент: типизация колонки таблицы значений должна строго соответствовать типу данных, которые мы будем загружать (например, СправочникСсылка.Номенклатура).
// Подготовим таблицу значений с нужным типом
ТЗ_Параметры = Новый ТаблицаЗначений;
ТЗ_Параметры.Колонки.Добавить("Ссылка", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
// Предположим, МассивСсылок уже заполнен данными
Для Каждого ТекущаяСсылка Из МассивСсылок Цикл
НоваяСтрока = ТЗ_Параметры.Добавить();
НоваяСтрока.Ссылка = ТекущаяСсылка;
КонецЦикла;
Далее мы инициализируем менеджер временных таблиц и передаем таблицу значений в запрос. Проанализируем пример кода для загрузки данных во временную таблицу:
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Первый шаг: Загрузка массива во временную таблицу и её индексация
Запрос.Текст =
"ВЫБРАТЬ
| ТЗ.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_КритерииОтбора
|ИЗ
| &ТЗ_Параметры КАК ТЗ
|;
|////////////////////////////////////////////////////////////////////////////////
|ИНДЕКСИРОВАТЬ ПО
| ВТ_КритерииОтбора.Ссылка";
Запрос.УстановитьПараметр("ТЗ_Параметры", ТЗ_Параметры);
Запрос.Выполнить();
Обратите внимание: использование оператора ИНДЕКСИРОВАТЬ ПО критически важно для производительности. Без индекса соединение большой временной таблицы с основной таблицей БД может выполняться медленнее, чем исходный вариант с оператором В.
Теперь, когда данные находятся во временной таблице ВТ_КритерииОтбора, мы можем выполнить основной запрос. Вместо фильтрации через ГДЕ ... В (...), мы используем ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Это позволит оптимизатору СУБД использовать наиболее эффективный алгоритм сопоставления данных.
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Код КАК Код,
| Номенклатура.Наименование КАК Наименование,
| Номенклатура.Артикул КАК Артикул
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_КритерииОтбора КАК ВТ_КритерииОтбора
| ПО Номенклатура.Ссылка = ВТ_КритерииОтбора.Ссылка";
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
Разберем, почему это работает быстрее. При соединении СУБД оперирует двумя наборами данных, которые (в идеале) оба проиндексированы. В случае с MS SQL Server, скорее всего, будет применен алгоритм Merge Join или Hash Match, что на порядки быстрее перебора списка констант.
Существует распространенное мнение, что оператор В плохо работает только после 1000 элементов. На самом деле, деградация производительности начинается гораздо раньше. Однако именно на отметке около 1000 элементов SQL Server начинает генерировать крайне неэффективные планы запросов. Если ваш массив содержит гарантированно менее 50 элементов, можно оставить условие В. Если же количество элементов динамическое и может расти — всегда используйте временные таблицы.
Для удобства разработки мы рекомендуем объединять создание временной таблицы и основной выборки в один пакетный запрос. Это сокращает количество обращений к серверу БД и делает код более читаемым. Посмотрим на итоговый пример реализации:
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.УстановитьПараметр("ТЗ_Параметры", ТЗ_Параметры);
Запрос.Текст =
"ВЫБРАТЬ
| ТЗ.Ссылка КАК Ссылка
|ПОМЕСТИТЬ ВТ_Данные
|ИЗ
| &ТЗ_Параметры КАК ТЗ
|ИНДЕКСИРОВАТЬ ПО
| Ссылка
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Товары.Наименование,
| Товары.Цена
|ИЗ
| Справочник.Номенклатура КАК Товары
|ГДЕ
| Товары.Ссылка В (ВЫБРАТЬ ВТ.Ссылка ИЗ ВТ_Данные КАК ВТ)";
// Обратите внимание: В пакете можно использовать условие В(ВЫБРАТЬ...),
// СУБД преобразует это в эффективное полусоединение (Semi-Join)
При работе с очень большими объемами (десятки и сотни тысяч ссылок) стоит учитывать следующие моменты:
УНИЧТОЖИТЬ или пересоздавать менеджер.В заключение отметим, что переход от прямого использования массивов в запросах к работе через временные таблицы — это признак профессионального подхода к разработке на 1С. Это гарантирует стабильную работу системы независимо от объема обрабатываемых пользователем данных и защищает от трудноотлаживаемых ошибок производительности в будущем.