아티클
09/27/2010

MERGE 문 성능 최적화

SQL Server 2008에서는 MERGE 문을 사용하여 단일 문에서 여러 DML(데이터 조작 언어) 작업을 수행할 수 있습니다. 예를 들어 원본 테이블과의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 및 삭제하여 두 테이블을 동기화해야 하는 경우가 있습니다. 일반적으로 이러한 작업은 개별 INSERT, UPDATE 및 DELETE 문을 포함하는 저장 프로시저 또는 일괄 처리를 실행하여 수행합니다. 그러나 이는 원본 테이블과 대상 테이블의 데이터가 적어도 각 문에 대해 한 번씩 여러 번 계산되고 처리됨을 의미합니다.

MERGE 문을 사용하면 여러 개의 개별 DML 문을 단일 문으로 대체할 수 있습니다. 이렇게 하면 작업이 하나의 문 내에서 수행되므로 원본 및 대상 테이블의 데이터가 처리되는 횟수가 최소화되어 쿼리 성능이 향상됩니다. 단, 성능 향상을 위해서는 인덱스, 조인 및 기타 고려 사항이 올바르게 설정되어야 합니다. 이 항목에서는 MERGE 문을 사용할 때 최적의 성능을 얻는 데 도움이 되는 최선의 방법 권장 사항에 대해 설명합니다.

인덱스를 위한 최선의 방법

MERGE 문의 성능을 높이려면 다음 인덱스 지침을 따르는 것이 좋습니다.

원본 테이블의 조인 열에 고유한 포함 인덱스를 만듭니다.
대상 테이블의 조인 열에 고유한 클러스터형 인덱스를 만듭니다.

이러한 인덱스를 통해 조인 키의 고유성을 확보하고 테이블의 데이터가 정렬되도록 할 수 있습니다. 쿼리 최적화 프로그램이 중복 행을 찾아 업데이트하기 위해 별도로 유효성 검사를 수행할 필요가 없고 추가 정렬 작업도 필요 없으므로 쿼리 성능이 개선됩니다.

예를 들어 다음 MERGE 문에서 원본 테이블 dbo.Purchases와 대상 테이블 dbo.FactBuyingHabits는 ProductID 및 CustomerID 열에서 조인됩니다. 이 문의 성능을 높이려면 dbo.Purchases 테이블의 ProductID 및 CustomerID 열에 고유 또는 기본 키 인덱스(클러스터형 또는 비클러스터형)를 만들고 dbo.FactBuyingHabits 테이블의 ProductID 및 CustomerID 열에 클러스터형 인덱스를 만듭니다. 이 테이블을 만드는 데 사용된 코드는 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.*;

JOIN을 위한 최선의 방법

MERGE 문의 성능을 높이고 정확한 결과를 얻으려면 다음 조인 지침을 따르는 것이 좋습니다.

ON <merge_search_condition> 절에는 원본 및 대상 테이블의 데이터 비교를 위한 조건을 나타내는 검색 조건만 지정합니다. 즉, 대상 테이블에서 원본 테이블의 해당 열과 비교할 열만 지정해야 합니다. 상수와 같은 다른 값에 대한 비교는 포함하지 않습니다.

원본 또는 대상 테이블에서 행을 필터링하려면 다음 방법 중 하나를 사용합니다.

적절한 WHEN 절에 행 필터링을 위한 검색 조건을 지정합니다(예: WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT...).
필터링된 행을 반환하는 원본 또는 대상에 대한 뷰를 정의하고 이 뷰를 원본 또는 대상 테이블로 참조합니다. 대상 테이블에 대해 정의된 뷰의 모든 동작은 뷰 업데이트를 위한 조건을 충족해야 합니다. 뷰를 사용하여 데이터를 업데이트하는 방법은 뷰를 통해 데이터 수정을 참조하십시오.
WITH <common table expression> 절을 사용하여 원본 또는 대상 테이블에서 행을 필터링합니다. 이 방법은 ON 절에 추가 검색 조건을 지정하는 것과 비슷하며 잘못된 결과를 생성할 수 있으므로 사용하지 않는 것이 좋으며 사용할 경우 구현 전에 철저히 테스트해야 합니다.

자세한 내용은 MERGE를 사용하여 데이터 삽입, 업데이트 및 삭제를 참조하십시오.

조인의 쿼리 최적화

MERGE 문의 조인 작업은 SELECT 문의 조인과 동일한 방식으로 최적화됩니다. 즉, SQL Server에서 조인을 처리할 때 쿼리 최적화 프로그램은 여러 가지 가능한 방법 중 가장 효율적인 방법을 선택합니다. 조인에 대한 자세한 내용은 조인 기본 사항 및 고급 쿼리 튜닝 개념을 참조하십시오. 원본 및 대상의 크기가 비슷하고 ‘인덱스를 위한 최선의 방법’ 섹션에 설명된 인덱스 지침을 원본 및 대상 테이블에 적용한 경우 merge join 연산자가 가장 효율적인 쿼리 계획입니다. 두 테이블 모두 한 번만 검색되고 데이터를 정렬할 필요가 없기 때문입니다. 원본 테이블이 대상 테이블보다 작은 경우 nested loops 연산자가 좋습니다.

MERGE 문에서 OPTION (<query_hint>) 절을 지정하여 특정 조인을 사용하도록 강제할 수 있습니다. 해시 조인은 인덱스를 사용하지 않으므로 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 문의 경우 MERGE 문이 실행될 때마다 새 계획이 컴파일되므로 개별 INSERT, UPDATE 또는 DELETE 문보다 성능이 떨어집니다.

쿼리 성능을 높이려면 다음 매개 변수화 지침을 따르는 것이 좋습니다.

ON <merge_search_condition> 절과 MERGE 문의 WHEN 절에 있는 모든 리터럴 값을 매개 변수화합니다. 예를 들어 리터럴 값을 적절한 입력 매개 변수로 대체하여 MERGE 문을 저장 프로시저에 통합할 수 있습니다.
문을 매개 변수화할 수 없는 경우 TEMPLATE 형식의 계획 지침을 만들고 PARAMETERIZATION FORCED 쿼리 힌트를 이 계획 지침에 지정합니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.
MERGE 문이 데이터베이스에서 자주 실행되는 경우 데이터베이스의 PARAMETERIZATION 옵션을 FORCED로 설정하는 것이 좋습니다. 이 옵션을 설정할 때는 신중해야 합니다. PARAMETERIZATION 옵션은 데이터베이스 수준 설정이므로 데이터베이스에 대한 모든 쿼리의 처리 방식에 영향을 미칩니다. 자세한 내용은 강제 매개 변수화를 참조하십시오.

TOP 절을 위한 최선의 방법

MERGE 문에서 TOP 절은 원본 테이블과 대상 테이블이 조인되고 삽입, 업데이트 또는 삭제 동작에 적합하지 않은 행이 제거된 후에 영향을 받는 행의 개수나 비율을 지정합니다. TOP 절은 조인된 행 수를 지정된 값으로 더 줄이며, 삽입, 업데이트 또는 삭제 동작은 나머지 조인된 행에 순서 없이 적용됩니다. 즉, 행은 WHEN 절에 정의된 동작에 순서 없이 분산됩니다. 예를 들어 TOP (10)을 지정하면 10개 행이 영향을 받습니다. 이 10개의 행 중 7개가 업데이트되고 3개가 삽입되거나, 1개가 삭제되고 5개가 업데이트되고 4개가 삽입될 수 있습니다.

일반적으로 TOP 절을 사용하여 큰 테이블에서 일괄 처리로 DML(데이터 조작 언어) 작업을 수행합니다. MERGE 문에서 TOP 절을 이러한 용도로 사용하는 경우 다음 내용을 알고 있어야 합니다.

I/O 성능이 영향을 받을 수 있습니다.

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 열에 적용됩니다.

이 예를 실행하려면 먼저 C:\SQLFiles\ 폴더에 'StockData.txt'라는 텍스트 파일을 만듭니다. 이 파일에는 쉼표로 구분된 두 개의 데이터 열이 있어야 합니다. 예를 들어 다음과 같은 데이터를 사용합니다.

Alpine mountain bike,100

Brake set,22

Cushion,5

그런 다음 C:\SQLFiles\ 폴더에 'BulkloadFormatFile.xml'이라는 xml 서식 파일을 만듭니다. 다음과 같은 정보를 사용합니다.

<?xml version="1.0"?>

</RECORD>

<ROW>

</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 문의 성능을 측정 및 진단할 수 있습니다.

sys.dm_exec_query_optimizer_info 동적 관리의 merge stmt 카운터를 사용하여 MERGE 문에 대한 쿼리 최적화 횟수를 반환할 수 있습니다.
sys.dm_exec_plan_attributes 동적 관리 함수의 merge_action_type 특성을 사용하여 MERGE 문의 결과로 사용되는 트리거 실행 계획의 유형을 반환할 수 있습니다.
SQL Trace를 사용하여 다른 DML(데이터 조작 언어) 문에 대해 사용하는 것과 동일한 방식으로 MERGE 문에 대한 문제 해결 데이터를 수집할 수 있습니다. 자세한 내용은 SQL Trace 소개를 참조하십시오.