Статья
04/01/2012

Оптимизация производительности инструкции MERGE

В SQL Server 2008 несколько операций языка обработки данных DML можно выполнить, используя одну инструкцию MERGE. Например, может потребоваться синхронизировать две таблицы путем вставки, обновления или удаления строк в одной таблице на основании отличий, найденных в другой таблице. Как правило, это производится путем выполнения хранимой процедуры или пакета, содержащего отдельные инструкции INSERT, UPDATE и DELETE. Однако это означает, что данные и в исходных и в целевых таблицах вычисляются и обрабатываются несколько раз. По крайней мере один раз для каждой инструкции.

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

Рекомендации по использованию соединений

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

Укажите в предложении ON <merge_search_condition> только те условия поиска, которые определяют критерий совпадения данных в исходных и целевых таблицах. То есть необходимо указать только те столбцы целевой таблицы, которые сравниваются с соответствующими столбцами исходной таблицы. Не включайте сравнения с другими значениями, такими как константа.

Чтобы отфильтровать строки от исходных или целевых таблиц, используйте один из следующих методов.

Укажите условие поиска для фильтрации строк в соответствующем предложении WHEN. Например, WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
Определите представление на источнике или цели, возвращающее отфильтрованные строки, и создайте на него ссылку как на исходную или целевую таблицу. Если представление определено на целевой таблице, то все действия, выполняемые с ним, должны удовлетворять условиям для обновления представлений. Дополнительные сведения об обновлении данных с помощью представления см. в разделе Изменение данных через представление.
Используйте предложение WITH <обобщенное табличное выражение>, чтобы отфильтровать строки от исходных или целевых таблиц. Данный метод аналогичен использованию дополнительного критерия поиска в предложении ON и может привести к неверному результату. Рекомендуется либо не использовать этот метод, либо тщательно протестировать его перед реализацией.

Дополнительные сведения см. в разделе Добавление, обновление и удаление данных с помощью инструкции MERGE.

Оптимизация запросов соединений

Операция соединения оптимизируется в инструкции MERGE тем же способом, что и в инструкции SELECT. То есть при обработке соединений в SQL Server оптимизатор запросов выбирает наиболее эффективный метод обработки из нескольких возможных. Дополнительные сведения о соединениях см. в разделах Основные принципы соединения и Дополнительные понятия настройки запросов. Когда источник и цель одного размера и рекомендации по использованию индекса, описанные в предыдущем подразделе «Рекомендации по использованию индекса», применяются к исходным и целевым таблицам, оператор соединение слиянием является наиболее эффективным планом запроса. Это происходит вследствие того, что обе таблицы просматриваются один раз и не требуется сортировки данных. Когда источник меньше целевой таблицы, предпочтительнее использовать оператор вложенные циклы.

Использование определенного соединения можно задать принудительно с помощью предложения OPTION (<query_hint>) в инструкции MERGE. Не рекомендуется использовать хэш-соединение в качестве подсказки в запросах для инструкций MERGE, так как этот тип соединений не использует индексы. Дополнительные сведения о подсказках в запросах см. в разделе Подсказки в запросах (Transact-SQL). Следующий пример указывает соединение вложенных циклов в предложении OPTION.

USE AdventureWorks2008R2;
GO
BEGIN TRAN;
MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty) 
       FROM Sales.SalesOrderDetail AS sod
       JOIN Sales.SalesOrderHeader AS soh
         ON sod.SalesOrderID = soh.SalesOrderID
         AND soh.OrderDate BETWEEN '20030701' AND '20030731'
       GROUP BY ProductID) AS src(ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
    THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
    THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*
OPTION (LOOP JOIN);
GO
ROLLBACK TRAN;

Рекомендации по использованию предложения TOP

В инструкции MERGE предложение TOP указывает количество строк (в абсолютном или процентном выражении), которые оказываются затронутыми при соединении исходной и целевой таблиц и после удаления строк, которые не соответствуют требованиям операций вставки, обновления и удаления. Предложение TOP дополнительно сокращает количество соединенных строк до указанного значения, а затем к оставшимся соединенным строкам применяются операции вставки, обновления или удаления без учета порядка. Иными словами, порядок, в котором строки подвергаются операциям, определенным в предложениях WHEN, не задан. Например, указание значения TOP (10) затрагивает 10 строк. Из них 7 могут быть обновлены и 3 вставлены или 1 может быть удалена, 5 обновлено и 4 вставлено и т. д.

Часто приходится использовать предложение TOP для выполнения операций языка обработки данных DML в большой таблице в пакетах. При использовании для этой цели предложения TOP в инструкции MERGE важно понимать следующие последствия.

Может быть затронута производительность операций ввода-вывода.

Инструкция MERGE выполняет полный просмотр обеих таблиц — исходной и целевой. Разделение операций на пакеты снижает количество операций записи на каждый пакет. Однако каждый пакет выполнит полное сканирование исходной и целевой таблиц. Итоговая операция чтения может затронуть производительность запроса.
Это может привести к неверным результатам.

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

Чтобы гарантировать правильные результаты:
- Используйте предложение ON для определения, какие из исходных строк затрагивают существующие целевые строки, а какие совершенно новые.
- Используйте дополнительное условие в предложении WHEN MATCHED, чтобы определить, не была ли целевая строка уже обновлена предыдущим пакетом.
Так как предложение TOP применяется только после использования этих предложений, каждое выполнение либо вставляет одну совершенно новую строку, либо обновляет существующую. Следующий пример создает исходную и целевую таблицы и представляет правильный метод использования предложения TOP для изменения цели в пакетных операциях.
```
CREATE TABLE dbo.inventory(item_key int NOT NULL PRIMARY KEY, amount int, is_current bit);
GO
CREATE TABLE dbo.net_changes(item_key int NOT NULL PRIMARY KEY, amount int);
GO

MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key
WHEN MATCHED AND inventory.is_current = 0
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) VALUES(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
Следующий пример демонстрирует неверный метод реализации предложения TOP. Проверка столбца is_current указывается в условии соединения с исходной таблицей. Это значит, что исходная строка, используемая в одном пакете, в следующем пакете будет обрабатываться как «несоответствующая», что приведет к нежелательной операции вставки.
```
MERGE TOP(1) dbo.inventory
USING dbo.net_changes
ON inventory.item_key = net_changes.item_key AND inventory.is_current = 0
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```
Следующий пример также демонстрирует неверный метод. При использовании обобщенного табличного выражения (CTE) для ограничения количества считываемых для пакета строк любая исходная строка, совпавшая с целевой строкой и отличающаяся от выбранной предложением TOP(1), обрабатывается как «несоответствующая», что приводит к нежелательной операции вставки. Кроме того, данный метод ограничивает только количество строк, которые можно обновить. Каждый пакет попытается вставить все «несоответствующие» исходные строки.
```
WITH target_batch AS (
  SELECT TOP(1) *
  FROM dbo.inventory
  WHERE is_current = 0
  )
MERGE target_batch
USING dbo.net_changes
ON target_batch.item_key = net_changes.item_key
WHEN MATCHED
  THEN UPDATE SET amount += net_changes.amount, is_current = 1
WHEN NOT MATCHED BY TARGET
  THEN INSERT (item_key, amount, is_current) values(item_key, amount, 1)
OUTPUT deleted.*, $action, inserted.*;
GO
```

Измерение и диагностика производительности инструкции MERGE

Следующие доступные функции помогают производить измерение и диагностику производительности инструкций MERGE.

Используйте счетчик merge stmt в функции динамического управления sys.dm_exec_query_optimizer_info для возвращения числа оптимизаций запросов, произведенных для инструкций MERGE.
Используйте атрибут merge_action_type в функции динамического управления sys.dm_exec_plan_attributes для возвращения типа триггера плана выполнения, используемого в виде результата инструкции MERGE.
Используйте SQL-трассировку для сбора данных диагностики инструкции MERGE тем же способом, что и для других инструкций языка обработки данных DML. Дополнительные сведения см. в разделе Знакомство с SQL Trace.