gifts2017

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

Опубликовал Роман С (Dach) в раздел Администрирование - Оптимизация БД (HighLoad)

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

Классический способ решения - использование ADO, обход строк таблицы в цикле и построчный INSERT с помощью конструкции INSERT INTO "+NameTable+" (ColumnName) values("+SetValue+")"
То есть, на каждую строку мы производим физическую запись в СУБД, заставляем работать носитель данных (жесткий диск например). Предлагаю способ, как ускорить этот процесс и записать всю ТЗ разом, пакетно.

Основная идея "честно украдена" у самой платформы 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 раза

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

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

 

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Евгений Шабалин (xzorkiix) 18.06.14 14:45
Вопрос. Зачем эта часть

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);
...Показать Скрыть


и почему например не так

УникальноеИмяВрТаблицы = "##" + СтрЗаменить(Новый УникальныйИдентификатор, "-", "");


когда GUID всегда уникальный
2. Евгений Шабалин (xzorkiix) 18.06.14 15:00
И ещё вопрос, насколько этот метод (запись во временную таблицу, а затем помещение выборки из нее в целевую таблицу) эффективней, варианта помещения в целевую таблицу наших данных через UNION ALL всё тех же строк.

Например

INSERT INTO pantscolor_t (procode,color,pic)
SELECT '74251', 'Black', '511black.jpg'
UNION ALL
SELECT '74251', 'Charcoal', '511charcoal.jpg' 
UNION ALL
SELECT '74251', 'Khaki', '511khaki.jpg' 
UNION ALL
SELECT '74251', 'Navy', '511navy.jpg' 
UNION ALL
SELECT '74251', 'OD Green', '511odgreen.jpg';
...Показать Скрыть

http://social.msdn.microsoft.com

с 2008 ms sql ещё появилась поддержка конструкции вида

-- Method 3 - SQL Server 2008+ Row Construction
INSERT INTO #SQLAuthority (ID, Value)
VALUES (1, 'First'), (2, 'Second'), (3, 'Third');

http://blog.sqlauthority.com
3. Роман С (Dach) 19.06.14 10:21
(1) xzorkiix, GUID "всегда уникальный" в рамках одной ИБ 1С-ки. В моем случае внешний источник общий для нескольких ИБ... Можно было еще использовать ЗначениеВСтрокуВнутр.
4. Роман С (Dach) 19.06.14 10:26
(2) xzorkiix, не знаю, но представь каким будет текст запроса, сформированного динамически по таблице со 50 тысячей строк например. Я даже не уверен, что сервер проглотит такой запрос.
5. Петр Вел (shmellevich) 21.06.14 11:45
Автор, для таких объемов эффективней использовать BULK INSERT
Я в тестовом режиме использую загрузку из csv, ~500k строк 32 столбика (180 Мб) залетает в таблицу за 20 секунд.
6. Роман С (Dach) 21.06.14 19:05
(5) shmellevich, эта команда позволяет инсертить из файла. А у меня ситуация, когда данные в оперативной памяти на клиенте. Причем они получаются динамически, порциями (чтобы не вылететь по нехватке памяти).
7. Виталий Барилко (Diversus) 22.08.14 21:51
(6) А Вы не тестировали массовую запись в таблицу с использованием ADODB.RecordSet?
Recordset = Новый COMОбъект("ADODB.Recordset");	
Recordset.CursorLocation = 2;
RecordSet.Open("table", Recordset);
Для каждого Строки Из ТаблицаДляВставки Цикл

    RecordSet.AddNew();
    RecordSet.Fields("rekvizit").Value = 123;
    RecordSet.Update();

КонецЦикла;
RecordSet.Close();
...Показать Скрыть

На сколько быстрее запись через временную таблицу MS SQL и через Recordset?
8. Роман С (Dach) 25.08.14 01:08
(7) не тестировал, честно говоря. Если запись идет в момент update - то такой метод никакого выигрыша не даст, а вот если в момент close - то это уже другое дело....
9. Виталий Барилко (Diversus) 08.09.14 18:09
(8) Протестирую отпишусь по этому вопросу...
10. Артем Жуков (Gukov10) 29.10.14 17:34
11. Артем Жуков (Gukov10) 25.12.14 17:57
Узкое место - файловая подсистема, где TempDB лежала.
Вытащили ее на отдельный SSD диск. Скорость записи выросла еще в сотни раз.
Не думаю,что подход с BULK INSERT будет быстрей с учетом того,что файл, который будем BULKать еще нужно подготовить (записать).