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