Производительность слияния SQL Server
Выполнение слияния таблицы с 40 миллионами строк занимает много часов на сервере базы данных со значительной мощностью (64 ГБ памяти, 16 ядер).
Я загружаю данные в промежуточную таблицу через SqlBulkCopy, а затем MERGE в целевую таблицу. MERGE выполняется партиями, чтобы минимизировать воздействие на TempDb.
Суть моего заявления такова:
DECLARE @RowID int = 0,
@RowCount int,
@Batches int = 0,
@BatchSize int = 10000
SELECT @RowCount = COUNT(1)
FROM [someStagingTable]
WHILE @RowID <= @RowCount
BEGIN
MERGE INTO [someTargetTable] AS Target
USING (SELECT * FROM [someStagingTable]
WHERE ID BETWEEN @RowID AND @RowID + @BatchSize - 1) AS Source
ON Target.AccountNumber = Source.AccountNumber
WHEN MATCHED THEN
UPDATE
SET ...
WHEN NOT MATCHED BY TARGET THEN
INSERT ...
SET @RowID = @RowID + @BatchSize
SET @Batches = @Batches + 1
COMMIT
END
По поводу индексации:
[someStagingTable].ID— столбец идентификаторовintс кластеризованным индексом.[someTargetTable].AccountNumberиндексируется
Не видя плана запроса и полных определений таблицы/индекса, на это невозможно ответить.
Ответы 1
В данном конкретном случае, несмотря на наличие очевидного индекса [someTargetTable].AccountNumber, для оператора MERGE требовалась подсказка индекса:
MERGE INTO [someTargetTable] WITH (INDEX=IX_someTargetTableAccountNumber) AS Target
USING (SELECT * FROM [someStagingTable] WHERE ID BETWEEN @RowID AND @RowID + @BatchSize - 1) AS Source
ON Target.AccountNumber = Source.AccountNumber
Это стало очевидным при проверке плана выполнения SQL, который начинался с TABLE SCAN из [someTargetTable] в каждом пакете. Добавление подсказки по индексу сократило время выполнения каждого пакета с ~300 секунд до ~1 секунды. План выполнения меняется с TABLE SCAN на INDEX SEEK, при этом прочитанные строки уменьшаются с 40M до 10K (размер пакета).
Это поиск по индексу, за которым следует поиск? Могло быть и хуже.
Я предлагаю вам проверить, какой ключ кластерного индекса используется для вашей целевой таблицы. Если оно отличается от
AccountNumber, возможно, было бы полезно заранее выполнить поиск, добавить значение кластеризованного индекса в промежуточную таблицу, а затем присоединиться к нему во время слияния. Также может быть полезно назначить промежуточные значенияID, упорядоченные по значению этого кластеризованного индекса. Это может уменьшить количество ссылок на страницы на этапе объединения: ключи, расположенные рядом с объявлением, могут находиться на одной или соседних страницах. Оба метода увеличивают накладные расходы на предварительную обработку, но могут повысить эффективность каждой операции пакетного слияния.