При импорте сложных структур данных из внешних файлов (например, таблиц Excel с десятками колонок) разработчики часто сталкиваются с необходимостью сопоставить текстовые наименования из файла с существующими элементами различных справочников в базе данных 1С. На первый взгляд задача кажется тривиальной: загрузить файл в таблицу значений, передать её в запрос и выполнить левое соединение (LEFT JOIN) со всеми нужными таблицами справочников.
Однако при попытке объединить в одном запросе 20–25 левых соединений, особенно по строковым полям неограниченной или большой длины (таким как Наименование), система может выдать критическую ошибку «Недостаточно памяти для получения результата запроса» или полностью аварийно завершить работу процесса (даже если в обрабатываемом файле всего несколько строк). В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем архитектурные ограничения СУБД и рассмотрим наиболее эффективные методы оптимизации этой операции в среде 1С:Предприятие.
Ошибка нехватки памяти в данном сценарии возникает вовсе не из-за объема возвращаемых данных. Основной виновник — этап компиляции и построения плана выполнения запроса оптимизатором СУБД (например, MS SQL Server, PostgreSQL или IBM DB2):
Hash Match или Merge Join), требующие колоссальных ресурсов кэша TempDB.Давайте рассмотрим, как исправить эту проблему с помощью правильных алгоритмических подходов.
Самый простой и быстрый способ локализовать проблему — отказаться от идеи «одного мега-запроса» и разбить задачу на последовательное сопоставление по каждой колонке в отдельности. Вместо 25 соединений в одном запросе мы сделаем 25 простых, изолированных запросов к каждому справочнику.
Рассмотрим пример кода, реализующий эту логику. Мы передаем в процедуру исходную таблицу значений, имя целевого справочника и имя реквизита, по которому производится сопоставление:
&НаСервере
Процедура ПодобратьЭлементПоНаименованию(ВрТЗ, ИмяСправочника, ИмяРеквизита)
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВРТЗ.Инд КАК Инд,
| ВРТЗ.[ИмяРеквизитаФайл] КАК ИмяРеквизитаФайл
|ПОМЕСТИТЬ ВТ_ДанныеФайла
|ИЗ
| &ВрТЗ КАК ВРТЗ
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТаблСправочник.Ссылка КАК Ссылка,
| ВТ_ДанныеФайла.Инд КАК Инд
|ИЗ
| ВТ_ДанныеФайла КАК ВТ_ДанныеФайла
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.[ИмяСправочника] КАК ТаблСправочник
| ПО ВТ_ДанныеФайла.ИмяРеквизитаФайл = ТаблСправочник.Наименование";
// Динамически подставляем имена полей и таблиц метаданных
Запрос.Текст = СтрЗаменить(Запрос.Текст, "[ИмяРеквизитаФайл]", ИмяРеквизита + "Файл");
Запрос.Текст = СтрЗаменить(Запрос.Текст, "[ИмяСправочника]", ИмяСправочника);
Запрос.УстановитьПараметр("ВрТЗ", ВрТЗ);
Выборка = Запрос.Выполнить().Выбрать();
Пока Выборка.Следующий() Цикл
// Заполняем найденную ссылку в исходную таблицу по индексу строки
СтрокаТЗ = ТЗ[Выборка.Инд];
СтрокаТЗ[ИмяРеквизита] = Выборка.Ссылка;
КонецЦикла;
КонецПроцедуры
Преимущества этого подхода:
Недостатки: Если строк в исходном файле очень много (десятки тысяч), постоянное создание временных таблиц и передача их обратно в базу данных на каждую колонку могут замедлить общую производительность операции.
Если вам необходима максимальная скорость работы и масштабируемость алгоритма на файлы размером более 10 000 строк, идеальным решением является использование механизма кэширования в оперативной памяти сервера приложений с помощью объектов Соответствие (Map).
Давайте разберем этот паттерн по шагам:
В (&МассивУникальных). Данный запрос выполняется мгновенно за счет автоматического использования индекса по полю Наименование.Соответствие), где ключом является текстовое наименование, а значением — ссылка на элемент справочника.Проанализируем пример реализации этого высокопроизводительного алгоритма:
&НаСервере
Процедура ЗагрузитьДанныеСГибриднымКэшем(ИсходнаяТЗ)
// Шаг 1: Инициализируем структуры для сбора уникальных значений
МассивНоменклатуры = Новый Массив;
МассивКонтрагентов = Новый Массив;
// ... и так далее для остальных 25 справочников
Для Каждого СтрокаФайла Из ИсходнаяТЗ Цикл
// Очищаем строки от лишних пробелов и собираем уникальные значения
ИмяНоменклатуры = СокрЛП(СтрокаФайла.НоменклатураНаименование);
Если ЗначениеЗаполнено(ИмяНоменклатуры) И МассивНоменклатуры.Найти(ИмяНоменклатуры) = Неопределено Тогда
МассивНоменклатуры.Добавить(ИмяНоменклатуры);
КонецЕсли;
ИмяКонтрагента = СокрЛП(СтрокаФайла.КонтрагентНаименование);
Если ЗначениеЗаполнено(ИмяКонтрагента) И МассивКонтрагентов.Найти(ИмяКонтрагента) = Неопределено Тогда
МассивКонтрагентов.Добавить(ИмяКонтрагента);
КонецЕсли;
КонецЦикла;
// Шаг 2: Выполняем точечные индексированные запросы
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Ссылка,
| Номенклатура.Наименование КАК Наименование
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Наименование В (&МассивНоменклатуры)
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Контрагенты.Ссылка КАК Ссылка,
| Контрагенты.Наименование КАК Наименование
|ИЗ
| Справочник.Контрагенты КАК Контрагенты
|ГДЕ
| Контрагенты.Наименование В (&МассивКонтрагентов)";
Запрос.УстановитьПараметр("МассивНоменклатуры", МассивНоменклатуры);
Запрос.УстановитьПараметр("МассивКонтрагентов", МассивКонтрагентов);
// Получаем результаты пакетного запроса
МассивРезультатов = Запрос.ВыполнитьПакет();
// Шаг 3: Заполняем кэш-таблицы (Соответствия)
КэшНоменклатуры = Новый Соответствие;
ВыборкаНоменклатуры = МассивРезультатов[0].Выбрать();
Пока ВыборкаНоменклатуры.Следующий() Цикл
// Приводим ключ к ВРЕГ для регистронезависимого поиска в кэше
КэшНоменклатуры.Вставить(ВРег(ВыборкаНоменклатуры.Наименование), ВыборкаНоменклатуры.Ссылка);
КонецЦикла;
КэшКонтрагентов = Новый Соответствие;
ВыборкаКонтрагентов = МассивРезультатов[1].Выбрать();
Пока ВыборкаКонтрагентов.Следующий() Цикл
КэшКонтрагентов.Вставить(ВРег(ВыборкаКонтрагентов.Наименование), ВыборкаКонтрагентов.Ссылка);
КонецЦикла;
// Шаг 4: Быстрое заполнение исходной ТЗ без обращения к СУБД
Для Каждого СтрокаФайла Из ИсходнаяТЗ Цикл
КлючНоменклатуры = ВРег(СокрЛП(СтрокаФайла.НоменклатураНаименование));
СтрокаФайла.НоменклатураСсылка = КэшНоменклатуры.Получить(КлючНоменклатуры);
КлючКонтрагента = ВРег(СокрЛП(СтрокаФайла.КонтрагентНаименование));
СтрокаФайла.КонтрагентСсылка = КэшКонтрагентов.Получить(КлючКонтрагента);
КонецЦикла;
КонецПроцедуры
Почему данный метод считается эталоном проектирования?
Наименование, что происходит практически мгновенно.Если по каким-то технологическим причинам вам необходимо выполнять сопоставление исключительно средствами языка запросов (например, когда таблица содержит миллионы записей и хранить её целиком в оперативной памяти сервера приложений нерационально), вы обязаны оптимизировать саму временную таблицу.
Главное правило при работе с временными таблицами в СУБД: все поля, по которым будет выполняться соединение (JOIN), должны быть проиндексированы.
Рассмотрим, как правильно сконструировать такой запрос:
ВЫБРАТЬ
ТЗ.ИндексСтроки КАК ИндексСтроки,
ТЗ.Наименование1 КАК Наименование1,
ТЗ.Наименование2 КАК Наименование2
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
&ТаблицаДанных КАК ТЗ
// Строим индексы по всем полям сопоставления!
ИНДЕКСИРОВАТЬ ПО
Наименование1,
Наименование2
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ИндексСтроки КАК ИндексСтроки,
Спр1.Ссылка КАК Ссылка1,
Спр2.Ссылка КАК Ссылка2
ИЗ
ВТ_ИсходныеДанные КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК Спр1
ПО ВТ.Наименование1 = Спр1.Наименование
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Спр2
ПО ВТ.Наименование2 = Спр2.Наименование
Наличие директивы ИНДЕКСИРОВАТЬ ПО заставит платформу 1С создать реальные индексы в СУБД для временной таблицы в TempDB. Это позволит оптимизатору запросов построить эффективный план (например, использовать быстрый Index Seek вместо полного сканирования таблицы Table Scan) и снизит потребление оперативной памяти до приемлемого уровня.
Чтобы процесс загрузки файлов Excel работал стабильно и не вызывал нареканий пользователей, рекомендуем внедрить следующие практики:
СокрЛП(). Также полезно очистить строки от непечатных символов и двойных пробелов, которые могут визуально не отличаться, но сделают точное сопоставление невозможным.Новый COMОбъект("Excel.Application") на стороне сервера. Это приводит к зависанию процессов Excel в операционной системе, утечкам памяти и полной неработоспособности системы на серверах под управлением Linux. Для разбора файлов xls/xlsx применяйте встроенный в платформу объект ТабличныйДокумент и его метод Прочитать().ПолнотекстовыйПоиск 1С или специализированные алгоритмы сравнения строк (например, расстояние Левенштейна).