Быстрее чем INSERT! BULK-операции и примеры использования

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

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

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

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

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

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

1. Нужно загрузить несколько миллионов записей в регистр сведений. Как это обычно делают? Правильно — записывают эти миллионы записей через набор регистров сведений, устанавливая необходимые отборы. На уровне СУБД это миллионы операций INSERT. Оптимально ли это?
2. Есть внешняя база данных и нужно туда делать массовую выгрузку за предыдущий месяц после закрытия периода. Вроде все просто. Но 1Сный путь в этом случае обычно принимает один из возможных вариантов (но не обязательно):
   • Разработчик делает запрос на стороне 1С и выгружает во внешнюю базу с помощью отдельных операций INSERT через ADO. Думаете это быстро на больших массивах данных?
   • Делает то же самое, что и в предыдущем пункте, через внешние источники данных. В них вызывается хранимая процедура, которая фактически делает отдельные INSERT’ы. Но разработчик утверждает, что это быстрее чем работа с ADO. Улучшит ли это ситуацию?
3. Обмен данными между базами 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-операции в прикладных решениях, таких возможностей просто не предоставляется. Но есть обходные пути, с которыми Вы познакомитесь ниже.

Поскольку платформа 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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Из базы, где уже обновили классификатор средствами БСП, выгружаем все перечисленные выше таблицы с помощью BCP.
2. Далее для каждой базы, в которой устаревший классификатор:
   • Очищаем существующие таблицы (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

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

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";

 Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ ПЕРВЫЕ 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);
ЗаписьТекста.Записать(";");
ЗаписьТекста.Записать(Символы.ПС);

КонецЦикла;

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

Функция КонвертироватьВИдентификаторSQL(УникальныйИдентификатор) Экспорт

ИдентификаторСтрокой = Строка(УникальныйИдентификатор);
ЧастиИдентификатора = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(ИдентификаторСтрокой, "-", Истина);

МассивЧастейИдентификатора = Новый Массив;
МассивЧастейИдентификатора.Добавить("0x");
МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[3]));
МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[4]));
МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[2]));
МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[1]));
МассивЧастейИдентификатора.Добавить(ВРег(ЧастиИдентификатора[0]));

Возврат СтрСоединить(МассивЧастейИдентификатора);

КонецФункции

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

 СтрокаСоединения = "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();

 Выборка.Сбросить();
Пока Выборка.Следующий() Цикл

Набор = РегистрыСведений.ЗамерыВремени.СоздатьНаборЗаписей();
Набор.Отбор.КлючеваяОперация.Установить(Выборка.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:SQLExchangePerformanceMeasurements.csv");

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

1. Нельзя использовать выходные параметры с ключевым словом OUTPUT. Выше в процедуре мы сделали такой параметр, через внешний источник мы его не сможем использовать! Давным-давно вопрос по этому поводу поднимался на ИС, вот тут. Судя по количеству ответов, проблема так и не решена нормальным образом.
2. Нет возможности получить результат из хранимой процедуры. Речь идет не только о параметрах OUTPUT, но и о возвращаемых наборах данных. Если хранимая процедура возвращает 1 или более наборов данных, то мы просто не сможем их прочитать.
3. Не все типы параметров можно использовать при вызове хранимых процедур и функций.
4. Нет возможности создавать полностью кастомные 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. Не буду заниматься рекламой, скажу лишь что решения эти очень эффективные и не дешевые (не для малого бизнеса точно).

Спасибо, что дочитали до конца! 🙂 Есть вопросы, замечания, троллинг? Добро пожаловать в комментарии!

Другие ссылки

• Нетривиальные подходы в решении всем известных проблем: ускорение «больших» документов в 1С и ускорение поиска по подстроке. Как добиться эффекта в разы?

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

• Импорт данных в таблицу SQL (оптимизация)