[MSSQL] CROSS APPLY, INNER JOIN 성능 비교 테스트

이번 시간에는 CROSS APPLY, INNER JOIN 성능 테스트를 진행합니다.

 

사용법

SELECT ..FROM [외부 테이블]

CROSS | OUTER APPLY (SELECT .. FROM [내부 테이블] WHERE [조인 조건자])

 

[CROSS APPLY]

- 내부 테이블(테이블 반환 함수)의 집합으로부터 조인 키로 결합한 외부 테이블의 행만을 반환합니다.

- INNER JOIN과 동일한 쿼리 결과를 반환합니다.

 

[OUTER APPLY]

- 내부 테이블(테이블 반환 함수)의 집합으로부터 조인 키에 결합한 외부 테이블의 행 + 그렇지 않은 행 전부를 반환합니다. 그렇지 않은 행에 열에는 NULL을 표시합니다.

- LEFT OUTER JOIN과 동일한 결과를 반환합니다.

 

 

 

 

 

---

비교 구문 예제

특정 상황에는 JOIN보다 더 좋은 성능을 발휘할 수 있습니다.

(OUTER APPLY와 TOP을 함께 사용하면 더욱 좋은 성능을 기대할 수 있습니다)

 

1. 테이블 생성

-- 1. 테이블 생성

USE join_test
go

--CUSTOMER(고객) 테이블 생성
CREATE TABLE CUSTOMER( --drop table CUSTOMER
 ID INT IDENTITY(1,1) PRIMARY KEY--아이디
,NAME NVARCHAR(30)--이름
)
GO

--CUSTOMER_PURCHASE(고객 구매 리스트) 테이블 생성
CREATE TABLE CUSTOMER_PURCHASE( --drop table CUSTOMER_PURCHASE
 PURCHASE_NO INT IDENTITY(1,1) PRIMARY KEY--구매번호
,ID INT FOREIGN KEY REFERENCES CUSTOMER(ID)--아이디
,ITEM NVARCHAR(30)--품목
,QTY INT--수량
,PRICE INT--가격
)
GO

 

 2. 데이터 생성(100건) 실행계획 끄고 데이터 생성

-- 2. 데이터 생성(100건) 실행계획 끄고 데이터 생성
declare @i int
declare @end int
set @i=1
set @end=99

while @i <= @end
begin
INSERT CUSTOMER VALUES ('번호'+convert(varchar,@i))
insert into CUSTOMER_PURCHASE values (@i,'ITEM'+convert(varchar,@i),@i,@i+1000)
set @i=@i+1
end
GO

INSERT CUSTOMER VALUES ('물품미구매자')
GO

--고객 정보 확인
SELECT * FROM CUSTOMER where id=1

--고객 구매 리스트 정보 확인
SELECT * FROM CUSTOMER_PURCHASE where id=1

 

---

3.  OUTER APPLY 예제

3개의 동일한 결과를 반환하는 쿼리를 동시에 실행해 비용을 비교해 보았다.

--CUSTOMER_PURCHASE(고객 구매 리스트)에 구매 기록이 없는 고객 정보 추출

--OUTER APPLY + TOP + Correlated Subquery 이용

--TOP 1을 사용하여 필요한 인덱스 키(B.ID)에만 엑세스하여 성능 향상을 기대할 수 있음.

 

1) OUTER APPLY 사용(25%)

SELECT A.ID, A.[NAME],B.ID
FROM CUSTOMER A OUTER APPLY (SELECT TOP 1 ID FROM CUSTOMER_PURCHASE B WHERE A.ID = B.ID) B
WHERE B.ID IS NULL
GO

 

2) LEFT OUTER  JOIN 사용(인덱스 힌트 추가)(55%)

동일한 결과 반환하는 LEFT OUTER  JOIN

LEFT JOIN으로 할경우 인덱스 키(B.ID)에 전체 엑세스하게 되므로 성능이 떨어질 수 있음.

SELECT A.ID, A.[NAME],B.ID
FROM CUSTOMER A
LEFT OUTER LOOP JOIN CUSTOMER_PURCHASE B ON A.ID = B.ID --비교를 위해 조인힌트 추가(LOOP JOIN)
WHERE B.ID IS NULL
GO

 

3) LEFT OUTER  JOIN(인덱스 힌트X)(20%)

SELECT A.ID, A.[NAME],B.ID
FROM CUSTOMER A
LEFT OUTER JOIN CUSTOMER_PURCHASE B ON A.ID = B.ID --옵티마이저는 머지조인으로 진행함
WHERE B.ID IS NULL
GO

 

테스트를 마치며 다음과 같은 장점을 확인

- OUTER APPLY의 장점을 이용하면  LEFT OUTER JOIN 연산자로 작성된 쿼리의 성능 향상이 가능하다.

- SQL구문을 간단하게 만들어 가독성이 증가한다.(커서를 대체하여 사용한다던지…)