MERGE 응용 - 2. CTE를 사용한 성능 최적화

MERGE 문의 기본 형태

MERGE [타겟 테이블] AS T
USING [소스 테이블] AS S
ON T.[컬럼] = S.[컬럼]
WHEN MATCHED THEN
    UPDATE SET ~~~
WHEN NOT MATCHED BY TARGET THEN
    INSERT ~~~
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;


WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

지난 번에 다룬 UPSERT에 한 가지 동작이 추가되었습니다.
<타겟 테이블에만 있고 소스 테이블에 없는 행을 삭제하라!>

이 동작을 수행하되 타겟 테이블의 일부만 가져와서 MERGE하는 경우를 생각해 봅시다.

IF OBJECT_ID(N'dbo.MergeTest', N'U') IS NOT NULL

       DROP TABLE dbo.MergeTest;

GO

 

CREATE TABLE dbo.MergeTest (

       userSN int NOT NULL,

       col1 int NOT NULL,

       col2 char(100) NOT NULL,

       CONSTRAINT PK_MergeTest PRIMARY KEY CLUSTERED (userSN, col1)

);

GO

 

INSERT dbo.MergeTest (userSN, col1, col2)

SELECT TOP (10000) ROW_NUMBER() OVER(ORDER BY A.number), 1, ''

FROM master.dbo.spt_values A

       CROSS JOIN master.dbo.spt_values B;

GO

 

INSERT dbo.MergeTest (userSN, col1, col2)

SELECT userSN, 2, '' FROM dbo.MergeTest

UNION ALL

SELECT userSN, 3, '' FROM dbo.MergeTest;

GO

SELECT COUNT(*) FROM dbo.MergeTest;

Results :

30000

SELECT * FROM dbo.MergeTest WHERE userSN = 10;

Results :

userSN	col1	col2
10	1
10	2
10	3

SELECT * FROM dbo.MergeTest WHERE userSN = 100;

Results :

userSN	col1	col2
100	1
100	2
100	3

- [타겟 테이블]인 dbo.MergeTest 테이블에는 30,000개의 행이 있습니다.
- 이 테이블 중 userSN = 10 인 레코드만 대상으로 MERGE 문을 실행하려면 어떻게해야 할까요?

1. ON 절에 T.userSN = 10을 넣을까요?
아니오. 이렇게하면 NOT MATCHED BY SOURCE THEN DELETE 가 실행될 때 29,997개의 레코드가 삭제됩니다.

2. 그럼 NOT MATCHED BY SOURCE AND T.userSN = 10 THEN DELETE 라고 하면 어떨까요?
네. 원하는 결과를 얻을 수 있습니다.
하지만, [타겟 테이블]을 full scan하므로 성능이 매우 떨어집니다. (물론 userSN 컬럼에 인덱스가 있습니다.)

3. ON 절에도 T.userSN = 10을 넣고, NOT MATCHED BY SOURCE AND T.userSN = 10 THEN DELETE 라고 작성해도 full scan이 일어납니다.

즉, [타겟 테이블]을 효과적으로 필터링하기 위한 무언가가 필요합니다.

제가 찾은 방법은 [타겟 테이블]을 CTE로 대체하는 것입니다.


위의 방법을 사용했을 때와 CTE를 사용했을 때의 차이는 아래 예와 같습니다.

MERGE dbo.MergeTest AS T

USING (

       SELECT 10, 2, ''

       UNION ALL

       SELECT 10, 3, 'A'

       UNION ALL

       SELECT 10, 4, 'B'

) AS S (userSN, col1, col2)

ON T.userSN = S.userSN AND T.col1 = S.col1

WHEN MATCHED AND T.col2 <> S.col2 THEN

       UPDATE SET col2 = S.col2

WHEN NOT MATCHED BY TARGET THEN

       INSERT (userSN, col1, col2) VALUES (S.userSN, S.col1, S.col2)

WHEN NOT MATCHED BY SOURCE AND T.userSN = 10 THEN

       DELETE;

GO

 

SELECT * FROM dbo.MergeTest WHERE userSN = 10;

Results :

userSN	col1	col2
10	2
10	3	A
10	4	B

WITH CTE AS (

       SELECT userSN, col1, col2

       FROM dbo.MergeTest

       WHERE userSN = 100

)

MERGE CTE AS T

USING (

       SELECT 100, 2, ''

       UNION ALL

       SELECT 100, 3, 'A'

       UNION ALL

       SELECT 100, 4, 'B'

) AS S (userSN, col1, col2)

ON T.userSN = S.userSN AND T.col1 = S.col1

WHEN MATCHED AND T.col2 <> S.col2 THEN

       UPDATE SET col2 = S.col2

WHEN NOT MATCHED BY TARGET THEN

       INSERT (userSN, col1, col2) VALUES (S.userSN, S.col1, S.col2)

WHEN NOT MATCHED BY SOURCE THEN

       DELETE;

GO

SELECT * FROM dbo.MergeTest WHERE userSN = 100;

Results :

userSN	col1	col2
100	2
100	3	A
100	4	B

CTE를 사용하면 [타겟 테이블]의 크기를 미리 제한할 수 있습니다.

단, CTE를 [타겟 테이블]로 사용하는 경우 예상하지 않은 동작을 일으키지 않는지 충분히 테스트하셔야 합니다.