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

Публикация № 1009357

Администрирование - Оптимизация БД (HighLoad)

SQLServer PostgreSQL bulk insert merge bcp оптимизации база данных copy

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

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

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-операции в прикладных решениях, таких возможностей просто не предоставляется. Но есть обходные пути, с которыми Вы познакомитесь ниже.

 
 Почему в запросе нет информации о загружаемых данных

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

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

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

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

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

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

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

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

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

Таблицы 1С для хранения замеров

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

Внешняя база для хранения лоов

 
 Скрипты создания объектов во внешней базе

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

-- Дано:
--	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:\SQLExchange\KeyOperations.dat',
	@fileToUpload_PerformanceMeasurements nvarchar(max) = 'D:\SQLExchange\PerformanceMeasurements.dat',
	@bcpErrorLog nvarchar(max) = 'D:\SQLExchange\ErrorLog.txt',
	@sqlServerInstance nvarchar(max) = 'localhost',
	@sqlLoginName nvarchar(max) = '<Логни>',
	@sqlLoginPassword nvarchar(max) = '<Пароль>',
	@cmdKeyOperationUpload varchar(8000),
	@cmdKeyOperationLoad varchar(8000),
	@cmdPerformanceMeasurementsUpload varchar(8000),
	@cmdPerformanceMeasurementsLoad varchar(8000);
 
 Этап №1: Синхронизируем ключевые операции
 
 Этап №2: Выгружаем порцию замеров 

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

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

 
 Как разрешить использование 'xp_cmdshell'

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

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

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

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

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

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

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

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

 
 Внимание новичкам!

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

Метаданные Таблица SQL
РегистрСведений.АдресныеОбъекты _InfoRg4627
РегистрСведений.ДомаЗданияСтроения _InfoRg4648
РегистрСведений.ДополнительныеАдресныеСведения _InfoRg4653
РегистрСведений.ЗагруженныеВерсииАдресныхСведений _InfoRg4662
РегистрСведений.ИсторияАдресныхОбъектов _InfoRg4683
РегистрСведений.ОриентирыАдресныхОбъектов _InfoRg4707
РегистрСведений.ПричиныИзмененияАдресныхСведений _InfoRg4714
РегистрСведений.СлужебныеАдресныеСведения _InfoRg4735
РегистрСведений.УровниСокращенийАдресныхСведений _InfoRg4740

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

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

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

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

 
 Выгрузка данных для каждой таблицы

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

 
 Загрузка данных для каждой таблицы

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

Выгрузка данных во внешнюю базуНаконец-то код на 1С!

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

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

ADO наше все

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

	КаталогВыгрузки = "F:\SQLExchange\";
	ФайлВыгрузкиЗамеров = КаталогВыгрузки + "PerformanceMeasurements.csv";
 
 Этап №1: формируем файл CSV
 
 Этап №2: загружаем данные через BULK INSERT
 
 Этап №3: удаляем выгруженные данные из базы 1С

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

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

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

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

 
 Обычная выгрузка в CSV

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

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

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

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

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

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

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

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

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

А как же PostgreSQL

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

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

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

Удачи!

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

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

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

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

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

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

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

 

112

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. acanta 57 09.03.19 22:51 Сейчас в теме
убедить разработчика не использовать параметры вывода (OUTPUT)

Разработчики PG / SQL не используют записи логов для информирования о результатах или из одного внешнего источника данных нельзя переключиться на другой источник, например текстовый файл или таблицу с результатами?
2. YPermitin 3031 09.03.19 23:35 Сейчас в теме
(1) Если правильно понял, то можно конечно. Другое дело что это будет не хорошо, т.к. фактически создает искусственные ограничения для разработчика БД и могут привести к проблемам с производительностью. Те же возвращаемые параметры это частоиспользуемая функция TSQL.

Вообщем, обходные пути есть, но иногда это костыли и кошмары разработчика БД. :)
3. julego 10.03.19 10:55 Сейчас в теме
Было бы здорово ещё увидеть пример кода, возвращающего output-параметр из внешней хранимой процедуры. Возможно?
Aletar; user774630; YPermitin; +3 Ответить
4. YPermitin 3031 10.03.19 11:04 Сейчас в теме
(3) пример с ADO или внешними источниками данных?

В любом случае можно.
5. Aletar 11.03.19 05:55 Сейчас в теме
(4) А как? Я тоже сталкивался с такой проблемой. Если это функция, то все понятно, но как получить output-параметры хранимой процедуры через внешние источники данных?
6. YPermitin 3031 11.03.19 06:22 Сейчас в теме
(5) Есть огромный костыль по использованию глобальной временной таблицы. При вызове хранимки выходные параметры записываются туда. А считать их можно потом отдельно простым селектом. Со всеми вытекающими, но вариант рабочий.

Могу позже сделать пример и выложить ответным комментарием.
julego; Aletar; +2 Ответить
7. Aletar 11.03.19 06:24 Сейчас в теме
23. YPermitin 3031 13.03.19 23:38 Сейчас в теме
(7) лучше поздно, чем никогда :)

Вот ответ на вопрос со всеми примерами. И даже чуть больше.

https://infostart.ru/public/1019947/
10. julego 11.03.19 09:11 Сейчас в теме
(4) С внешними источниками понятно, костыли, но тоже интересно. С ADO должно быть проще и правильнее?
11. YPermitin 3031 11.03.19 09:17 Сейчас в теме
(10) правильнее или нет не знаю, наверное от задачи лучше смотреть. Но проше точно. Вечером постараюсь найти время и сделать пример.

На нем и сами определите что лучше :)
24. YPermitin 3031 14.03.19 10:15 Сейчас в теме
(10) лучше поздно, чем никогда :)

Вот ответ на вопрос со всеми примерами. И даже чуть больше.

https://infostart.ru/public/1019947/
25. julego 16.03.19 06:07 Сейчас в теме
(24)Спасибо! Признательна за такое подробное освещение вопроса.
8. PerlAmutor 35 11.03.19 06:39 Сейчас в теме
Задачу быстрой загрузки данных решал несколько иначе, без INSERTов. Сначала через BULK выгрузил пример таблицы в двоичный (.dat) файл. Разобрал его структуру, воспроизвел формирование двоичных данных скриптом. Дальше подставлял в параметр и все - миллионы строк во временной таблице. Оттуда данные уже перемещал в основную таблицу.
YPermitin; +1 Ответить
9. YPermitin 3031 11.03.19 06:49 Сейчас в теме
(8) интересное решение!

А можно где-нибудь посмотреть пример такой выгрузки? Для интереса.
19. PerlAmutor 35 12.03.19 06:16 Сейчас в теме
(9) К сожалению, нет. Мои изыскания достались компании на которую я работал, после её ликвидации все наработки остались на серверах руководства.
12. Quantum80 11.03.19 09:31 Сейчас в теме
Быстро сформировать большой csv файл на 1С тоже может оказаться не тривиальной задачей.
YPermitin; +1 Ответить
13. YPermitin 3031 11.03.19 09:35 Сейчас в теме
(12) тоже верно.

Средствами 1С это может занять длительное время. Тут вариантов несколько:
1. Делать в несколько потоков выгрузку нескольких файлов.
2. Делать с помощью внешних компонентов.
3. Использовать не 1Сные средства, как в нескольких примерах из статьи.
4. И конечно же не использовать BULK, где он не эффективен.
17. Loklir 11.03.19 16:45 Сейчас в теме
(12)Я использую выгрузку не в csv а в XML, и OPENROWSET на стороне сервера. Как по мне то так гораздо быстрей.
YPermitin; +1 Ответить
18. YPermitin 3031 11.03.19 17:58 Сейчас в теме
(17) не ожидал увидеть упоминание про OPENROWSET в комментариях! Спасибо, что написали!

OPENROWSET это нечто иное, это не только BULK-операции, это еще и возможность удаленное взаимодействия с различными источниками данных, в т.ч. и CSV, XML, XLSX, другими инстансами SQL Server и даже PG и т.д. Все то, для чего существуют поставщики, даже ElasticSearch!

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

Не знаю конкретно Вашу ситуацию, но трудно судить почему это будет работать быстрее чем BULK-операции. Возможно сам этап формирования CSV занимает больше времени. В любом случае, OPENROWSET - это отличный инструмент при работы со многими источниками данных. В статье про него не стал писать, т.к. это вроде как и не совсем BULK, и вроде как если прикрутить его к 1С, то страшно может стать :)
Loklir; acanta; +2 Ответить
14. Darklight 18 11.03.19 09:57 Сейчас в теме

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

Таким образом, сразу можно зафиксировать, что инструкция BULK INSERT не всегда может подойти для загрузки, особенно в контексте платформы 1С, где ссылки и некоторые другие поля хранятся в виде "varbinary".


такое ограничение сводит на нет большинство полезных стратегий применения BULK INSERT между таблицами баз 1С, которые почти всегда имеют UUIDы (и не только), хранящиеся как varbinary.

Но за статью спасибо.
YPermitin; +1 Ответить
15. YPermitin 3031 11.03.19 10:05 Сейчас в теме
(14) здесь надо понимать, что речь про инструкцию TSQL "Bulk Insert".

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

Можно сделать свой алгоритм выгрузки из 1С в формат BCP, как предлагал коллега в комментариях.

Вообщем, это не безвыходная ситуация.
16. Darklight 18 11.03.19 13:13 Сейчас в теме
(15)Ок, спасибо за пояснения
20. geron4 91 12.03.19 08:29 Сейчас в теме
Спасибо! Хороший материал!
Как заметил автор, использование вышеописанных методов подходит для случаев, где они действительно нужны, если есть время и возможность - лучше пользоваться типовыми механизмами 1С, но действительно бывают случаи, когда нужна скорость выполнения, например операция не может закончится в адекватное время.
21. starik-2005 1866 12.03.19 10:31 Сейчас в теме
Чисто за картинку! Потом может прочитаю )))
YPermitin; +1 Ответить
22. YPermitin 3031 12.03.19 10:36 Сейчас в теме
(21) статью готовил 2 часа, из них 1 час на мемы :)
26. rukalico 27.03.19 14:33 Сейчас в теме
Юрий, добрый день!

Почему во всей статье приводится приvер чтения из промежуточного csv файла?
Разве не логичнее писать во внешнюю таблицу напрямую из 1С выборки запроса.
В статье везде тем не менее файл, это наводит на мысль - есть ли здесь какой то нюанс?
YPermitin; +1 Ответить
27. YPermitin 3031 27.03.19 14:40 Сейчас в теме
(26) нюансы есть.

Например, вот вопросы, которые можно себе задать для полного понимания:
1. Что значит писать напрямую из выборки 1С?
2. Что такое выборка данных в 1С и как она реализована?

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

А так, если кратко: напрямую в базу можно фактически только через ADO или внешние источники, но это не оптимально при большом объеме, т.к. реализовано через большоое количество операций INSERT.

Также есть информация. Что выбока данных - это фактически работа с результатом запроса, созраненного в файл (сеансовые данные). Точных экспериментов мало ставил, но уверенность есть.
28. rukalico 27.03.19 14:59 Сейчас в теме
(27) Имеется ввиду Запрос.Выполнить().Выбрать()
То есть если нужно записать во внешнюю таблицу разве для этого нужно делать промежуточную выгрузку в csv? И почему этот способ как основной пример в статье рассматривается.. Вот это я не понял.
29. YPermitin 3031 27.03.19 18:12 Сейчас в теме
(28)
если нужно записать во внешнюю таблицу разве для этого нужно делать промежуточную выгрузку в csv? И почему этот способ как основной пример в статье рассматривается.. Вот это


Если получить данные через выборку, а потом вставлять во внешнюю таблицу отдельными операциями INSERT, то это конечно будет медленнее для больших объемов данных, чем использование BULK INSERT.

BULK INSERT можно делать из внешнего файла либо CSV формата, либо нативного формата SQL Server, который используется утилитой BCP. То есть в статье все же два варианта выгрузки, а не только CSV.
30. nvv1970 06.05.19 12:13 Сейчас в теме
Появился повод вернуться к статье, подискутировать и обсудить пару нюансов...

Есть задача подчистить некоторые данные в некоторой базе. Однако, из-за неуверенности, что заказчик верно сформулировал условия удаления - решил некоторые удаляемые данные сохранить во внешнюю базу. База 0.5Тб, бэкап развернуть тупо негде в случае восстановления каких-то отдельных записей.
Есть проблема: дисковая подсистема дохлая и показывает не более 20-40Мб/с (и это при последовательном доступе, пики до 100Мб/с - единичны, часто деградация до 1-5 Мб/с). А скопипастить нужно минимум 50+Гб, 200+ млн строк (этот же сервер, но диски источник-приемник разные).

В добавок к статье хотелось бы сравнить способы `bulk ins ert`, `select into`, `ins ert in to`.

Например, select into выполняет минимальное логирование и выполняется достаточно быстро. Не нагуглил реальных сравнений с "балком"
Вопрос (select into vs bulk ins ert): на практике какая будет разница прямого копирования и выгрузки+загрузки через файл? Поделитесь результатами тестов.

К слову, `Ins ert in to` тоже может быть таким же быстрым как и `select into` при соблюдении ряда условий (все есть в документации): simple, tablock изменяемой таблицы, отсутствие индексов. Миллионы строк вставляются за секунды.

Однако ВСТАВКА - это НЕ САМОЕ узкое место оказалось в моей задаче!
Таблицы в приемнике делаю сжатыми, но это не повлияло на результат.
Чертовы pageiolatch_sh в базе источнике ((

Пример на основе `sele ct into`:
1. выполняем копирование основной таблицы регистра накопления (10-15 колонок) - копирование 1млн строк выполняется за 10-20 сек, в таблице 30млн строк = 10 Гб, скорость дисков 30-40Мб/с. Ожидания на чтении возникают редко.
2. выполняем копирование регистра версии объектов (наиболее показательный для сравнения пример, но по факту данные конечно же не нужны для резервирования)... 1,5 млн=11Гб... даже 10000 строк начинает здорово задумываться. Стабильные, постоянные и длительные pageiolatch... Чтение еле шевелится. 1-3Мб/с. Так можно дождаться "второго пришествия" или как минимум нового года ((

Первые подозрения: шпиндели и фрагментация. Однако ситуация точно ровно такая же и у других клиентов: версии объектов даже последовательно читаются крайне медленно (кстати, коллеги, а как у вас?). Исключал в тестировании влияние буфера, т.е. сравниваем только физическое чтение.
Я думаю, что все дело в blob-объектах (image) регистра. Вероятно они и провоцируют непоследовательное чтение.
(Как MS ни ругается, что пора прекратить использовать устаревшие blob-типы image и ntext - 1С явно не спешит от них отказываться, хранение var***(max) кажется реализовано иначе, т.е. прямо в страницах с данными... Лучше перечитать BOL, мог что-то забыть и соврать)

Еще один важный вопрос с учетом предыдущей проблемы:
- как при переносе данных реализовать порционность и гарантировать последовательное чтение исходных данных?
Например, основная таблица регистров накопления - это всегда период. Можно по месяцу вычитывать и переносить. Данные по периодам распределены более-менее равномерно, чтение внутри периода - последовательно (при отсутствии фрагментации). Нет необходимости формировать таблицу ключей.
А вот непериодические РС? Делать временную упорядоченную таблицу ключей кластерного индекса (например, только первые поля)? Очень индивидуальное решение для каждого регистра.
Как еще можно реализовать порционность при условии, что исходные данные после каждой порции переноса не удаляются?
31. YPermitin 3031 06.05.19 13:11 Сейчас в теме
(30) спасибо за развернутый комментарий. Постараюсь на него ответить, но позже.

Очень обширные вопросы задали, с телефона пальцы устанут печатать :)
Aleskey_K; +1 Ответить
32. sparhh 24.06.19 10:24 Сейчас в теме
(30) (31)

Так же хочу на похожую тему задать вопрос:
Если нужно во внешнюю базу СГЛ передавать данные.
Это лучше делать через ADO или через механизм Внешних источников 1С?
Правильно ли я понимаю что через ADO можно делать INSERT INTO, то есть пулять сразу несколько строк одним запросом. Тогда как Внешние источники могут делать только построчный INSERT, что может быть не оптимально?
33. nvv1970 24.06.19 10:29 Сейчас в теме
(32) Хммм... Но таблицы внешних источников - это не только объекты, но и наборы.
Никогда не писал в них.
Но запись в таблицу подразумевает не только чистый инсерт. Нужно удаление/вставка по отборам, т.е. реализация вашей логики. А уж логика может вообще не подпадать под логику внешних источников 1С.

PS: могу сказать, что работа с чтением миллиардных таблиц точно проходит без проблем ))
34. sparhh 24.06.19 10:49 Сейчас в теме
(33) В моем случае нужно только писать во внешние таблицы СГЛ.
Вот и задаюсь вопросом - использовать АДО или механизм Внешних источников...
YPermitin; +1 Ответить
35. YPermitin 3031 24.06.19 11:21 Сейчас в теме
(34) я служею простому правилу: Если нет явных причин использовать внешние источники данных, то я использую ADO.

Внешние источники могут создать проблемы как в разработке, так и в сопровождении.
36. sparhh 24.06.19 11:44 Сейчас в теме
(36) А какие могут быть явные причины?

Я сам больше за ADO, и пока ищу причины отказаться от внешних источников..
Потому как с одной стороны это решение из мира 1С для 1С..
37. YPermitin 3031 24.06.19 11:46 Сейчас в теме
(36) если обращаться к данным нужно через запросы к внешним источникам как разработчики 1С привыкли.

Или нужна хоть-какая-то кроссплатформенность.

Но я бы все равно 10 раз подумал :)
38. sparhh 24.06.19 17:52 Сейчас в теме
(37) Все так действительно, забыл что ADO только для виндовс..
А есть ли какая то удобная библиотека для работы с АДО через 1С?
Оставьте свое сообщение