При работе с внешними базами данных через ADODB.Connection многие разработчики сталкиваются с тем, что классический метод вставки данных INSERT INTO ... VALUES при выполнении в цикле работает крайне медленно. В данной статье мы разберем, как оптимизировать этот процесс, минимизировать накладные расходы на передачу данных и существенно повысить скорость записи.
Основная причина низкой производительности заключается в том, что при отправке каждого отдельного оператора INSERT сервер баз данных должен каждый раз заново выполнять парсинг запроса, проверку прав доступа и построение плана выполнения. Если мы отправляем 4000 запросов, сервер проделывает эту тяжелую работу 4000 раз. Чтобы ускорить этот процесс, нам необходимо уменьшить количество обращений к SQL-движку.
Наиболее эффективным способом для умеренных объемов данных является использование пакетной вставки. Вместо того чтобы формировать отдельный запрос для каждой строки, мы объединяем их в одну команду с множеством наборов значений. Это позволяет SQL-серверу один раз скомпилировать план запроса и применить его ко всем переданным строкам.
Рассмотрим, как реализовать формирование такого пакета на стороне 1С с использованием объекта ЗаписьXML для быстрого построения строки запроса:
Функция ДобавитьЗаписиСКЛПакетом(Соединение, ТаблицаSQL, Поля, МассивТаблиц)
Для каждого ТекТаблица Из МассивТаблиц Цикл
КолСтрок = ТекТаблица.Количество();
Сч = 0;
ЗаписьXML = Новый ЗаписьXML;
ЗаписьXML.УстановитьСтроку();
ЗаписьXML.ЗаписатьБезОбработки("INSERT INTO " + ТаблицаSQL + " ([" + Поля[0].Значение + "], [" + Поля[1].Значение + "], [" + Поля[2].Значение + "], [" + Поля[3].Значение + "], [" + Поля[4].Значение + "]) VALUES");
Для каждого Стр Из ТекТаблица Цикл
Сч = Сч + 1;
Разделитель = ?(Сч = КолСтрок, "", ",");
ЗаписьXML.ЗаписатьБезОбработки("('" + Стр[0] + "', '" + Стр[1] + "', '" + Стр[2] + "', '" + Стр[3] + "', '" + Стр[4] + "')" + Разделитель);
КонецЦикла;
ТекстЗапроса = ЗаписьXML.Закрыть();
Соединение.Execute(ТекстЗапроса);
КонецЦикла;
КонецФункции
Обратите внимание на использование переменной Разделитель: мы ставим запятую между значениями всех строк, кроме последней. Оптимальный размер пакета для большинства задач — от 500 до 1000 строк.
Если объемы данных исчисляются сотнями тысяч записей, обычные INSERT-запросы, даже пакетные, могут не дать желаемого результата. В таких случаях стоит обратить внимание на Bulk Insert. Это специальный механизм SQL Server, предназначенный для максимально быстрого импорта данных из внешних файлов или потоков. Использование Bulk позволяет обойти логирование каждой отдельной операции, что в разы ускоряет запись в базу.
Помимо самого метода вставки, на скорость записи сильно влияют индексы. Каждый раз, когда мы добавляем запись в таблицу, SQL-сервер вынужден пересчитывать индексы, что требует дополнительных ресурсов. Чтобы ускорить массовое заполнение, можно выполнить следующие действия:
NVARCHAR(MAX), если достаточно VARCHAR(10)).TABLOCK: Использование хинта WITH (TABLOCK) в начале запроса INSERT может ускорить процесс за счет блокировки всей таблицы, что избавляет сервер от необходимости управлять блокировками на уровне каждой отдельной строки.Для достижения максимальной производительности при работе с регистрами сведений из 1С через ADODB, придерживайтесь следующих правил:
ЗаписьXML, так как он работает быстрее обычного сложения строк.Bulk Insert.Помните, что ключевая задача разработчика при оптимизации — это сокращение количества «диалогов» между приложением 1С и SQL-сервером. Чем меньше команд передается через Соединение.Execute(), тем быстрее будет работать ваш код.