При интеграции систем 1С:Предприятие со сторонними базами данных, особенно при построении хранилищ данных (DWH) или BI-систем, разработчики часто сталкиваются с необходимостью передачи огромных объемов информации. Когда счет идет на миллионы строк (например, при выгрузке детальной аналитики по проводкам или движениям регистров за несколько лет), стандартные и интуитивно понятные методы начинают работать неприемлемо медленно. Выгрузка может растягиваться на часы, а иногда и вовсе приводить к переполнению оперативной памяти сервера.
В этой статье мы подробно разберем практический кейс оптимизации выгрузки 4 000 000 строк из 1С в СУБД MS SQL. Мы выясним, почему популярные методы работы с файлами в 1С дают прогрессирующее замедление, как правильно оптимизировать код циклов и как настроить процесс на стороне SQL-сервера для достижения максимальной скорости.
Рассмотрим классический сценарий. У нас есть сложная таблица значений, полученная в результате выполнения тяжелого запроса (например, данные по доходам, расходам и производству в разрезе аналитики). Нам нужно выгрузить эту таблицу во внешнюю базу MS SQL. Постраничный INSERT через ADO-подключение работает крайне медленно из-за сетевых задержек на каждый запрос (Round-trip time). Самым очевидным решением кажется выгрузка данных в промежуточный CSV-файл с последующей его загрузкой на стороне SQL-сервера с помощью высокопроизводительной команды BULK INSERT.
Однако при реализации этого механизма «в лоб» разработчики часто наступают на скрытые грабли платформы 1С. Рассмотрим исходный код формирования файла, который приводит к резкому падению производительности при росте числа строк:
ФайлCSV = Новый ТекстовыйДокумент;
ФайлCSV.ДобавитьСтроку(ТекстШапки);
Для Каждого СтрокаТ Из ТаблицаДанных Цикл
ТекстСтроки = "";
Для Каждого Колонка Из ТаблицаДанных.Колонки Цикл
Если ТекстСтроки = "" Тогда
ТекстСтроки = СокрЛП(СтрокаТ[Колонка.Имя]);
Иначе
ТекстСтроки = ТекстСтроки + Разделитель + СокрЛП(СтрокаТ[Колонка.Имя]);
КонецЕсли;
КонецЦикла;
ФайлCSV.ДобавитьСтроку(ТекстСтроки);
КонецЦикла;
ФайлCSV.Записать(ИмяФайла, КодировкаТекста.UTF16);
При запуске такого кода на объеме в 200 000 строк процесс еще может казаться относительно быстрым, но при попытке выгрузить 4 миллиона строк время работы начинает расти нелинейно (экспоненциально). Чем больше строк добавлено в документ, тем медленнее обрабатывается каждая следующая строка. Выгрузка зависает на 2,5–3 часа.
Выясним причину этого поведения: Объект ТекстовыйДокумент хранит все строки в оперативной памяти сервера в виде единого массива. При вызове метода ДобавитьСтроку() платформе приходится постоянно перевыделять память под увеличивающийся массив строк и производить их внутреннюю индексацию. На больших объемах данных это приводит к колоссальным накладным расходам и деградации производительности.
Для того чтобы избежать накопления гигантских объемов данных в оперативной памяти и исключить постоянное перевыделение адресного пространства, необходимо использовать потоковую запись на диск. В платформе 1С для этого предназначены объекты ЗаписьТекста или более современный ЗаписьДанных.
Эти объекты сбрасывают буфер данных на жесткий диск порциями, сохраняя линейную (стабильную) скорость записи вне зависимости от того, сколько миллионов строк уже записано в файл. Наш расход оперативной памяти при этом стремится к нулю.
Перепишем алгоритм формирования файла с использованием объекта ЗаписьДанных:
ФайлCSV = Новый ЗаписьДанных(ИмяФайла, КодировкаТекста.UTF16);
ФайлCSV.ЗаписатьСтроку(ТекстШапки);
Для Каждого СтрокаТ Из ТаблицаДанных Цикл
ТекстСтроки = "";
Для Каждого Колонка Из ТаблицаДанных.Колонки Цикл
Если ТекстСтроки = "" Тогда
ТекстСтроки = СокрЛП(СтрокаТ[Колонка.Имя]);
ФайлCSV.ЗаписатьСимволы(ТекстСтроки);
Иначе
ФайлCSV.ЗаписатьСимволы(Разделитель);
ФайлCSV.ЗаписатьСимволы(СокрЛП(СтрокаТ[Колонка.Имя]));
КонецЕсли;
КонецЦикла;
ФайлCSV.ЗаписатьСтроку(""); // Переход на новую строку
КонецЦикла;
ФайлCSV.Закрыть();
Уже одно это изменение позволяет сократить время формирования текстового файла в разы, убирая прогрессирующее затухание скорости.
Даже при использовании потоковой записи код все еще содержит внутренние замедлители. Обратите внимание на обход колонок внутри цикла по строкам: Для Каждого Колонка Из ТаблицаДанных.Колонки. При каждой итерации основного цикла (а их у нас 4 миллиона!) платформа вынуждена обращаться к коллекции метаданных колонок таблицы значений и получать имя колонки через свойство Колонка.Имя.
Проанализируем шаги оптимизации цикла:
Внедряя эти правила, мы получаем максимально оптимизированный код для подготовки файла выгрузки:
// Шаг 1. Предварительно кэшируем индексы и имена колонок
МассивИменКолонок = Новый Массив;
Для Каждого Колонка Из ТаблицаДанных.Колонки Цикл
МассивИменКолонок.Добавить(Колонка.Имя);
КонецЦикла;
КоличествоКолонок = МассивИменКолонок.Количество(); ПределКолонок = КоличествоКолонок - 1;
// Шаг 2. Инициализируем поток записи
ЗаписьВыгрузки = Новый ЗаписьТекста(ИмяФайла, КодировкаТекста.UTF8);
ЗаписьВыгрузки.ЗаписатьСтроку(ТекстШапки);
// Шаг 3. Запускаем высокопроизводительный цикл
Для Каждого СтрокаТ Из ТаблицаДанных Цикл
СтрокаДляЗаписи = "";
// Быстрый обход по индексу без обращения к метаданным
Для ИндексКолонки = 0 По ПределКолонок Цикл
ЗначениеПоля = СтрокаТ[ИндексКолонки];
ТекстПоля = ?(ЗначениеПоля = Неопределено, "", СокрЛП(СтрокаТ[ИндексКолонки]));
Если ИндексКолонки = 0 Тогда
СтрокаДляЗаписи = ТекстПоля;
Иначе
СтрокаДляЗаписи = СтрокаДляЗаписи + Разделитель + ТекстПоля;
КонецЕсли;
КонецЦикла;
ЗаписьВыгрузки.ЗаписатьСтроку(СтрокаДляЗаписи);
КонецЦикла;
ЗаписьВыгрузки.Закрыть();
Такой подход обеспечивает строго линейную скорость работы. 4 миллиона строк кода будут обработаны и записаны в файл за несколько минут.
В сообществе разработчиков 1С хорошо известен тот факт, что специализированный объект ЗаписьXML реализован на уровне платформы на языке C++ и оптимизирован гораздо лучше, чем универсальные текстовые потоки. Запись структурированного XML-файла может происходить быстрее, чем ручная конкатенация строк в цикле.
Схема выглядит следующим образом: мы пишем данные в файл с помощью ЗаписьXML, а на стороне MS SQL используем встроенный в T-SQL парсер XML (метод nodes()) для разбора файла и быстрой вставки. Однако стоит учитывать, что разбор XML на стороне SQL-сервера на экстремальных объемах в миллионы строк требует значительных ресурсов процессора СУБД.
После того как мы научились мгновенно генерировать CSV-файл на стороне 1С, узкое место смещается на сторону СУБД. Простая команда BULK INSERT без дополнительных настроек может тратить много времени на логирование транзакций и проверку ограничений таблицы.
Рассмотрим ключевые приемы оптимизации загрузки файлов на стороне MS SQL:
TABLOCK, SQL Server наложит монопольную блокировку на всю таблицу. Это переведет операцию в режим минимального протоколирования (минимальной записи в лог транзакций LDF), что ускорит операцию записи в десятки раз.ALTER INDEX ALL ON ВашаТаблица DISABLE), выполнить BULK INSERT, а затем заново построить их (REBUILD).BATCHSIZE = 100000 или 200000. Это позволит СУБД фиксировать транзакции порциями, предотвращая раздувание лога транзакций.Пример оптимизированного T-SQL скрипта для массового импорта подготовленного CSV-файла:
BULK INSERT ИмяЦелевойТаблицы
FROM 'C:\Uploads\data_1c.csv'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
FIRSTROW = 2, -- пропускаем шапку
BATCHSIZE = 200000,
TABLOCK -- включаем быстрое минимальное протоколирование
);
Давайте сопоставим результаты различных подходов при обработке больших массивов данных:
Использование правильных низкоуровневых объектов платформы 1С для работы с файловой системой (ЗаписьТекста, ЗаписьДанных) совместно с базовой оптимизацией циклов позволяет решать сложнейшие задачи интеграции и обмена данными без привлечения стороннего дорогостоящего ETL-инструментария.