Поделиться через


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

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

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

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

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

  • Создайте индекс в столбцах соединения исходной таблицы, являющийся уникальным и охватывающим.

  • Создайте уникальный кластеризованный индекс в столбцах соединения целевой таблицы.

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

Например, в следующей инструкции MERGE исходная таблица, dbo.Purchases, и целевая таблица, dbo.FactBuyingHabits, соединяются в столбцах ProductID и CustomerID. Чтобы увеличить производительность данной инструкции, необходимо создать уникальный или первичный индекс ключа (кластеризованный или некластеризованный) в столбцах ProductID и CustomerID таблицы dbo.Purchases и кластеризованный индекс в столбцах ProductID и CustomerID таблицы dbo.FactBuyingHabits. Код, используемый для создания этих таблиц, см. в разделе Добавление, обновление и удаление данных с помощью инструкции MERGE.

MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
    VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*; 

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

Для улучшения производительности инструкции 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 AdventureWorks;
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;

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

Если инструкция SELECT, INSERT, UPDATE или DELETE выполняется без параметров, то оптимизатор запросов SQL Server может произвести внутреннюю параметризацию инструкции. Это значит, что все литеральные значения, содержащиеся в запросе, заменяются параметрами. Например, инструкция INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10) может быть реализована внутренне в виде INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2). Данный процесс, называемый простая параметризация, увеличивает возможности реляционного механизма по применению существующих скомпилированных планов выполнения для новых инструкций SQL. Производительность запросов может быть улучшена вследствие снижения частоты компиляций и перекомпиляций запросов. Оптимизатор запросов не применяет процесс простой параметризации к инструкциям MERGE. Поэтому инструкции MERGE, содержащие литеральные значения, могут быть не настолько производительными, как отдельные инструкции INSERT, UPDATE или DELETE, так как при каждом выполнении инструкции MERGE компилируется новый план.

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

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

  • Если инструкцию нельзя параметризовать, создайте структуру плана типа TEMPLATE и укажите в нем подсказку PARAMETERIZATION FORCED в запросе. Дополнительные сведения см. в разделе Указание механизма параметризации запросов с помощью структур плана.

  • Если в базе данных часто выполняются инструкции MERGE, рекомендуется установить параметр базы данных PARAMETERIZATION в значение FORCED. При установке данного параметра проявляйте осторожность. Параметр PARAMETERIZATION является параметром уровня базы данных и влияет на обработку всех запросов к базе данных. Дополнительные сведения см. в разделе Принудительная параметризация.

Рекомендации по использованию предложения 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 может использоваться для осуществления эффективной массовой загрузки данных из исходного файла данных в целевую таблицу путем указания предложения OPENROWSET(BULK…) в качестве исходной таблицы. С помощью этого целый файл обрабатывается в одном пакете.

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

  • Создайте кластеризованный индекс в столбцах соединения целевой таблицы.

  • Чтобы указать способ сортировки файла исходных данных, в предложении OPENROWSET(BULK…) используйте подсказки ORDER и UNIQUE.

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

Данные руководства гарантируют уникальность ключей соединения и совпадение порядка сортировки данных в исходном файле с целевой таблицей. Производительность запросов увеличивается, так как не требуется дополнительных операций сортировки и необязательных копирований данных. Следующий пример использует инструкцию MERGE для массовой загрузки данных из плоского файла StockData.txt в целевую таблицу dbo.Stock. Кластеризованный индекс создается в столбце, использующемся для соединения с исходными данными, путем определения ограничения первичного ключа в StockName целевой таблицы. Подсказки ORDER и UNIQUE применяются к столбцу Stock в источнике данных, который соответствует ключевому столбцу кластеризованного индекса в целевой таблице.

Перед запуском этого примера создайте текстовый файл с именем StockData.txt в папке C:\SQLFiles\. Файл должен содержать два столбца данных, разделенных запятой. Например, используйте следующие данные.

Alpine mountain bike,100

Brake set,22

Cushion,5

Затем создайте файл формата XML с именем BulkloadFormatFile.xml в папке C:\SQLFiles\. Используйте следующие данные.

<?xml version="1.0"?>

<BCPFORMAT xmlns="https://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="5"/>

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="Stock" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="Delta" xsi:type="SQLSMALLINT"/>

</ROW>

</BCPFORMAT>

USE AdventureWorks;
GO
CREATE TABLE dbo.Stock (StockName nvarchar(50) PRIMARY KEY, Qty int CHECK (Qty > 0));
GO
MERGE dbo.Stock AS s
USING OPENROWSET (
    BULK 'C:\SQLFiles\StockData.txt',
    FORMATFILE = 'C:\SQLFiles\BulkloadFormatFile.xml',
    ROWS_PER_BATCH = 15000,
    ORDER (Stock) UNIQUE) AS b
ON s.StockName = b.Stock
WHEN MATCHED AND (Qty + Delta = 0) THEN DELETE
WHEN MATCHED THEN UPDATE SET Qty += Delta
WHEN NOT MATCHED THEN INSERT VALUES (Stock, Delta);
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.