Как оптимизировать запрос в 1С при сопоставлении большого файла с крупными справочниками

← На главную

При разработке обработок загрузки данных из внешних источников (например, файлов Excel на 10 000–20 000 строк) разработчики часто сталкиваются с падением производительности запросов. Особенно остро проблема проявляется, когда сопоставление выполняется с объемными справочниками: справочником Номенклатура на десятки тысяч элементов и подчиненными справочниками или регистрами на сотни тысяч записей. Разберем по шагам, как правильно построить и оптимизировать пакетный запрос для решения этой задачи.

1. Загрузка данных во временную таблицу и строгая типизация

Рассмотрим распространенную ошибку: передачу списка кодов напрямую в параметр запроса через условие ГДЕ Поле В (&МассивКодов). Если массив содержит 13 000 значений, запрос либо завершится с системной ошибкой СУБД из-за превышения лимита параметров (в MS SQL Server это 2 100 параметров), либо приведет к длительной компиляции плана запроса. Единственно верный подход для больших объемов — передача Таблицы Значений в запрос как параметра и помещение ее во временную таблицу.

Проанализируем критически важный аспект: типизацию колонок. Если таблица значений создана без явного указания типов колонок через ОписаниеТипов, СУБД создает во временной базе (tempdb) поля неограниченной длины. Это приводит к неявному приведению типов (Implicit Type Conversion) при соединении со справочниками, из-за чего СУБД отключает использование индексов и выполняет полное сканирование таблиц.

Посмотрим на пример правильной типизации и загрузки данных во временную таблицу:


// Формирование типизированной таблицы значений
ТаблицаЗагрузки = Новый ТаблицаЗначений;

МассивТипаСтрока = Новый Массив;
МассивТипаСтрока.Добавить(Тип("Строка"));
КвалификаторыСтроки = Новый КвалификаторыСтроки(25);
ОписаниеКода = Новый ОписаниеТипов(МассивТипаСтрока, , КвалификаторыСтроки);

ТаблицаЗагрузки.Колонки.Добавить("КодНоменклатуры", ОписаниеКода);
ТаблицаЗагрузки.Колонки.Добавить("КодРЛС", ОписаниеКода);

// Заполнение ТаблицаЗагрузки данными из файла...

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

2. Правила индексации временных таблиц

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

Согласно правилу левого префика:

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

Если требуется связывать разные справочники по независимым полям, разделим данные на специализированные временные таблицы с отдельными индексами либо поместим результат в отдельные проиндексированные таблицы:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВходящиеДанные.КодНоменклатуры КАК КодНоменклатуры
ПОМЕСТИТЬ ВТ_КодыНоменклатуры
ИЗ
    &ТаблицаЗагрузки КАК ВходящиеДанные
ИНДЕКСИРОВАТЬ ПО
    КодНоменклатуры
;
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВходящиеДанные.КодРЛС КАК КодРЛС
ПОМЕСТИТЬ ВТ_КодыСинонимов
ИЗ
    &ТаблицаЗагрузки КАК ВходящиеДанные
ИНДЕКСИРОВАТЬ ПО
    КодРЛС

3. Оптимизация соединений и условий отбора

Выясним разницу между фильтром через подзапрос ГДЕ Поле В (ВЫБРАТЬ ...) и явным соединением таблиц ВНУТРЕННЕЕ СОЕДИНЕНИЕ / ЛЕВОЕ СОЕДИНЕНИЕ.

При работе с временными таблицами явное соединение по проиндексированным полям является наиболее предсказуемым для оптимизатора СУБД. При этом важно соблюдать следующие правила:

Использование условий связи: Все условия сопоставления таблиц переносим в секцию ПО (условие связи), а не в секцию ГДЕ, чтобы СУБД могла оптимально отсечь данные на этапе соединения.
Замена типов соединений: Если требуется отобрать только те строки, которые гарантированно присутствуют в справочнике, используем ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Если нужно сохранить исходный состав строк внешнего файла (все 13 000 позиций), базовой таблицей выбираем временную таблицу и применяем к ней ЛЕВОЕ СОЕДИНЕНИЕ со справочниками.
Исключение декартовых произведений: При объединении данных по номенклатуре и ее синонимам необходимо контролировать связи между таблицами. Если соединить справочник товаров и справочник синонимов без условия связи между ними, сформируется декартово произведение строк, что приведет к катастрофическому замедлению и переполнению памяти.

4. Специфика подчиненных справочников

Проанализируем ситуацию со справочником синонимов на 700 000 записей. Если справочник подчинен владельцу, типовой кластерный индекс платформы формируется по составному ключу (Владелец, Код) или (Владелец, Ссылка).

Если выполнять поиск элемента только по реквизиту Код без указания поля Владелец, СУБД вынуждена просканировать все 700 000 строк. Чтобы соединение по коду подчиненного справочника работало быстро, в метаданных конфигуратора для реквизита поиска должно быть включено свойство «Индексировать» (или «Индексировать с дополнительным упорядочиванием»).

5. Пакетная обработка и альтернатива через «Соответствие»

Рассмотрим два дополнительных метода ускорения сопоставления при регулярной обработке данных:

Порционирование (чанкинг): Если файл содержит десятки тысяч строк, разбиваем исходную таблицу на пачки (например, по 2 000–3 000 строк) и передаем их в запрос в цикле. Это снижает нагрузку на базу tempdb и предотвращает блокировки таблиц.
Использование объекта Соответствие в оперативной памяти: Если справочник номенклатуры относительно статичен, на сервере 1С можно заранее выгрузить пары Код - Ссылка в коллекцию Соответствие. Поиск методом Соответствие.Получить(Код) в оперативной памяти сервера для 13 000 строк выполняется за доли секунды, полностью исключая лишние обращения к СУБД.