Пакетная запись таблицы значений с клиента в СУБД (ускорение построчного 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 оптимизация Технологический журнал Системный администратор Программист Бесплатно (free)

Обсудим поиск и разбор причин длительных серверных вызовов CALL, SCALL.

24.06.2024    5309    ivanov660    12    

56

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

Метод очень медленно работает, когда параметр приемник содержит намного меньше свойств, чем источник.

06.06.2024    9470    Evg-Lylyk    61    

44

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

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

13.03.2024    5182    spyke    28    

49

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

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

13.03.2024    7707    vasilev2015    20    

42

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

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

2 стартмани

15.02.2024    12593    250    ZAOSTG    83    

115

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

Принимать, хранить и анализировать показания счетчиков (метрики) в базе 1С? Почему бы нет? Но это решение быстро привело к проблемам с производительностью при попытках построить какую-то более-менее сложную аналитику. Переход на PostgresSQL только временно решил проблему, т.к. количество записей уже исчислялось десятками миллионов и что-то сложное вычислить на таких объемах за разумное время становилось все сложнее. Кое-что уже практически невозможно. А что будет с производительностью через пару лет - представить страшно. Надо что-то предпринимать! В этой статье поделюсь своим первым опытом применения СУБД Clickhouse от Яндекс. Как работает, что может, как на нее планирую (если планирую) переходить, сравнение скорости работы, оценка производительности через пару лет, пример работы из 1С. Все это приправлено текстами запросов, кодом, алгоритмами выполненных действий и преподнесено вам для ознакомления в этой статье.

1 стартмани

24.01.2024    5787    glassman    18    

40

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

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

09.01.2024    14562    doom2good    49    

71
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. xzorkiix 35 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 383 19.06.14 10:21 Сейчас в теме
(1) xzorkiix, GUID "всегда уникальный" в рамках одной ИБ 1С-ки. В моем случае внешний источник общий для нескольких ИБ... Можно было еще использовать ЗначениеВСтрокуВнутр.
2. xzorkiix 35 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 383 19.06.14 10:26 Сейчас в теме
(2) xzorkiix, не знаю, но представь каким будет текст запроса, сформированного динамически по таблице со 50 тысячей строк например. Я даже не уверен, что сервер проглотит такой запрос.
5. shmellevich 136 21.06.14 11:45 Сейчас в теме
Автор, для таких объемов эффективней использовать BULK INSERT
Я в тестовом режиме использую загрузку из csv, ~500k строк 32 столбика (180 Мб) залетает в таблицу за 20 секунд.
6. Dach 383 21.06.14 19:05 Сейчас в теме
(5) shmellevich, эта команда позволяет инсертить из файла. А у меня ситуация, когда данные в оперативной памяти на клиенте. Причем они получаются динамически, порциями (чтобы не вылететь по нехватке памяти).
7. Diversus 2329 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 383 25.08.14 01:08 Сейчас в теме
(7) не тестировал, честно говоря. Если запись идет в момент update - то такой метод никакого выигрыша не даст, а вот если в момент close - то это уже другое дело....
9. Diversus 2329 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ать еще нужно подготовить (записать).
Оставьте свое сообщение