При разработке механизмов импорта данных из внешних файлов, таких как Excel, программисты часто сталкиваются с проблемой производительности и точности сопоставления данных. Стандартный метод НайтиПоНаименованию(), вызванный внутри цикла, может значительно замедлить процесс и привести к ошибкам, если в справочнике присутствуют дубли или помеченные на удаление элементы. В этой статье мы подробно разберем, как уйти от поиска в цикле, оптимизировать работу с базой данных и правильно использовать механизмы запросов для получения ссылок.
Рассмотрим стандартную ситуацию: у нас есть файл Excel с сотнями или тысячами строк, содержащих названия товаров или статей затрат. Нам нужно заполнить колонку табличного поля, которая имеет тип СправочникСсылка. Самый очевидный, но не самый эффективный путь — перебирать строки Excel и для каждой строки вызывать метод Справочники.ИмяСправочника.НайтиПоНаименованию(ЗначениеИзExcel).
Почему это плохо? Каждый вызов этого метода инициирует отдельное обращение к серверу базы данных. Представим, что само выполнение поиска занимает доли секунды, но накладные расходы на установку соединения и передачу данных (latency) суммируются. Если строк 700, мы получим 700 обращений. Кроме того, этот метод возвращает первый попавшийся элемент, который может оказаться помеченным на удаление или быть группой, что в контексте загрузки данных часто является ошибкой.
Для оптимизации процесса мы можем собрать все уникальные наименования из файла Excel в один массив и передать его в запрос. Это позволит получить все необходимые ссылки за одно обращение к базе данных. Проанализируем, как это реализовать:
Рассмотрим пример кода для реализации этого подхода:
// 1. Формируем массив наименований из данных Excel
МассивНаименований = Новый Массив;
Для Каждого СтрокаДанных Из ДанныеExcel Цикл
МассивНаименований.Добавить(СокрЛП(СтрокаДанных.Наименование));
КонецЦикла;
// 2. Выполняем запрос для получения ссылок
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Наименование КАК Наименование,
| Номенклатура.Ссылка КАК Ссылка
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Наименование В (&МассивНаименований)
| И НЕ Номенклатура.ПометкаУдаления
| И НЕ Номенклатура.ЭтоГруппа";
Запрос.УстановитьПараметр("МассивНаименований", МассивНаименований);
ТаблицаСсылок = Запрос.Выполнить().Выгрузить();
// 3. Используем таблицу для сопоставления
Для Каждого СтрокаДанных Из ДанныеExcel Цикл
ПараметрыОтбора = Новый Структура("Наименование", СокрЛП(СтрокаДанных.Наименование));
НайденныеСтроки = ТаблицаСсылок.НайтиСтроки(ПараметрыОтбора);
Если НайденныеСтроки.Количество() > 0 Тогда
СтрокаТП.Номенклатура = НайденныеСтроки[0].Ссылка;
КонецЕсли;
КонецЦикла;
Если объем данных велик (несколько тысяч строк и более), использование массива в условии ГДЕ ... В может привести к деградации производительности СУБД. В таких случаях эффективнее передать всю таблицу данных из Excel во временную таблицу запроса. Это позволит выполнить внутреннее соединение на уровне сервера БД, что является максимально быстрым способом сопоставления.
Разберем алгоритм работы с ВТ:
ТаблицуЗначений с колонкой "Наименование" и загружаем в неё данные.Важный совет: При использовании временных таблиц всегда индексируйте поля, по которым происходит соединение. Это критически важно для скорости работы запроса.
В процессе разработки загрузки часто возникает необходимость ограничить поиск только определенной папкой (группой) справочника. Рассмотрим типичную ошибку, когда отбор В ИЕРАРХИИ не срабатывает. Выясним причину: часто программный поиск группы по коду или наименованию возвращает ПустаяСсылка, если объект не найден, и последующий запрос просто не находит данных.
Проанализируем правильный подход к выполнению пакета запросов с использованием иерархии:
ГруппаСтатей = Справочники.СтатьиДвиженияДенежныхСредств.НайтиПоКоду("001");
Если ГруппаСтатей.Пустая() Тогда
Сообщить("Группа с кодом 001 не найдена!");
Возврат;
КонецЕсли;
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("ГруппаСтатей", ГруппаСтатей);
Запрос.Текст =
"ВЫБРАТЬ
| Статьи.Ссылка КАК Ссылка,
| Статьи.Наименование КАК Наименование
|ИЗ
| Справочник.СтатьиДвиженияДенежныхСредств КАК Статьи
|ГДЕ
| Статьи.Ссылка В ИЕРАРХИИ(&ГруппаСтатей)
| И Статьи.ЭтоГруппа = ЛОЖЬ";
// Если используется пакет запросов, обрабатываем результат через ВыполнитьПакет()
МассивРезультатов = Запрос.ВыполнитьПакет();
Выборка = МассивРезультатов[0].Выбрать();
Пока Выборка.Следующий() Цикл
// Обработка данных
КонецЦикла;
Одной из самых частых причин, по которым поиск по наименованию не выдает результат при загрузке из Excel, является наличие лишних пробелов или спецсимволов. Рассмотрим основные моменты, на которые стоит обратить внимание:
СокрЛП() его не удаляет. Рекомендуется использовать функцию СтрЗаменить(Значение, Символ(160), " ") перед поиском.ТаблицеЗначений методом НайтиСтроки() регистр имеет значение. Приводите все к одному регистру через ВРег() или НРег(), если сопоставление идет в памяти.Для качественной загрузки данных из Excel мы рекомендуем:
НЕ ПометкаУдаления и НЕ ЭтоГруппа, чтобы избежать получения некорректных ссылок.ВыполнитьПакет() убедитесь, что вы обходите все результаты и используете метод Следующий() для выборки данных.Соответствие (где Ключ — Наименование, а Значение — Ссылка) обеспечит мгновенный поиск без повторных обращений к серверу.Использование этих подходов не только ускорит загрузку в десятки раз, но и сделает ваш код более надежным и устойчивым к ошибкам в данных.