При обработке объемных прайс-листов (от 50 000 до 100 000 строк и более) разработчики часто сталкиваются с критическим падением производительности. Процесс загрузки может растягиваться на долгие часы. Проанализируем основные причины замедления и разберем по шагам, как кардинально ускорить чтение данных из файлов и их последующую обработку в базе 1С.
Задержка при обработке больших прайс-листов обычно складывается из двух узких мест:
COMОбъект("Excel.Application") с поячеечным обращением вида Лист.Cells(Row, Col).Value порождает сотни тысяч межпроцессных COM-вызовов, что крайне медленно.Справочники.Номенклатура.НайтиПоРеквизиту() или выполнение отдельного запроса на каждой итерации цикла по 80 000 позиций заставляет СУБД многократно выполнять поиск, перегружая дисковую подсистему и процессор.Рассмотрим, как оптимизировать поиск существующих позиций. Вместо построчного обращения к базе данных применим пакетный запрос с передачей массива артикулов или временной таблицы.
Разберем базовый вариант ускорения через пакетный запрос и коллекцию Соответствие (хеш-таблицу со скоростью доступа O(1)):
// 1. Формируем массив уникальных артикулов из загруженных данных
МассивАртикулов = Новый Массив;
Для Каждого Стр Из ТаблицаЗагрузки Цикл
Если ЗначениеЗаполнено(Стр.Артикул) Тогда
МассивАртикулов.Добавить(Стр.Артикул);
КонецЕсли;
КонецЦикла;
// 2. Одним запросом выбираем существующие ссылки
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Артикул КАК Артикул,
| Номенклатура.Ссылка КАК Ссылка
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.Артикул В(&МассивАртикулов)";
Запрос.УстановитьПараметр("МассивАртикулов", МассивАртикулов);
РезультатЗапроса = Запрос.Выполнить();
// 3. Заполняем соответствие для мгновенного поиска по ключу
КартаСсылок = Новый Соответствие;
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
КартаСсылок.Вставить(Выборка.Артикул, Выборка.Ссылка);
КонецЦикла;
// 4. Сопоставляем данные
Для Каждого Стр Из ТаблицаЗагрузки Цикл
СсылкаНайдена = КартаСсылок[Стр.Артикул];
Если СсылкаНайдена <> Неопределено Тогда
Стр.Номенклатура = СсылкаНайдена;
Иначе
// Позиция новая — помечаем к созданию
КонецЕсли;
КонецЦикла;
Важно: Убедимся, что в дереве метаданных конфигурации у реквизита Артикул справочника Номенклатура установлено свойство Индексировать. Без индекса СУБД будет выполнять полное сканирование таблицы (Table Scan), нивелируя выигрыш от оптимизации запроса.
Для прямого извлечения данных из файлов .xls и .xlsx без запуска тяжелого процесса Excel отлично подходит технология ADO. Данные считываются как из обычной реляционной таблицы.
Посмотрим на пример реализации подключения и выполнения запроса к книге Excel:
Функция ПрочитатьExcelЧерезADO(ПутьКФайлу, ИмяЛиста = "Sheet1")
ДанныеТЗ = Новый ТаблицаЗначений;
СтрокаСоединения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + СокрЛП(ПутьКФайлу) + ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";";
Соединение = Новый COMОбъект("ADODB.Connection");
Попытка
Соединение.Open(СтрокаСоединения);
Исключение
ВызватьИсключение "Не удалось подключиться к OLEDB: " + ОписаниеОшибки();
КонецПопытки;
ТекстЗапроса = "SELECT * FROM [" + ИмяЛиста + "$]";
Записи = Новый COMОбъект("ADODB.Recordset");
Записи.Open(ТекстЗапроса, Соединение);
// Создаем колонки в Таблице Значений по полям выборки
КоличествоКолонок = Записи.Fields.Count;
Для Номер = 0 По КоличествоКолонок - 1 Цикл
ИмяПоля = Записи.Fields(Номер).Name;
ДанныеТЗ.Колонки.Добавить(СтрЗаменить(ИмяПоля, " ", "_"));
КонецЦикла;
// Считываем строки
Пока НЕ Записи.EOF Цикл
НоваяСтрока = ДанныеТЗ.Добавить();
Для Номер = 0 По КоличествоКолонок - 1 Цикл
НоваяСтрока[Номер] = Записи.Fields(Номер).Value;
КонецЦикла;
Записи.MoveNext();
КонецЦикла;
Записи.Close();
Соединение.Close();
Возврат ДанныеТЗ;
КонецФункции
Начиная с платформы 8.3.6, платформа 1С предоставляет нативный способ чтения табличных файлов без COM-библиотек с помощью метода ТабличныйДокумент.Прочитать(). Это кроссплатформенное решение, работающее на стороне сервера (в том числе под управлением Linux).
Функция ПрочитатьExcelВТаблицуЗначений(ПутьКФайлу)
ТабДок = Новый ТабличныйДокумент;
ТабДок.Прочитать(ПутьКФайлу, СпособЧтенияЗначенийТабличногоДокумента.Текст);
ВсегоСтрок = ТабДок.ВысотаТаблицы;
ВсегоКолонок = ТабДок.ШиринаТаблицы;
Таблица = Новый ТаблицаЗначений;
Для НомерКолонки = 1 По ВсегоКолонок Цикл
Таблица.Колонки.Добавить("Колонка" + НомерКолонки);
КонецЦикла;
Для НомерСтроки = 1 По ВсегоСтрок Цикл
Стр = Таблица.Добавить();
Для НомерКолонки = 1 По ВсегоКолонок Цикл
Стр[НомерКолонки - 1] = ТабДок.ПолучитьОбласть(НомерСтроки, НомерКолонки).ТекущаяОбласть.Текст;
КонецЦикла;
КонецЦикла;
Возврат Таблица;
КонецФункции
Если необходимо использовать Excel.Application (например, при наличии сложных формул или защиты листов), категорически избегаем чтения по одной ячейке. Выгрузим весь диапазон целиком в COMSafeArray одной операцией:
Excel = ПолучитьCOMОбъект(ПутьКФайлу);
// Считываем весь используемый диапазон листа в безопасный массив
МассивДанных = Excel.Sheets(1).UsedRange.Value.Unload;
Excel.Application.Quit();
// МассивДанных представляет собой двумерный массив, который обходится стандартными средствами языка 1С
Если есть техническая возможность получать от поставщика выгрузку в формате XML или JSON, обработка станет значительно быстрее. Форматы XML и JSON являются специализированными транспортными протоколами.
Для разбора файла XML на 80 000 записей нельзя использовать объектную модель DOM (ПостроительDOM), так как она полностью загружает файл в оперативную память. Используем потоковое последовательное чтение через объект ЧтениеXML:
Чтение = Новый ЧтениеXML;
Чтение.ОткрытьФайл(ПутьКФайлуXML);
Пока Чтение.Прочитать() Цикл
Если Чтение.ТипУзла = ТипУзлаXML.НачалоЭлемента И Чтение.Имя = "Товар" Тогда
Артикул = Чтение.ПолучитьАтрибут("Артикул");
Наименование = Чтение.ПолучитьАтрибут("Наименование");
Цена = Число(Чтение.ПолучитьАтрибут("Цена"));
// Обрабатываем элемент в потоке
КонецЕсли;
КонецЦикла;
Чтение.Закрыть();
Формат .xlsx внутри представляет собой обычный ZIP-архив с XML-документами. При необходимости максимальной скорости без внешних драйверов можно распаковать файл с помощью объекта ЧтениеZipФайла и напрямую разобрать таблицы строк и ячеек через ЧтениеXML.
Создание новых элементов номенклатуры и запись цен также требуют оптимизации:
НачатьТранзакцию() и ЗафиксироватьТранзакцию().РегистрСведенийМенеджерЗаписи. Формируем единый набор записей РегистрСведенийНаборЗаписей или создаем документ «Установка цен номенклатуры».ФоновыеЗадания.Выполнить), исключая передачу массивов между клиентом и сервером.