При разработке загрузок данных из внешних источников (например, прайс-листов или каталогов поставщиков) часто возникает задача: передать в запрос ТаблицуЗначений с колонками Артикул и Наименование, сопоставить её с данными информационной базы и получить только те строки, которых в базе еще нет. Однако при больших объемах справочника (от сотен тысяч элементов) прямое соединение таблицы-параметра может приводить к зависанию сеанса и ошибке нехватки оперативной памяти.
Разберем причины возникновения этой проблемы и рассмотрим по шагам правильные и производительные способы её решения.
Проанализируем ситуацию, почему простой запрос с соединением таблицы-параметра может зависать на больших объемах данных:
КвалификаторыСтроки с фиксированной длиной, платформа транслирует такие поля в СУБД как строки неограниченной длины. Сравнение и хэширование таких строк в SQL требует огромного объема оперативной памяти и дискового пространства базы TempDB.Номенклатура.Перед передачей данных в запрос обязательно зададим жесткие типы для колонок таблицы значений, совпадающие с типами реквизитов в конфигурации, и свернем дубликаты. Рассмотрим пример создания и подготовки таблицы:
// Задаем точные квалификаторы строк в соответствии с метаданными справочника
КвалификаторАртикула = Новый КвалификаторыСтроки(25);
КвалификаторНаименования = Новый КвалификаторыСтроки(150);
МассивТипаАртикул = Новый Массив;
МассивТипаАртикул.Добавить(Тип("Строка"));
ОписаниеТипаАртикул = Новый ОписаниеТипов(МассивТипаАртикул, , КвалификаторАртикула);
МассивТипаНаименование = Новый Массив;
МассивТипаНаименование.Добавить(Тип("Строка"));
ОписаниеТипаНаименование = Новый ОписаниеТипов(МассивТипаНаименование, , КвалификаторНаименования);
// Создаем таблицу значений с явной типизацией колонок
ТЗ_Исходная = Новый ТаблицаЗначений;
ТЗ_Исходная.Колонки.Добавить("Артикул", ОписаниеТипаАртикул);
ТЗ_Исходная.Колонки.Добавить("Наименование", ОписаниеТипаНаименование);
// Заполняем таблицу данными...
// Удаляем возможные дубликаты перед выполнением запроса
ТЗ_Исходная.Свернуть("Артикул, Наименование");
Рассмотрим основной и самый надежный способ решения задачи через пакетный запрос. Мы помещаем переданную таблицу во временную таблицу, обязательно индексируем её по полям сопоставления, после чего выполняем ЛЕВОЕ СОЕДИНЕНИЕ с проверкой реквизита справочника на NULL.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТЗ.Артикул КАК Артикул,
| ТЗ.Наименование КАК Наименование
|ПОМЕСТИТЬ ВТ_ИсходныеДанные
|ИЗ
| &ТаблицаИсточник КАК ТЗ
|
|ИНДЕКСИРОВАТЬ ПО
| Артикул,
| Наименование
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ РАЗЛИЧНЫЕ
| ВТ.Артикул КАК Артикул,
| ВТ.Наименование КАК Наименование
|ИЗ
| ВТ_ИсходныеДанные КАК ВТ
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
| ПО ВТ.Артикул = СпрНоменклатура.Артикул
| И ВТ.Наименование = СпрНоменклатура.Наименование
| И (НЕ СпрНоменклатура.ЭтоГруппа)
|ГДЕ
| СпрНоменклатура.Ссылка ЕСТЬ NULL";
Запрос.УстановитьПараметр("ТаблицаИсточник", ТЗ_Исходная);
РезультатЗапроса = Запрос.Выполнить();
ТЗ_Ненайденные = РезультатЗапроса.Выгрузить();
Разберем ключевые особенности этого запроса:
ИНДЕКСИРОВАТЬ ПО Артикул, Наименование заставляет СУБД создать индекс для временной таблицы, что многократно ускоряет операцию соединения.СпрНоменклатура.Ссылка ЕСТЬ NULL отсекает все совпавшие записи, оставляя в результате только те строки исходной таблицы, для которых в справочнике не нашлось соответствия.НЕ СпрНоменклатура.ЭтоГруппа исключает группы иерархического справочника из процесса сопоставления.Если во входящей таблице значений содержатся десятки тысяч строк, даже оптимизированный запрос может потребовать значительных ресурсов. В таких ситуациях разберем по шагам порционную обработку:
ТаблицуЗначений на порции (например, по 5 000 – 10 000 строк).
// Создаем структуру для итогового результата
ТЗ_Итог = ТЗ_Исходная.СкопироватьКолонки();
РазмерПорции = 10000;
ВсегоСтрок = ТЗ_Исходная.Количество();
ТекущийИндекс = 0;
Пока ТекущийИндекс < ВсегоСтрок Цикл
ТЗ_Порция = ТЗ_Исходная.СкопироватьКолонки();
КонечныйИндекс = Мин(ТекущийИндекс + РазмерПорции - 1, ВсегоСтрок - 1);
Для НомерСтроки = ТекущийИндекс По КонечныйИндекс Цикл
ЗаполнитьЗначенияСвойств(ТЗ_Порция.Добавить(), ТЗ_Исходная[НомерСтроки]);
КонецЦикла;
Запрос.УстановитьПараметр("ТаблицаИсточник", ТЗ_Порция);
Выборка = Запрос.Выполнить().Выбрать();
Пока Выборка.Следующий() Цикл
НоваяСтрока = ТЗ_Итог.Добавить();
НоваяСтрока.Артикул = Выборка.Артикул;
НоваяСтрока.Наименование = Выборка.Наименование;
КонецЦикла;
ТекущийИндекс = ТекущийИндекс + РазмерПорции;
КонецЦикла;
Если справочник в базе содержит порядка 200–300 тысяч элементов и сопоставление выполняется на стороне сервера, можно использовать альтернативный подход в оперативной памяти:
Соответствие, где ключом выступает уникальная строковая комбинация или структура полей.Соответствие.
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Спр.Артикул КАК Артикул,
| Спр.Наименование КАК Наименование
|ИЗ
| Справочник.Номенклатура КАК Спр
|ГДЕ
| НЕ Спр.ЭтоГруппа";
Выборка = Запрос.Выполнить().Выбрать();
СуществующаяНоменклатура = Новый Соответствие;
Пока Выборка.Следующий() Цикл
// Формируем составной ключ (с разделителем, исключающим коллизии)
Ключ = СокрЛП(Выборка.Артикул) + "#|#" + СокрЛП(Выборка.Наименование);
СуществующаяНоменклатура.Вставить(Ключ, Истина);
КонецЦикла;
ТЗ_Ненайденные = ТЗ_Исходная.СкопироватьКолонки();
Для Каждого СтрТЗ Из ТЗ_Исходная Цикл
КлючПоиска = СокрЛП(СтрТЗ.Артикул) + "#|#" + СокрЛП(СтрТЗ.Наименование);
Если СуществующаяНоменклатура[КлючПоиска] = Неопределено Тогда
ЗаполнитьЗначенияСвойств(ТЗ_Ненайденные.Добавить(), СтрТЗ);
КонецЕсли;
КонецЦикла;
Такой способ исключает сложные вычисления на стороне СУБД и предотвращает блокировки в базе данных, эффективно решая проблему нехватки памяти при поиске отсутствующих позиций.