При обслуживании больших баз данных, особенно копий крупных информационных систем на платформе 1С, перед администраторами и разработчиками часто встает задача выборочного переноса или синхронизации данных. Когда таблицы достигают объемов в миллиарды строк и терабайты физического дискового пространства, стандартные методы полной перезаписи баз становятся неэффективными.
Типичный сценарий: необходимо регулярно переносить новые документы или записи регистров за определенный период из основной базы в аналитическую копию. Если выполнять перенос простым запросом INSERT, то при первом же совпадении уникального ключа (например, ссылки на документ или уникального набора измерений регистра накопления) СУБД вернет ошибку нарушения уникальности первичного ключа (Primary Key Violation) и полностью откатит всю транзакцию. В этой статье мы подробно разберем, как реализовать вставку данных в MS SQL Server, игнорируя дубликаты записей и не прерывая выполнение скрипта.
Для исключения дублирования строк на этапе выборки разработчики часто используют левое соединение (LEFT JOIN) с последующей фильтрацией незаполненных значений. Пример такого подхода выглядит следующим образом:
INSERT INTO TargetTable (RowKey, Column1, Column2)
SELECT Source.RowKey, Source.Column1, Source.Column2
FROM SourceTable AS Source
LEFT JOIN TargetTable AS Target ON Target.RowKey = Source.RowKey
WHERE Target.RowKey IS NULL;
Проанализируем эту ситуацию с точки зрения производительности. На таблицах объемом в несколько миллиардов строк операция LEFT JOIN заставит планировщик MS SQL Server строить тяжелые планы выполнения, требующие огромного количества оперативной памяти под сортировку и хэширование (Hash Match). На терабайтных объемах это приведет к критическому падению скорости работы дисковой подсистемы.
Более эффективной и быстрой альтернативой является использование оператора WHERE NOT EXISTS. Рассмотрим этот пример подробнее:
INSERT INTO TargetTable (RowKey, Column1, Column2)
SELECT Source.RowKey, Source.Column1, Source.Column2
FROM SourceTable AS Source
WHERE NOT EXISTS (
SELECT 1
FROM TargetTable AS Target
WHERE Target.RowKey = Source.RowKey
);
Выясним причину превосходства этого метода. Оптимизатор MS SQL Server для конструкции NOT EXISTS чаще всего выбирает физический оператор Left Anti Semi Join. В отличие от полноценного соединения, этот оператор прекращает сканирование индекса целевой таблицы сразу же, как только находит первое совпадение по ключу для проверяемой строки. Это позволяет значительно сэкономить ресурсы процессора и диска.
Оператор MERGE (также известный как "upsert") позволяет объединить операции вставки, обновления и удаления в один запрос. Если наша задача состоит только в добавлении отсутствующих строк без изменения уже существующих, мы можем использовать сокращенный синтаксис MERGE.
Разберем по шагам, как выглядит такой запрос:
MERGE TargetTable AS Target
USING SourceTable AS Source
ON (Target.RowKey = Source.RowKey)
WHEN NOT MATCHED THEN
INSERT (RowKey, Column1, Column2)
VALUES (Source.RowKey, Source.Column1, Source.Column2);
Важные нюансы работы с MERGE на больших таблицах:
MERGE показывает высокую скорость выполнения благодаря однократному чтению метаданных и эффективному сопоставлению. Однако на таблицах с миллиардами строк его план выполнения может оказаться нестабильным.MERGE весьма агрессивен в плане блокировок. Он часто вызывает эскалацию блокировок до уровня всей таблицы, что может привести к взаимным блокировкам (deadlocks) в высоконагруженных системах. При работе в режиме онлайн рекомендуется использовать хинты блокировок, такие как HOLDLOCK.MERGE при сложных условиях или наличии триггеров на целевой таблице. Убедитесь, что на вашем сервере установлены последние пакеты обновлений (Cumulative Updates).Малоизвестным, но наиболее точным решением задачи "игнорирования" ошибок дубликатов на уровне СУБД является использование специальной настройки уникального индекса или первичного ключа — IGNORE_DUP_KEY = ON.
Посмотрим на пример создания такого индекса:
-- Если индекс или ограничение уже создано, его потребуется пересоздать
ALTER TABLE TargetTable
DROP CONSTRAINT PK_TargetTable;
-- Создаем первичный ключ заново с включенной опцией игнорирования дубликатов
ALTER TABLE TargetTable
ADD CONSTRAINT PK_TargetTable PRIMARY KEY (RowKey)
WITH (IGNORE_DUP_KEY = ON);
Как это работает на практике:
После включения этой опции мы можем выполнять самый простой запрос вставки без каких-либо проверок:
INSERT INTO TargetTable (RowKey, Column1, Column2)
SELECT RowKey, Column1, Column2
FROM SourceTable;
Если в процессе вставки MS SQL Server обнаружит строки, ключи которых уже присутствуют в целевой таблице, он не откатит транзакцию и не прервет выполнение скрипта. СУБД просто отбросит дублирующиеся строки, запишет новые уникальные данные, а пользователю вернет информационное предупреждение (Warning) вместо критической ошибки. Это максимально быстрое решение, поскольку оно не требует выполнения дополнительных подзапросов или соединений в коде вставки.
Ограничения метода: пересоздание первичного ключа на таблице в несколько миллиардов строк — крайне ресурсоемкая операция, требующая монопольного доступа к таблице и значительного времени выполнения. Данный метод идеально подходит для обслуживания выделенных аналитических копий баз данных, где структуру таблиц можно подготовить заранее.
Попытка перенести миллиарды строк одним транзакционным блоком гарантированно приведет к переполнению журнала транзакций (файла .ldf), нехватке оперативной памяти и долгому откату изменений в случае сбоя. При работе с терабайтными базами критически важно разбивать процесс на небольшие порции (пакеты).
Рассмотрим пример скрипта пакетной вставки с использованием цикла WHILE и ограничения TOP:
DECLARE @RowsInserted INT = 1;
DECLARE @BatchSize INT = 50000; -- Оптимальный размер пакета подбирается экспериментально
WHILE @RowsInserted > 0
BEGIN
BEGIN TRANSACTION;
INSERT INTO TargetTable (RowKey, Column1, Column2)
SELECT TOP (@BatchSize) Source.RowKey, Source.Column1, Source.Column2
FROM SourceTable AS Source
WHERE NOT EXISTS (
SELECT 1
FROM TargetTable AS Target
WHERE Target.RowKey = Source.RowKey
)
ORDER BY Source.RowKey; -- Гарантирует последовательность обработки
SET @RowsInserted = @@ROWCOUNT;
COMMIT TRANSACTION;
-- Небольшая пауза для очистки логов транзакций и снижения нагрузки на диски
WAITFOR DELAY '00:00:01';
END;
Этот подход минимизирует размер активной транзакции, удерживает размер файла журнала в разумных пределах и позволяет другим процессам бесконфликтно обращаться к таблицам во время технологического окна переноса данных.
Для достижения максимальной скорости вставки больших объемов данных рекомендуется использовать промежуточные (Staging) таблицы. Схема выглядит следующим образом:
StagingTable без индексов и ограничений.bcp, команда BULK INSERT или службы SSIS. Эти инструменты работают в обход стандартного механизма логирования транзакций (минимально протоколируемые операции).StagingTable.StagingTable в TargetTable с помощью изученных ранее конструкций WHERE NOT EXISTS или MERGE локально, внутри одного сервера, что исключает сетевые задержки.Использование любого из предложенных методов зависит от архитектуры вашей системы, доступного времени на синхронизацию данных и ограничений на изменение структуры целевой базы данных.