Предисловие

Microsoft SQL Server поддерживает так называемые BULK-операции, используемые для быстрого изменения больших объемов данных в базе. В статье пойдет речь о практических примерах их использования. Все примеры сделаны в контексте платформы 1С (а как иначе).

Суть проблемы

Чем больше база данных - тем больше и ответственность, потому что появляются такие проблемы, о которых раньше можно было даже не подозревать. Это и влияние неоптимальных запросов на работу всей системы, плохое обслуживание индексов и статистик, “чудеса” платформенных запросов, стратегии бэкапирования и многое многое другое. Но мы, конечно же, обо всем этом говорить сегодня не будем.

Сегодня в центре внимания будет другая проблема - оптимизация операций массовой вставки / обновления данных в базе с помощью BULK-операций СУБД, а также как это можно использовать на практике. Все примеры ниже не являются простыми и не претендуют на универсальное решение. Основная цель публикации - демонстрация возможностей СУБД в контексте платформы 1С для решения подобных задач.

Полезна ли будет для Вас эта информация? Если Вы когда-либо имели дело хотя бы с одной из следующих задач, то материал может пригодиться:

• Выгрузка большого массива данных из 1С во внешнюю базу данных (SQL Server, PostgreSQL и др. СУБД). Надеюсь, Вы не делали выгрузку большим количеством операций INSERT, а если делали, то читать обязательно.
• Загрузка очень больших массивов данных из внешнего источника в таблицы 1С (регистры сведений, справочники и т.д.). Конечно, чаще всего это делать лучше средствами платформы, но что если время выполнения категорически не устраивает?
• Специфика работы с базой требует частого восстановления данных. Постоянно восстанавливать бэкап и не жалеть на это время?

Почему для платформы 1С это может быть проблемой? Вот несколько ситуаций:

• Нужно загрузить несколько миллионов записей в регистр сведений. Как это обычно делают? Правильно - записывают эти миллионы записей через набор регистров сведений, устанавливая необходимые отборы. На уровне СУБД это миллионы операций INSERT. Оптимально ли это?
• Есть внешняя база данных и нужно туда делать массовую выгрузку за предыдущий месяц после закрытия периода. Вроде все просто. Но 1Сный путь в этом случае обычно принимает один из возможных вариантов (но не обязательно):
  • Разработчик делает запрос на стороне 1С и выгружает во внешнюю базу с помощью отдельных операций INSERT через ADO. Думаете это быстро на больших массивах данных?
  • Делает то же самое, что и в предыдущем пункте, через внешние источники данных. В них вызывается хранимая процедура, которая фактически делает отдельные INSERT’ы. Но разработчик утверждает, что это быстрее чем работа с ADO. Улучшит ли это ситуацию?
• Обмен данными между базами 1С, но требовательный к скорости передачи массивов данных. То есть операцию нужно выполнить как можно скорее. Вместо 6 часов нужно уменьшить время обмена до 10 минут. Средствами платформы это сделать либо очень трудно, либо еще труднее (заметьте, я не сказал невозможно!).

Тут то на помощь и могут прийти возможности BULK-операций. Большинство примеров будет в рамках Microsoft SQL Server, но и для PostgreSQL будет кое-что интересное.

Немного теории

С помощью операций BULK INSERT и BULK MERGE можно эффективно загружать большие массивы данных в базу. Эти способы имеют преимущества в производительности по сравнению с методами загрузки данных через множественные операции INSERT, в том числе и пакетный INSERT. Ниже мы не будем останавливаться на сравнении BULK и пакетного INSERT, но будут некоторые ссылки на сторонние публикации для сравнения.

Почему BULK INSERT быстрее? Потому что это минимально логируемая операция, объем логов которой зависит от модели восстановления базы, наличия индексов и ограничений. Также с помощью некоторых параметров можно управлять размерами порций данных, которые будут записываться в таблицу в рамках одной транзакции, что также может привести к ускорению операции. Например, если у Вас установлена модель восстановления “Простая” или “C неполным протоколированием”, то использование BULK-операций может существенно ускорить операции загрузки данных. Но даже если у вас “Полная” модель восстановления, то и в этом случае они помогут ускорить операции массовой загрузки / изменения данных, особенно если это касается платформы 1С. Все это будет продемонстрировано ниже.

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

Подробную информацию по использованию BULK INSERT можно найти на MSDN. Там же есть информация по операциям массового импорта данных. Далее мы не будем рассматривать все особенности BULK-операций, а лишь то, что будет относиться к практическим кейсам.

Когда использовать

Будем честными и признаем, что платформа 1С все же использует BULK-операции в особых случаях. Например, если Вы загружаете DT-выгрузку базы, то платформа использует операции BUKL INSERT для переноса данных в таблицы SQL Server. Вот пример SQL-команды при загрузке данных из DT-файла.

insert bulk _AccRg786(
    [_Period] datetime2(0),
    [_RecorderTRef] binary(4),
    [_RecorderRRef] binary(16),
    [_LineNo] numeric(9,0),
    [_Active] binary(1),
    [_AccountDtRRef] binary(16),
    [_AccountCtRRef] binary(16),
    [_Fld787RRef] binary(16),
    [_Fld788DtRRef] binary(16),
    [_Fld788CtRRef] binary(16),
    [_Fld789DtRRef] binary(16),
    [_Fld789CtRRef] binary(16),
    [_Fld790] numeric(15,2),
    [_Fld791Dt] numeric(15,2),
    [_Fld791Ct] numeric(15,2),
    [_Fld792Dt] numeric(15,3),
    [_Fld792Ct] numeric(15,3),
    [_Fld793Dt] numeric(15,2),
    [_Fld793Ct] numeric(15,2),
    [_Fld794Dt] numeric(15,2),
    [_Fld794Ct] numeric(15,2),
    [_Fld795Dt] numeric(15,2),
    [_Fld795Ct] numeric(15,2),
    [_Fld796] nvarchar(150) collate Cyrillic_General_CI_AS,
    [_Fld797] binary(1),
    [_Fld774] numeric(7,0),
    [_EDHashDt] numeric(10,0),
    [_EDHashCt] numeric(10,0)
) with (
    TABLOCK, 
    ROWS_PER_BATCH=10000
)

Также Bulk Insert используется в некоторых случаях при реструктуризации базы данных. К сожалению, при разработке мы ничего не можем сделать, чтобы платформа начала использовать BULK-операции в прикладных решениях, таких возможностей просто не предоставляется. Но есть обходные пути, с которыми Вы познакомитесь ниже.

Запрос выше не содержит никакой информации об источнике загружаемых данных. Так происходит, потому что трассировка отслеживает только TDS-пакет, который анонсирует начало загрузки данных, а также таблицу назначения для загрузки. Данные же фактически передаются последующими пакетами.

Поскольку платформа 1С не предоставляет штатные возможности использования операций массовой вставки / обновления данных, то использовать подобный подход работы повсеместно просто не имеет смысла. Эти решения обосновано применять для интеграций, массовых загрузок данных, построения внешнего хранилища данных, восстановления данных и др. То есть там, где штатные возможности не удовлетворяют потребностям бизнеса. Если Вы не видите для себя никаких преимуществ в их использовании, то скорее всего они и не пригодятся.

Примеры задач

Теперь рассмотрим несколько примеров. В качестве полигона будем использовать демобазу БСП. Мы сосредоточимся больше на BULK-операциях и не будем рассматривать такие вопросы как: отключение индексов и ограничений на время загрузки данных; или изменение модели восстановления базы и др. Просто помните, что отключение индексов на время загрузки данных может значительно ускорить операцию.

Все примеры выдуманные! Любое сходство с реальными проектами и задачами случайно! Все примеры очень упрощенные.

Архивирование замеров производительности

Во многих конфигурациях есть подсистема “Оценка производительности”, собирающая замеры времени выполнения операций. В зависимости от интенсивности работы в базе, регистр может наполняться большим количеством записей, что приводит к излишнему росту базы данных. В подсистеме уже есть механизмы экспорта и очистки исторических данных средствами платформы, но при больших объемах логов эти операции могут выполняться значительное время.

В этом случае можно пойти другим путем и сделать выгрузку средствами SQL Server в другую внешнюю базу с логами, после чего удалять выгруженные данные в базе 1С.

Как это сделать? Допустим у нас есть 1Сная база, в которой имеются такие таблицы.

Внешняя база хранит историю замеров в следующем виде.

CREATE TABLE [dbo].[KeyOperations](
	[ID] [binary](16) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Description] [nvarchar](max) NULL,

	CONSTRAINT [PK_KeyOperations] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)  ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[PerformanceMeasurements](
	[KeyOperation] [binary](16) NOT NULL,
	[BeginDate] [numeric](15, 0) NOT NULL,
	[SessionID] [numeric](15, 0) NOT NULL,
	[PeriodHour] [datetime2](7) NOT NULL,
	[Duration] [numeric](15, 3) NOT NULL,
	[UserName] [nvarchar](150) NOT NULL,
	CONSTRAINT [PK_PerformanceMeasurements] PRIMARY KEY CLUSTERED 
	(
		[KeyOperation] ASC,
		[BeginDate] ASC,
		[SessionID] ASC,
		[PeriodHour] ASC
	) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PerformanceMeasurements]  WITH CHECK ADD	
	CONSTRAINT [FK_PerformanceMeasurements_KeyOperation] FOREIGN KEY([KeyOperation])
REFERENCES [dbo].[KeyOperations] ([ID])
GO

ALTER TABLE [dbo].[PerformanceMeasurements] CHECK CONSTRAINT [FK_PerformanceMeasurements_KeyOperation]
GO

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

-- Дано:
--	1. Базы [bsl] (исходная база) и [PerfMonitoring] (приемник данных)
--	2. [_Reference2598] - таблица ключевых операций на стороне 1С
--		_IDRRef		 - Идентификатор
--		_Description - Имя операции
--		_Fld4266	 - Служебное имя ключевой операции
--	3. [_InfoRg5892] - таблица регистра замеров времени на стороне 1С
--		_Fld5893RRef - Ключевая операция
--		_Fld5894	 - Дата начала замера в миллисекундах
--		_Fld5895	 - Номер сеанса 1С
--		_Fld5896	 - Дата записи (начало часа)
--		_Fld5897	 - Время выполнения
--		_Fld5902	 - Имя пользователя

SET NOCOUNT ON;

DECLARE 
	@sourceDatabase SYSNAME = 'bsl',
	@fileToUpload_KeyOperations nvarchar(max) = 'D:SQLExchangeKeyOperations.dat',
	@fileToUpload_PerformanceMeasurements nvarchar(max) = 'D:SQLExchangePerformanceMeasurements.dat',
	@bcpErrorLog nvarchar(max) = 'D:SQLExchangeErrorLog.txt',
	@sqlServerInstance nvarchar(max) = 'localhost',
	@sqlLoginName nvarchar(max) = '<Логни>',
	@sqlLoginPassword nvarchar(max) = '<Пароль>',
	@cmdKeyOperationUpload varchar(8000),
	@cmdKeyOperationLoad varchar(8000),
	@cmdPerformanceMeasurementsUpload varchar(8000),
	@cmdPerformanceMeasurementsLoad varchar(8000);

-- Этап №1: Синхронизируем ключевые операции

-- Формируем команды вызова утилиты BCP и передаем ее на выполнение в 'xp_cmdshell'
SET @cmdKeyOperationUpload = 
	'bcp ' +
	'"SELECT _IDRRef, _Description, _Fld4266 FROM [' + @sourceDatabase + '].[dbo].[_Reference2598]" ' +
	 'queryout ' + @fileToUpload_KeyOperations + ' -T -c ' +
	 '-e "' + @bcpErrorLog + '" ' +
	 '-U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmdKeyOperationUpload, no_output

-- Подготоваливаем временную таблицу для последующей обработки данных
-- Таблица выступает в качестве буфера
IF OBJECT_ID('tempdb..##tt_KeyOperations') IS NOT NULL
	DROP TABLE ##tt_KeyOperations;
CREATE TABLE ##tt_KeyOperations
(
	[ID] [binary](16) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Description] [nvarchar](max) NULL,
)

-- Загружаем данные во временную таблицу для последующей обработки
-- с помощью утилиты BCP. Данные из временной таблицы будут использоваться
-- для постобработки
SET @cmdKeyOperationLoad = 
	'bcp ##tt_KeyOperations IN "' + @fileToUpload_KeyOperations + '" ' + 
	'-T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmdKeyOperationLoad, no_output

-- Обновляем данные в таблице [KeyOperations] с помощью операции MERGE
-- по следующей логике
--	1. Если ключевая операция по ID найдена и есть изменения полей,
--		то обновляем запись
--	2. Если ключевая операция по ID НЕ найдена, то просто добавляем
--		недостающую запись
MERGE [dbo].[KeyOperations] AS target
	USING (SELECT [ID], [Name], [Description] FROM ##tt_KeyOperations) AS source ([ID], [Name], [Description])   
	ON (target.[ID] = source.[ID])  
	WHEN MATCHED AND NOT target.[Name] = source.[Name] THEN
		UPDATE SET [Name] = source.[Name]
				  ,[Description] = source.[Description]		
	WHEN NOT MATCHED THEN  
		INSERT ([ID], [Name], [Description])  
		VALUES (source.[ID], source.[Name], source.[Description]);  

-- Удаляем временную таблицу
IF OBJECT_ID('tempdb..##tt_KeyOperations') IS NOT NULL
	DROP TABLE ##tt_KeyOperations;

/*
Эту синхронизацию следует запускать перед выгрузкой замеров времени, чтобы список ключевых операций во внешней базе всегда был актуальный на момент выгрузки замеров.
*/

-- Этап №2: Выгружаем порцию замеров 

-- Подготавливаем и выполняем команду выгрузки порции замеров времени с помощью утилиты BCP
SET @cmdPerformanceMeasurementsUpload = 
	'bcp ' +
	'"SELECT TOP 10000 _Fld5893RRef, _Fld5894, _Fld5895, _Fld5896, _Fld5897, _Fld5902 FROM [' + @sourceDatabase + '].[dbo].[_InfoRg5892] ORDER BY _Fld5894" ' +
	 'queryout ' + @fileToUpload_PerformanceMeasurements + ' -T -c ' +
	 '-e "' + @bcpErrorLog + '" ' +
	 '-U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmdPerformanceMeasurementsUpload, no_output;

-- Подготавливаем временную таблицу для сохранения промежуточных данных
-- с целью постобработки
IF OBJECT_ID('tempdb..##tt_PerformanceMeasurements') IS NOT NULL
	DROP TABLE ##tt_PerformanceMeasurements;
CREATE TABLE ##tt_PerformanceMeasurements
(
	[KeyOperation] [binary](16) NOT NULL,
	[BeginDate] [numeric](15, 0) NOT NULL,
	[SessionID] [numeric](15, 0) NOT NULL,
	[PeriodHour] [datetime2](7) NOT NULL,
	[Duration] [numeric](15, 3) NOT NULL,
	[UserName] [nvarchar](150) NOT NULL,
)

-- Загружаем данные во временную таблицу для последующего использования
SET @cmdPerformanceMeasurementsLoad = 
	'bcp ##tt_PerformanceMeasurements IN "' + @fileToUpload_PerformanceMeasurements + '" ' + 
	'-e "' + @bcpErrorLog + '" ' +
	'-T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmdPerformanceMeasurementsLoad, no_output;

-- Данные из временной таблицы добавляем в таблицу "PerformanceMeasurements" с помощью операции MERGE
-- по следующей логике:
--	1. Если запись не найдена, то просто добавляем ее в таблицу
--	2. Если запись по основным измерениям уже существует, то пропускаем загрузку
MERGE [dbo].[PerformanceMeasurements] AS target
	USING (SELECT [KeyOperation], [BeginDate], [SessionID], [PeriodHour], [Duration], [UserName] FROM ##tt_PerformanceMeasurements) 
		AS source ([KeyOperation], [BeginDate], [SessionID], [PeriodHour], [Duration], [UserName])   
	ON (target.[KeyOperation] = source.[KeyOperation]
		AND target.[BeginDate] = source.[BeginDate]
		AND target.[SessionID] = source.[SessionID]
		AND target.[PeriodHour] = source.[PeriodHour])
	WHEN NOT MATCHED THEN  
		INSERT ([KeyOperation], [BeginDate], [SessionID], [PeriodHour], [Duration], [UserName])  
		VALUES (source.[KeyOperation], source.[BeginDate], source.[SessionID], source.[PeriodHour], source.[Duration], source.[UserName]);  

-- Удаляем ранее выгруженные данные из исходной таблицы
DELETE FROM [bsl].[dbo].[_InfoRg5892]
WHERE EXISTS (
	SELECT * FROM ##tt_PerformanceMeasurements tb_tmp
	WHERE [_Fld5893RRef] = tb_tmp.[KeyOperation]
		AND [_Fld5894] = tb_tmp.[BeginDate]
		AND [_Fld5895] = tb_tmp.[SessionID]
		AND [_Fld5896] = tb_tmp.[PeriodHour]
)

-- Удаляем временную таблицу
IF OBJECT_ID('tempdb..##tt_PerformanceMeasurements') IS NOT NULL
	DROP TABLE ##tt_PerformanceMeasurements;

/*
На 2 этапе выполняется выгрузка замеров времени порцией в 10000  записей. На практике этот этап можно запускать с некоторой периодичностью.
*/

В комментариях дано описание основных этапов. Для выгрузки используется штатная для SQL Server утилита BCP, предназначенная для решения задач импорта и экспорта данных. Подробнее о BCP ( Bulk Copy Program) можно узнать здесь и вот здесь. Основное, что нужно сейчас понять, что BCP позволяет выгружать и загружать данные достаточно быстрым способом. Одним из главных предназначений BCP является перенос данных между отдельными серверами. У нас же простой пример, мы используем BCP в рамках одного сервера, поэтому подобный подход может показаться излишним.

В реальности же, рабочая база находится на одном сервере, а база с логами на другом. В этом случае использование BCP будет полностью оправдано для быстрой и эффективной передачи данных.

По умолчанию возможность использования `xp_cmdshell` ограничена параметрами инстанса SQL Server. Для включения этой фичи необходимо выполнить такой скрипт.

-- Включаем возможность изменения дополнительный настроек
EXEC sp_configure 'show advanced options', 1
GO
-- Применяем сделанные изменения
RECONFIGURE
GO
-- Включаем возможность использования команд
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Применяем сделанные изменения
RECONFIGURE
GO

Это требует дополнительных мер безопасности, чтобы злоумышленники не смогли использовать командную оболочку в SQL Server в корыстных целях. Подробнее читать здесь.

Вместо BCP можно сделать выгрузку, например, в CSV средствами 1С, но в этом случае пришлось бы позаботиться о проверках при передаче данных файла во внешнюю базу на случаи сбоев, чтобы не удалить в исходной базе еще не переданную информацию. Также есть такие утилиты как SQLCMD, которая также позволяет делать выгрузки данных.

Этот пример должен был показать следующее:

• SQL Server имеет инструмент для быстрой и эффективной выгрузки / загрузки данных как в рамках одного сервера, так и между различными серверами и инстансами.
• BCP относительно прост в использовании, но требует понимания работы SQL Server и требований безопасности окружения.
• Также BCP может использоваться не только для загрузки и выгрузки, но и для подготовки данных и к последующей обработке, как это было сделано с помощью временных таблиц и операции MERGE.

Основные недостатки:

• Оторванность от логики приложения на уровне платформы и конфигурации.
• Новое звено в цепочке передачи данных, а именно файл с выгрузкой данных.

Задачи выгрузки данных из базы 1С сторонними инструментами встречаются не так часто и обычно носят разовый характер, но бывают и исключения.

Обновление ФИАС во множестве баз

Еще один нетривиальный пример - это обновление ФИАС в большом количестве баз. Все знают, что полностью этот классификатор занимает большой объем данных в базе, а его обновление иногда может занимать достаточно много времени. Если у Вас таких баз много и актуальность классификатора адресов важна, то можно прибегнуть к актуализации его таблиц средствами SQL Server.

Обновлять ФИАС в базах 1С необходимо штатными средствами! Это лишь пример и он не является заменой стандартного функционала БСП. Подходит он только для особых случаев.

И так, у нас есть десятки баз, в которых используется подсистема БСП “Адресный классификатор”. Обычно весь классификатор хранится в нескольких регистрах сведений, который частично меняется от одной версии БСП к другой. Например, структура регистров может быть следующая.

В случае, если версия БСП или, на крайний случай, подсистемы “Адресный классификатор” между базами одинаковая, то у нас есть возможность обновить его средствами SQL Server. Точнее, в одной базе мы обновляем его стандартными средствами БСП, а в другие базы уже переносим хардкорным способом.

Делается это следующим образом:

• Из базы, где уже обновили классификатор средствами БСП, выгружаем все перечисленные выше таблицы с помощью BCP.
• Далее для каждой базы, в которой устаревший классификатор:
  • Очищаем существующие таблицы (DELETE для надежности, или TRUNCATE TABLE для быстрых и безбашенных разработчиков)
  • Загружаем данные в каждую таблицу с помощью BCP.

.к. версии БСП / подсистемы “Адресный классификатор” в базах одинаковые, то и структура регистров сведений тоже будет одинаковая. В этом случае загрузка данных должна выполниться корректно. Вот так будут выглядеть команды выгрузки и загрузки.

Внимание, впереди хардкод и хардкор одновременно! Это только для примера. В идеале можно вынести алгоритм выгрузки таблицы в хранимую процедуру и вызывать ее, но тогда это усложнит пример.

DECLARE 
	@uploadPath nvarchar(max) = 'D:\SQLExchange\',
	@sqlServerInstance nvarchar(max) = 'localhost',
	@sqlLoginName nvarchar(max) = '<Логин>',
	@sqlLoginPassword nvarchar(max) = '<Пароль>',
	@cmd varchar(8000);

SET @cmd = 'bcp [bsl].[dbo]._InfoRg4627 out ' + @uploadPath + '_InfoRg4627.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4648 out ' + @uploadPath + '_InfoRg4648.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4653 out ' + @uploadPath + '_InfoRg4653.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4662 out ' + @uploadPath + '_InfoRg4662.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4683 out ' + @uploadPath + '_InfoRg4683.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4707 out ' + @uploadPath + '_InfoRg4707.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4714 out ' + @uploadPath + '_InfoRg4714.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4735 out ' + @uploadPath + '_InfoRg4735.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl].[dbo]._InfoRg4740 out ' + @uploadPath + '_InfoRg4740.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output

Главное, уловить суть :)

Когда выгрузка готова, можно загружать данные уже в другую базу.

Почти ничего нового по сравнению с предыдущим кодом. Ключевое слово “OUT” заменили на “IN”, а также поменяли имя базы приемника загрузки - вместо “bsl” указали “bsl_other”. Ну и, конечно же, добавили очистку данных от существующих записей. Все остальное оказалось без изменений.

DECLARE 
	@uploadPath nvarchar(max) = 'D:SQLExchange',
	@sqlServerInstance nvarchar(max) = 'localhost',
	@sqlLoginName nvarchar(max) = '<Логин>',
	@sqlLoginPassword nvarchar(max) = '<Пароль>',
	@cmd varchar(8000);

-- Устанавливаем контекст базы, куда должны загрузиться
-- данные
USE [bsl_other];

-- Предварительно очищаем таблицы от всех данных
TRUNCATE TABLE [dbo].[_InfoRg4627];
TRUNCATE TABLE [dbo].[_InfoRg4648];
TRUNCATE TABLE [dbo].[_InfoRg4653];
TRUNCATE TABLE [dbo].[_InfoRg4662];
TRUNCATE TABLE [dbo].[_InfoRg4683];
TRUNCATE TABLE [dbo].[_InfoRg4707];
TRUNCATE TABLE [dbo].[_InfoRg4714];
TRUNCATE TABLE [dbo].[_InfoRg4735];
TRUNCATE TABLE [dbo].[_InfoRg4740];

-- И выполняем загрузку из ранее созданных файлов выгрузки
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4627] IN ' + @uploadPath + '_InfoRg4627.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4648] IN ' + @uploadPath + '_InfoRg4648.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4653] IN ' + @uploadPath + '_InfoRg4653.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4662] IN ' + @uploadPath + '_InfoRg4662.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4683] IN ' + @uploadPath + '_InfoRg4683.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4707] IN ' + @uploadPath + '_InfoRg4707.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4714] IN ' + @uploadPath + '_InfoRg4714.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4735] IN ' + @uploadPath + '_InfoRg4735.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output
SET @cmd = 'bcp [bsl_other].[dbo].[_InfoRg4740] IN ' + @uploadPath + '_InfoRg4740.dat -T -c -U' + @sqlLoginName + ' -P' + @sqlLoginPassword + ' -S' + @sqlServerInstance;
EXEC master..xp_cmdshell @cmd, no_output

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

Пример чем-то похож на предыдущий, где мы передавали замеры времени ключевых операций. Но здесь мы делали выгрузку всех данных таблицы без указания запросов. Сам по себе этот пример проще.

Выгрузка данных во внешнюю базу

Самой распространенной задачей, где в мире “1С” можно встретить BULK-операции, является выгрузка данных во внешнее хранилище. Цели могут быть разные: создание отдельной базы для отчетов; выгрузка информации для стороннего приложения (интеграция); просто архивирование данных и многое другое.

Чаще всего при разработке в такой ситуации используют либо старый добрый ADODB, либо штатные внешние источники данных. Рассмотрим небольшие примеры для обоих вариантов.

ADO наше все

Об ADO и его использовании написано очень много. Он позволяет выполнять работу с другими источниками данных и поддерживает самых разных поставщиков - SQL Server, PostgreSQL, Excel, Access и многое другое. Сейчас продемонстрируем простой прием выгрузки данных средствами 1С и последующую загрузку в таблицу выражением BULK INSERT через ADO. Для выгрузки будем использовать регистр сведений “Замеры времени”, как это делали выше с помощью утилиты BCP. И так, по порядку.

КаталогВыгрузки = "F:\SQLExchange\";
ФайлВыгрузкиЗамеров = КаталогВыгрузки + "PerformanceMeasurements.csv";

Этап №1: Формируем файл CSV

В выгрузке данных в файл CSV нет ничего особенного. Просто выполняем запрос и последовательно записывает данные в файл через класс “ЗаписьТекста”, разбивая значениями точкой с запятой. Порция для выгрузки всего 1000 записей.

Запрос = Новый Запрос;
Запрос.Текст =
	"ВЫБРАТЬ ПЕРВЫЕ 1000
	|	ЗамерыВремени.КлючеваяОперация КАК KeyOperation,
	|	ЗамерыВремени.ДатаНачалаЗамера КАК BeginDate,
	|	ЗамерыВремени.НомерСеанса КАК SessionID,
	|	ЗамерыВремени.ДатаЗаписиНачалоЧаса КАК PeriodHour,
	|	ЗамерыВремени.ВремяВыполнения КАК Duration,
	|	ЗамерыВремени.Пользователь КАК UserName
	|ИЗ
	|	РегистрСведений.ЗамерыВремени КАК ЗамерыВремени";
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();

ЗаписьТекста = Новый ЗаписьТекста(ФайлВыгрузкиЗамеров, КодировкаТекста.ANSI);

Пока Выборка.Следующий() Цикл
	
	ИдентификаторSQL = КонвертироватьВИдентификаторSQL(Выборка.KeyOperation.УникальныйИдентификатор());
	ЗаписьТекста.Записать(ИдентификаторSQL);
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(XMLСтрока(Выборка.BeginDate));
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(XMLСтрока(Выборка.SessionID));
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(Формат(Выборка.PeriodHour, "ДФ='yyyy-MM-dd hh:mm:ss.000'"));
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(XMLСтрока(Выборка.Duration));
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(Выборка.UserName);
	ЗаписьТекста.Записать(";");
	ЗаписьТекста.Записать(Символы.ПС);
	
КонецЦикла;

ЗаписьТекста.Закрыть();

По завершению выгрузки имеем сформированный файл, который можно загрузить с помощью BULK INSERT.

Для того, чтобы выгружать уникальные идентификаторы ссылочных типов в том же виде, в котором они хранятся на стороне SQL Server, используется такая функция.

Функция КонвертироватьВИдентификаторSQL(УникальныйИдентификатор) Экспорт
	
	ИдентификаторСтрокой = Строка(УникальныйИдентификатор);
	ЧастиИдентификатора = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(ИдентификаторСтрокой, "-", Истина);
	
	МассивЧастейИдентификатора = Новый Массив;
	МассивЧастейИдентификатора.Добавить("0x");
	МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[3]));
	МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[4]));
	МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[2]));
	МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[1]));
	МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[0]));
	
	Возврат СтрСоединить(МассивЧастейИдентификатора);
	
КонецФункции

Далее необходимо загрузить данные файла в таблицу.

Этап №2: Загружаем данные через BULK INSERT

Для загрузки используется скрипт примерно такого содержания:

-- Указываем таблицу приемника загрузки
BULK INSERT [dbo].[PerformanceMeasurements]
-- Путь к файлу с загружаемыми данными (В нашем случае CSV)
FROM '<путь к файлу с данными>'
WITH (FIRSTROW = 1, -- с какой строки начинается загрузка
	  MAXERRORS = 0, -- количество допустимых ошибок
	  CODEPAGE = '1251', -- кодировка файла с данными
	  FIELDTERMINATOR = ';') -- разделитель значений в строках

Ранее мы уже загружали данные замеров с помощью утилиты BCP, но если мы попытаемся сделать то же самое с помощью скрипта выше, то получим ошибку преобразования данных для столбца “KeyOperaiton”. Все дело в том, что инструкция BULK INSERT не умеет загружать двоичные данные, а идентификатор ключевой операции как-раз хранятся с таким типом на стороне базы данных.

Чтобы пример все же был рабочим, я просто взял и изменил тип колонки с “varbinary(16)” на “varchar(34)”. В этом случае загрузка происходит без проблем. Таким образом, сразу можно зафиксировать, что инструкция BULK INSERT не всегда может подойти для загрузки, особенно в контексте платформы 1С, где ссылки и некоторые другие поля хранятся в виде “varbinary”. Но для выгрузки данных во внешнюю базу этот подход может оказаться самым эффективным.

Обратите внимание, что с помощью параметра “CODEPAGE” можно явно указать кодировку файла с данными. Это может быть важно, если в нем есть текстовые данные.

Вот так выглядит код загрузки данных из CSV в таблицу базы с помощью BULK INSERT:

СтрокаСоединения = "driver={SQL Server};Persist Security Info=False;"
	+ "User ID=<Логин>;Password=<Пароль>;"
	+ "Initial Catalog=<БазаДанных>;Server=<ИмяИнстансаСУБД>";

Connection = Новый COMОбъект("ADODB.Connection");
Connection.ConnectionTimeOut = 0;
Connection.CommandTimeOut = 0;
Connection.CursorLocation = 3;
Connection.ConnectionString = СокрЛП(СтрокаСоединения);
Connection.IsolationLevel = 1048576;
Connection.Open();

ТекстКоманды = "
	|BULK INSERT [dbo].[PerformanceMeasurements]
	|FROM '" + ФайлВыгрузкиЗамеров + "'
	|WITH (FIRSTROW = 1,
	|	  CODEPAGE = '1251',
	|	  FIELDTERMINATOR = ';'); 	
	|";

Command = Новый COMОбъект("ADODB.Command");
Command.CommandTimeout = 100;
Command.ActiveConnection = Connection;
Command.CommandText = ТекстКоманды;
Command.Execute();

После этого в таблице “[dbo].[PerformanceMeasurements]” появятся записи, которые мы ранее выгрузили в CSV. Остался последний шаг - удалить выгруженные записи из исходной базы.

Этап №3: Удаляем выгруженные данные из базы 1С

Для удаления уже выгруженных данных достаточно заново обойти выборку запроса и записать пустые наборы данных в разрезе измерений выгруженных записей.

Выборка.Сбросить();
Пока Выборка.Следующий() Цикл
	
	Набор = РегистрыСведений.ЗамерыВремени.СоздатьНаборЗаписей();
	Набор.Отбор.КлючеваяОперация.Установить(Выборка.KeyOperation);
	Набор.Отбор.ДатаНачалаЗамера.Установить(Выборка.BeginDate);
	Набор.Отбор.НомерСеанса.Установить(Выборка.SessionID);
	Набор.Отбор.ДатаЗаписиНачалоЧаса.Установить(Выборка.PeriodHour);
	Набор.Записать();
	
КонецЦикла;

Дело сделано, замеры времени теперь во внешней базе!

Теперь вы знаете как выгружать из базы 1С CSV-файлы с последующей их загрузкой во внешнюю базу с помощью BULK INSERT. Также мы ознакомились с некоторыми ограничениями этой инструкции.

Внешние источники данных и боль

С некоторых пор считается, что использовать внешние источники данных - это путь истинного разработчика 1С, а ADO уже устарел и никому не нужен. Раз такое дело, то сделаем пример с использованием этого механизма платформы. Заодно прочувствуем некоторую боль от его использования.

Выгрузку сделаем как и в прошлом примере в файл CSV.

// Выгрузка аналогична предыдущему примеру!!!

                Запрос = Новый Запрос;
                Запрос.Текст =
                    "ВЫБРАТЬ ПЕРВЫЕ 1000
                    |	ЗамерыВремени.КлючеваяОперация КАК KeyOperation,
                    |	ЗамерыВремени.ДатаНачалаЗамера КАК BeginDate,
                    |	ЗамерыВремени.НомерСеанса КАК SessionID,
                    |	ЗамерыВремени.ДатаЗаписиНачалоЧаса КАК PeriodHour,
                    |	ЗамерыВремени.ВремяВыполнения КАК Duration,
                    |	ЗамерыВремени.Пользователь КАК UserName
                    |ИЗ
                    |	РегистрСведений.ЗамерыВремени КАК ЗамерыВремени";
                РезультатЗапроса = Запрос.Выполнить();
                Выборка = РезультатЗапроса.Выбрать();
                
                ЗаписьТекста = Новый ЗаписьТекста(ФайлВыгрузкиЗамеров, КодировкаТекста.ANSI);
                
                Пока Выборка.Следующий() Цикл
                    
                    ИдентификаторSQL = КонвертироватьВИдентификаторSQL(Выборка.KeyOperation.УникальныйИдентификатор());
                    ЗаписьТекста.Записать(ИдентификаторSQL);
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(XMLСтрока(Выборка.BeginDate));
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(XMLСтрока(Выборка.SessionID));
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(Формат(Выборка.PeriodHour, "ДФ='yyyy-MM-dd hh:mm:ss.000'"));
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(XMLСтрока(Выборка.Duration));
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(Выборка.UserName);
                    ЗаписьТекста.Записать(";");
                    ЗаписьТекста.Записать(Символы.ПС);
                    
                КонецЦикла;
                
                ЗаписьТекста.Закрыть();

Загрузку же сделаем через вызов хранимой процедуры на стороне внешней базы. Вот “внутренности” этой процедуры:

CREATE PROCEDURE LoadPerformanceMeasurement
	@FileCSV nvarchar(max),
	@Success int = null OUTPUT
AS
BEGIN
	DECLARE @cmd nvarchar(max) =
	'BULK INSERT [dbo].[PerformanceMeasurements] '
	+ 'FROM ''' + @FileCSV + ''''
	+ 'WITH (FIRSTROW = 1,'
	+ '	  MAXERRORS = 0,'
	+ '	  CODEPAGE = ''1251'','
	+ '	  FIELDTERMINATOR = '';'')';
	
	BEGIN TRY
	 EXEC sp_executesql @cmd;
	 SET @Success = 1;
	END TRY
	BEGIN CATCH
	 SET @Success = 0;	
	END CATCH
END
GO

В процедуре формируется конструкция BULK INSERT по переданному параметру пути к файлу данных CSV. Далее выполняется попытка выполнения команды. Если все прошло без ошибок, то в переменную @Success устанавливается значение 1. Если были ошибки, то устанавливается значение 0. Параметр @Success является выходным (ключевое слово OUTPUT), то есть процедура предоставляет значение параметра вызывающему коду. Теперь перейдем непосредственно к внешнему источнику данных. В конфигурации добавили в источник данных новую хранимую процедуру.

Для вызова используем следующий код.

ВнешниеИсточникиДанных.ПримерРаботыСВнешнимИсточникомДанных
	.LoadPerformanceMeasurement("D:\SQLExchange\PerformanceMeasurements.csv");

Это даже работает! Вроде все в порядке, в чем же проблема? Все дело в том, что внешние источники данных налагают некоторые ограничения на работу с базой данных. Например:

• Нельзя использовать Внешние источники данных?выходные параметры с ключевым словом OUTPUT. Выше в процедуре мы сделали такой параметр, через внешний источник мы его не сможем использовать! Давным-давно вопрос по этому поводу поднимался на ИС, вот тут. Судя по количеству ответов, проблема так и не решена нормальным образом.
• Нет возможности получить результат из хранимой процедуры. Речь идет не только о параметрах OUTPUT, но и о возвращаемых наборах данных. Если хранимая процедура возвращает 1 или более наборов данных, то мы просто не сможем их прочитать.
• Не все типы параметров можно использовать при вызове хранимых процедур и функций.
• Нет возможности создавать полностью кастомные SQL-скрипты во внешнем источнике данных со сложной логикой. Все равно придется обернуть их или в хранимую процедуру, или отказаться в пользу других решений.

Это не совсем относится к теме статьи, поэтому подробнее останавливаться не буду. Рекомендую для работы с базой данных использовать ADO, в том числе и при работе с BULK-операциями, т.к. избавитесь от множества ограничений при разработке и других проблем эксплуатации. А если внешнюю базу данных дорабатывает профессиональный разработчик, то ADO для Вас единственный вариант. Попробуйте убедить разработчика не использовать параметры вывода (OUTPUT).

Восстановление отдельной таблицы на тестовом стенде

Часто экспериментируете с данными на тестовой базе? Запустили обработку, но уже поняли, что допустили ошибку? Хотите восстановить бэкап базы и попробовать заново? Стойте!

Есть способ, который сэкономит Вам много времени! Достаточно сделать бэкап всей таблицы или ее части с помощью BCP в файл. Если что-то пойдет не так, то Вам достаточно восстановить эти данные, а остальную базу не трогать. Согласитесь, это же намного быстрее!

Подробнее останавливаться на этом кейсе не будем, т.к. команды выгрузки и загрузки будут аналогичными первому примеру. Если нужны примеры работы с BCP, то добро пожаловать.

А как же PostgreSQL

PG хорошая СУБД, которая также имеет инструменты для быстрой работы с массивами данных. Аналогично SQL Server, основными рекомендациями к ускорению операции вставки данных будут:

• Отключение индексов на время загрузки
• Отключение ограничений также на время операции
• Использование COPY, которая в какой-то мере является аналогом инструкции “BULK INSERT” в SQL Server.

Вот тут отличное руководство по ускорению вставки данных и некоторым другим оптимизациям.

Удачи!

Мы рассмотрели несколько примеров применения BULK-операций, используя как BULK INSERT для вставки данных, так и операцию MERGE для обновления уже существующих данных. В контексте SQL Server для этих целей используется утилита BCP, которая выгружает данные в свой собственный формат и имеет обширные возможности по настройке выгрузки, форматирования и оптимизации. Также с помощью BCP можно выполнять загрузку. Кроме этого SQL Server поддерживает оператор BULK INSERT в SQL-скриптах, что также было продемонстрировано.

Касательно PostgreSQL стоит отметить, что подобные возможности у него также присутствуют в виде инструкции COPY, которая позволяет выполнять выгрузку и загрузку данных и имеет большое количество параметров.

Публикация не является подробной инструкцией и не содержит всеобъемлющей информации. Главная цель была создать обзор и показать основы, а дальше уже только мануалы, мануалы, мануалы.

Не нашли для себя применения BULK-операций? Значит, время еще не пришло и возможности платформы Вас полностью устраивают. И это хорошо.

На практике, особенно в “кровавом энтерпрайзе”, возникают задачи, которые решить стандартными средствами уже не получается. Для оптимизации массовой модификации данных есть даже сторонние решения корпоративного уровня, которые позволяют оптимизировать множественные операции INSERT, которые генерирует платформа, в единую операцию BULK INSERT. Не буду заниматься рекламой, скажу лишь что решения эти очень эффективные и не дешевые (не для малого бизнеса точно).

Спасибо, что дочитали до конца! :)