[SQL 튜닝]스칼라 서브쿼리 튜닝하기

서론

 

리오더 프로젝트를 진행하다가 이력관리 테이블에서 스칼라 서브 쿼리로 각각의 날짜 컬럼을 조회하는 쿼리를 발견한 적이 있습니다. 같은 테이블에 반복적으로 액세스를 하게 되므로 비효율적이고 이후에 조회 컬럼이 추가되면 테이블 액세스 반복 횟수는 점점 증가하므로 개선을 해보고 싶었습니다. 이를 튜닝하는 방법을 [친절한 SQL 튜닝] 책에서 봤던 기억이 나 공부했던 내용을 토대로 실제 프로젝트에 적용하여 개선했습니다. 이 과정에서 다시 한번 복습하게 된 스칼라 서브 쿼리 튜닝 방법을 이번 포스팅에서 소개해보려고 합니다.

 

실행환경

• SQL Server 13.0

[예제 테스트 데이터 생성 SQL]

 

--테이블 생성
--회원 테이블
CREATE TABLE MEMBER(
     MEMBER_NO INT--회원번호
	,MEMBER_NM NVARCHAR(30)--회원명
	,JOIN_DT DATETIME--가입일
);
--멤버십 이력관리 테이블
CREATE TABLE MEMBER_FEE(
   MEMBER_NO INT--회원번호
	,SEQ       INT--순번
	,MEMBER_AMT NUMERIC(18,0)--멤버십요금
	,START_DT DATETIME--시작일
	,APPLY_DT DATETIME--실제적용일
	,END_DT DATETIME--종료일
);

--테스트 데이터 생성
INSERT INTO MEMBER
SELECT 1,'SWAN','20200101'
UNION ALL
SELECT 2,'ALICE','20210102'
UNION ALL
SELECT 3,'BOB','20200601'
UNION ALL
SELECT 4,'SAM','20210901'
UNION ALL
SELECT 5,'TOM','20200501'

INSERT INTO MEMBER_FEE
SELECT 1,1,50000,'20200101','20200102','20201231'
UNION ALL
SELECT 1,2,40000,'20210103','20210103',N'20210630'
UNION ALL
SELECT 2,1,15000,'20210103','20210103','20210630'
UNION ALL
SELECT 3,1,20000,'20200601','20200601','20201231'
UNION ALL
SELECT 3,2,20000,'20210101','20210630','20210630'
UNION ALL
SELECT 3,3,20000,'20210701','20210702','20211231'
UNION ALL
SELECT 5,1,30000,'20200501','20200501','20200731'
UNION ALL
SELECT 5,2,20000,'20200801','20200802','20210630'
UNION ALL
SELECT 5,3,10000,'20210630','20210630','20210831'

 

본론

 

문제점

 

[문제의 스칼라 서브 쿼리]

 

SELECT MEMBER_NM 
       ,(SELECT CONVERT(CHAR(10), MIN(START_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS FIRST_FEE_DT
	   ,(SELECT CONVERT(CHAR(10), MAX(END_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS END_FEE_DT
FROM MEMBER A

 

[실행 계획]

 

START_DT, END_DT 컬럼을 조회하기 위해 MEMBER_FEE 테이블에 2번 반복 액세스하고 있습니다. 이후 APPLY_DT 컬럼 혹은 다른 컬럼 조회를 추가해야 한다면 테이블 액세스 횟수는 추가한 만큼 늘어나는 문제점이 생깁니다.

 

[실제 적용일(APPLY_DT)의 최대(FIRST_APPLY_DT), 최소(END_APPLY_DT)를 추가했을 경우]

 

SELECT MEMBER_NM 
       ,(SELECT CONVERT(CHAR(10), MIN(START_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS FIRST_FEE_DT
	   ,(SELECT CONVERT(CHAR(10), MAX(END_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS END_FEE_DT
	   ,(SELECT CONVERT(CHAR(10), MIN(APPLY_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS FIRST_APPLY_DT
	   ,(SELECT CONVERT(CHAR(10), MAX(APPLY_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS END_APPLY_DT
FROM MEMBER A

 

[실행계획]

 

MEMBER_FEE 테이블에 4번의 반복 액세스가 발생 → 비효율 증가

 

해결책

 

[ 반복적인 테이블 액세스 비효율을 개선한 스칼라 서브 쿼리]

 

SELECT MEMBER_NM
      ,SUBSTRING(DT,1,10) AS FIRST_FEE_DT
	  ,SUBSTRING(DT,11,10) AS END_FEE_DT
	  ,SUBSTRING(DT,21,10) AS FIRST_APPLY_DT
	  ,SUBSTRING(DT,31,10) AS END_APPLY_DT
FROM
(
SELECT MEMBER_NM 
       ,(SELECT CONVERT(CHAR(10), MIN(START_DT),126)
				+CONVERT(CHAR(10), MAX(END_DT),126)
				+CONVERT(CHAR(10), MIN(APPLY_DT),126)
				+CONVERT(CHAR(10), MAX(APPLY_DT),126) FROM MEMBER_FEE WHERE MEMBER_NO = A.MEMBER_NO) AS DT
FROM MEMBER A
)RESULT

 

 

[실행계획]

 

MEMBER_FEE 테이블을 액세스 할 때 구해야 할 값들을 모두 더하여 인라인 뷰로 감싸고 바깥쪽 SELECT절에서 SUBSTRING을 이용해 문자열을 분리했습니다. 이렇게 하면 이후에 컬럼이 추가되어도 MEMBER_FEE 테이블 액세스는 1회만 발생하므로 효율적입니다.

 

결론

 

오늘은 스칼라 서브 쿼리 튜닝 방법에 대해서 알아보았습니다. 공부하면서 배웠던 개념들을 실제 프로젝트에 적용하여 개선해보니 더욱 의미 있는 배움이었다고 느껴집니다. 앞으로도 배웠던 내용들을 프로젝트에 적용하여 개선 또는 최적화할 수 있는지 항상 생각해보아야겠습니다.

 

 

Reference

 

조시형, 친절한 SQL 튜닝, 2018, 4.4 서브쿼리 조인中