Пакетная запись таблицы значений с клиента в СУБД (ускорение построчного INSERT)

Основная идея «честно украдена» у самой платформы 1С 😉 Что происходит на уровне СУБД, когда в пакетном запросе типизированная таблица значений помещается в запрос? СУБД создает временную таблицу и с помощью построчного INSERT помещает строки ТЗ в ВТ. ВТ существует в оперативной памяти сервера СУБД. Обращение к оперативной памяти, разумеется, существенно быстрее обращений к физическому носителю.

Возьмем нашу ТЗ с большим количеством строк, поместим все строки в ВТ на СУБД, а затем на уровне СУБД выберем все строки и прозведем пакетную запись с помощью конструкции INSERT INTO NameRealTable SELECT * FROM NameTempTable.

Для выполнения задачи удобно создать и использовать хранимую процедуру, как это сделать — описывать не буду, примеров в сети достаточно. Как работать с объектом ADO — тоже. Приведу сам код хранимой процедуры и обращения к ней из 1С. Процедура написана для MS SQL 2012. 

 

ALTER PROCEDURE [dbo].[ProcedurePacketInsetInTable]

--параметры: @NameTable - уникальное имя глобальной временной таблицы, формируемое на клиенте, несмотря на мануал на msdn, даже в рамках одного коннекта

--разные объекты command могут работать только с глобальной ВТ, для надежности записи и чтения имя формируем уникальным  

--@FlagCreate - флаг создания глобальной временной таблицы  

--@FlagInsert - флаг результируещей записи (записи всей переданной построчно таблицы значений)  

--@Text       - текст запроса, содержащий все значения     

@NameTable nchar(100),  @NameTableDelete nchar(100),  @FlagCreate int, @FlagInsert int,  @Text nvarchar(max)      

AS BEGIN  --результирующий инсерт делаем, если передан флаг, если нет - создаем и заполняем таблицу  IF @FlagInsert = 0   --////   

 BEGIN    --если передан флаг создания таблицы - динамически создадим глобальную временную таблицу, используя переданное имя    

  IF @FlagCreate = 1

BEGIN      --хотя SQL сам удаляет ВТ при отсутствии обращений к ним, тем не менее, подстрахуемся:      

    DECLARE @DinamicDeleteTable nvarchar(max)      

    SET @DinamicDeleteTable = 'IF OBJECT_ID('+@NameTableDelete+') IS NOT NULL DROP TABLE ' + @NameTable      

                                EXEC sp_executesql @DinamicDeleteTable

DECLARE @DinamicCreate nvarchar(max)      

    SET @DinamicCreate = 'SELECT TOP 0 * INTO ' + @NameTable + ' FROM EdbTranzactionsPC'         

    EXEC sp_executesql @DinamicCreate     

   END            --если флаг не передан - таблица уже создана, выполняем динамический построчный инсерт в глобальную ВТ        

  ELSE          EXEC sp_executesql @Text

END   --////  

ELSE  --передан флаг записи, выполняем запись и очистку ВТ   

 BEGIN    DECLARE @DinamicALLInsert nvarchar(max)    

 SET @DinamicALLInsert = 'INSERT INTO EdbTranzactionsPc SELECT * FROM ' + @NameTable    

 EXEC sp_executesql @DinamicALLInsert        DECLARE @DinamicDELETE nvarchar(max)    

 SET @DinamicDELETE = 'DELETE FROM ' + @NameTable     

 EXEC sp_executesql @DinamicDELETE        

 END    

END

Отмечу, что в моем случае таблица значений на клиенте получалась расчетным способом неоднократно (в цикле), эмпирически выявил, что разные «command.execute()» могут «видеть» лишь глобальную временную таблицу (с двумя #).

Вызовы процедуры из 1С:

1. На первом шаге создаем глобальную ВТ с уникальным именем. Уникальное имя нужно для того, чтобы с разных экземпляров клиента могла производиться одновременная загрузка различных данных.

Параметры можно устанавливать методом Append, я сделал просто обход коллекции ввиду их небольшого количества.

UUID = Ссылка.УникальныйИдентификатор();
Ч1 = Сред(UUID,20,4);
Ч2 = Сред(UUID,25,12);
Ч3 = Сред(UUID,15,4);
Ч4 = Сред(UUID,10,4);
Ч5 = Сред(UUID,1,8);
УникальноеИмяВрТаблицы = СОКРЛП("##" + Ч1 + Ч2 + Ч3 + Ч4 + Ч5);

//1 шаг
Для Каждого Parameter Из мCommand.Parameters Цикл
Если Parameter.Name = "@FlagCreate" Тогда
Parameter.Value = 1; //создаем ВТ
ИначеЕсли Parameter.Name = "@NameTableDelete" Тогда
Parameter.Value = "'tempdb.." + УникальноеИмяВрТаблицы + "'";
ИначеЕсли Parameter.Name = "@FlagInsert" Тогда
Parameter.Value = 0; //не инсертим
ИначеЕсли Parameter.Name = "@Text" Тогда
Parameter.Value = ""; //не инсертим
ИначеЕсли Parameter.Name = "@NameTable" Тогда
Parameter.Value = УникальноеИмяВрТаблицы; //имя ВТ
КонецЕсли;
КонецЦикла;

Попытка
мCommand.Execute();
исключение
ВывестиСообщение(ОписаниеОшибки(), СтатусСообщения.Внимание,СтруктураЛогФайлов);
Возврат;
КонецПопытки;
//

2. На втором щаге мы обходим нашу таблицу значений в цикле и формируем текст запроса. 

Не забываем, что числа нужно приводить к нужному виду, а строки обрамлять апострофами.

ТекстЗапроса = "INSERT INTO "+УникальноеИмяВрТаблицы+" (Column1) values("+Формат(СтрокаТЗ.ЗначениеДляЗаписи,"ЧРД=.;ЧГ=0;ЧН=")+")";

//2 шаг
Для Каждого Parameter Из Command.Parameters Цикл
Если Parameter.Name = "@FlagCreate" Тогда
Parameter.Value = 0; //не создаем
ИначеЕсли Parameter.Name = "@NameTableDelete" Тогда
Parameter.Value = "'tempdb.." + УникальноеИмяВрТаблицы + "'";
ИначеЕсли Parameter.Name = "@Text" Тогда
Parameter.Value = ТекстЗапроса; //заполняем значения
ИначеЕсли Parameter.Name = "@FlagInsert" Тогда
Parameter.Value = 0; //не инсертим
ИначеЕсли Parameter.Name = "@NameTable" Тогда
Parameter.Value = УникальноеИмяВрТаблицы;
КонецЕсли;
КонецЦикла;

Попытка
Command.Execute();
исключение
ВывестиСообщение(ОписаниеОшибки(), СтатусСообщения.Внимание,СтруктураЛогФайлов);
Возврат Ложь;
КонецПопытки; 

3. На третьем шаге производим пакетную запись. Здесь я использую открытие и фиксацию транзакции СУБД, так как происходит непосредственная запись на физический носитель.

Отмечу, что даже если база внешнего источника используется в режиме «симпл», лог транзакции писаться все равно будет (иначе «роллбэк» был бы просто невозможен).

После успешного завершения лог автоматически очистится, но размер файла лога не изменится. То есть, если будет производиться пакетная запись например 2 млн строк — лог вырастет до десятка Гб (но будет пустым),

поэтому для базы неплохо настроить регламентное задание и «шринкать» лог. 

 //инсертим весь результат
//3 шаг
Для Каждого Parameter Из Command.Parameters Цикл
Если Parameter.Name = "@FlagInsert" Тогда
Parameter.Value = 1; //инсертим
КонецЕсли;
КонецЦикла;

Попытка
мConnectionКонсолидация.BeginTrans();
Command.Execute();
мConnectionКонсолидация.CommitTrans();
исключение
Petrol.ВывестиСообщение(ОписаниеОшибки(), СтатусСообщения.Внимание,СтруктураЛогФайлов);
мConnectionКонсолидация.RollbackTrans();
ВывестиСообщение("Результирующая запись в базу продаж по строке " + стр.НомерСтроки +" не удалась!",СтатусСообщения.Внимание,СтруктураЛогФайлов);
Возврат Ложь;
КонецПопытки;

4. На четвертом шаге закроем коннект, в рамках которого мы работали с внешним источником данных.

При этом созданная глобальная ВТ уничтожится автоматически. 

Если мConnectionКонсолидация.State = 1 Тогда
мConnectionКонсолидация.Close();
КонецЕсли;

Параметры открываемого коннекта:

//
мConnectionКонсолидация = Новый COMОбъект("ADODB.Connection");
мConnectionКонсолидация.ConnectionTimeOut = 0; //таймаут ожидания коннекта
мConnectionКонсолидация.CommandTimeOut = 0;
мConnectionКонсолидация.CursorLocation = 3;
мConnectionКонсолидация.ConnectionString = СокрЛП(мСтрСоединения);
//для изоляции транзакций, режим Serializable (блокировка грязного чтения, чтения фантомов, диапазона индексов)
мConnectionКонсолидация.IsolationLevel = 1048576;

 

Мне удалось ускорить запись 2 млн строк примерно в 2 раза

Ограничивающими факторами являются функции форматирования чисел и преобразования строк

Надеюсь, мой опыт будет полезен Вам, коллеги.