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

16.06.14

База данных - 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 раза

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

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

 

ADO пакетная запись пакетный insert

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2975    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5107    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7634    158    ZAOSTG    67    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5977    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8868    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5105    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16183    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. xzorkiix 34 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 всегда уникальный
RomanMartynenko; diamond_nsk; +2 Ответить
3. Dach 372 19.06.14 10:21 Сейчас в теме
(1) xzorkiix, GUID "всегда уникальный" в рамках одной ИБ 1С-ки. В моем случае внешний источник общий для нескольких ИБ... Можно было еще использовать ЗначениеВСтрокуВнутр.
2. xzorkiix 34 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
4. Dach 372 19.06.14 10:26 Сейчас в теме
(2) xzorkiix, не знаю, но представь каким будет текст запроса, сформированного динамически по таблице со 50 тысячей строк например. Я даже не уверен, что сервер проглотит такой запрос.
5. shmellevich 134 21.06.14 11:45 Сейчас в теме
Автор, для таких объемов эффективней использовать BULK INSERT
Я в тестовом режиме использую загрузку из csv, ~500k строк 32 столбика (180 Мб) залетает в таблицу за 20 секунд.
6. Dach 372 21.06.14 19:05 Сейчас в теме
(5) shmellevich, эта команда позволяет инсертить из файла. А у меня ситуация, когда данные в оперативной памяти на клиенте. Причем они получаются динамически, порциями (чтобы не вылететь по нехватке памяти).
7. Diversus 2306 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 372 25.08.14 01:08 Сейчас в теме
(7) не тестировал, честно говоря. Если запись идет в момент update - то такой метод никакого выигрыша не даст, а вот если в момент close - то это уже другое дело....
9. Diversus 2306 08.09.14 18:09 Сейчас в теме
(8) Протестирую отпишусь по этому вопросу...
12. talych 17 26.12.18 15:25 Сейчас в теме
(8) Ну и как решили? при апдейте пишет или при Закрытии?
10. Gukov10 29.10.14 17:34 Сейчас в теме
11. Gukov10 25.12.14 17:57 Сейчас в теме
Узкое место - файловая подсистема, где TempDB лежала.
Вытащили ее на отдельный SSD диск. Скорость записи выросла еще в сотни раз.
Не думаю,что подход с BULK INSERT будет быстрей с учетом того,что файл, который будем BULKать еще нужно подготовить (записать).
Оставьте свое сообщение