[SQL Server] SQL Server 쿼리 성능 측정과 개선

SQL Server 쿼리 성능 측정과 개선

어느 날 진행 중인 프로젝트에서 한 쿼리의 성능이 이슈가 된 적이 있었습니다.
임시 테이블 A의 조건절에 조건 값으로 A 값과 B 값을 각각 넣었을 때,

두 조건 사이의 조회되는 데이터 양이 크게 차이가 나지 않음에도 불구하고,

조회 시 약 30배 차이의 소요시간이 걸리는 결과를 보여줬습니다.

(해당 조건은 애플리케이션의 가장 기본이 되는 조건으로써 대부분의 테이블에서 사용되는 컬럼값입니다.)

 

해당 쿼리는 여러 개의 테이블 정보를 조인해서 완료 건수, 전체 건수, 비율 등 가공된 결과를 가져오는데,

여러개의 임시 테이블을 선언해서 결과에 필요한 재료데이터들을 먼저 조회하는 구조였습니다.

각각 임시테이블은 5개의 테이블과 조인된 뷰를 조회하고 있었고 그 외에도 필요한 다른 테이블들과 조인되어 있었습니다.

---

이슈가 된 쿼리

SELECT
	DATAS
FROM
	(SELECT
		(SELECT
			COUNT(A)
		FROM
			VW_ITEM CI
			INNER JOIN 
				TB_T T ON CI.CON = T.CON
			INNER JOIN 
				TB_EP EP ON CI.CON = EP.CON
			INNER JOIN 
				VW_DI DI ON DI.CON = EP.CON
		WHERE
			1=1
			AND CONDITIONS) AS totA,
		(SELECT
			COUNT(A)
		FROM
			VW_ITEM CI
			INNER JOIN 
				TB_T T ON CI.CON = T.CON            
			INNER JOIN 
				TB_EP EP ON CI.CON = EP.CON
			INNER JOIN 
				VW_DI DI ON DI.CON = EP.CON
		WHERE
			1=1
			AND CONDITIONS) AS endA,
		(SELECT
			COUNT(A)
		FROM
			VW_ITEM CI
			INNER JOIN 
				TB_T T ON CI.CON = T.CON            
			INNER JOIN 
				TB_EP EP ON CI.CON = EP.CON
			INNER JOIN 
				VW_DI DI ON DI.CON = EP.CON
		WHERE
			1=1
			AND CONDITIONS) AS totB,
		(SELECT
			COUNT(A)
		FROM
			VW_ITEM CI
			INNER JOIN 
				TB_T T ON CI.CON = T.CON
			INNER JOIN 
				TB_EP EP ON CI.CON = EP.CON
			INNER JOIN 
				VW_DI DI ON DI.CON = EP.CON
		WHERE
			1=1
			AND CONDITIONS) AS endB,
					DE.C AS C,
					DE.D AS D
	FROM
		VW_DE DE
	WHERE
		DE.CON = 'CON') A
WHERE
	0 < A.totA
	OR 0 < A.totB
ORDER BY
	perA DESC,
	perB DESC,
	A.totA DESC,
	A.totB DESC

먼저 어느 부분에서 가장 많은 데이터를 조회하는지 알아보기 위해 아래 명령어를 추가하고 쿼리를 실행했습니다.

SET STATISTICS TIME ON; (컴파일 및, 실행시간)
SET STATISTICS IO ON; (각 구문 당 IO 실행 결과)

[MAIN QUERY]

SET STATISTICS IO OFF;

 

첫 번째 시도

가장 많은 조회가 이루어지는 부분은 CI 뷰였는데 먼저 해당 뷰를 테이블로 바꿔 직접 조회하도록 해봤습니다.
그러자 조건값이 A일 때와  B일 때의 차이가 거의 없을 정도로 개선되었는데, 뷰와 조건의 직접적인 상관관계는 알 수 없었습니다.
뷰를 이루고 있는 조인된 테이블을 모두 뒤져봐도 조건 값에 따른 데이터의 양이나 질의 차이는 없었습니다.
결과는 성공이지만 뭔가 찝찝했습니다.

 

 

두 번째 시도

최하위 임시테이블 (이하 임시테이블) 이 데이터를 가져오는 곳인 뷰와 임시테이블들을 살펴보았더니
뷰에서 조인한 테이블을 임시테이블이 또 조인하고 있었습니다. (둘 다 INNER JOIN.)
혹시 몰라서 해당 중복 조인을 제거했는데 조회결과나 성능의 변화는 전혀 없었습니다.

세 번째 시도

임시테이블이 조인을 걸 때 문제가 되는 조건을 함께 참조하도록 해당 조건을 임시테이블 모두에 집어넣어 보았습니다.
역시 유의미한 차이는 없었습니다...

 

네 번째 시도

이번에는 각각의 임시테이블을 따로 떼어내서 조회해 보았습니다.

SELECT
	COUNT(A)
FROM
    VW_ITEM CI
    INNER JOIN 
        TB_T T ON CI.CON = T.CON
    INNER JOIN 
        TB_EP EP ON CI.CON = EP.CON
    INNER JOIN 
        VW_DI DI ON DI.CON = EP.CON
WHERE
    1=1
    AND CONDITIONS

이 시도에서 조금 유의미한 발견을 했습니다.

문제가 됐다고 생각했던 뷰 조회나 타 테이블 조인까지는 아무런 이상이 없었으나,
임시테이블에 걸려있던 WHERE 조건 부분에서 상당한 딜레이가 걸리기 시작한다는 사실을 발견했습니다.
해당 딜레이를 가져오는 조건을 찾기 위해 조건을 하나씩 새로 달아가며 조회를 해봤습니다.

문제는 CI 뷰뿐 아니라 DI 뷰에서도 발생하고 있었고 문제의 원인은 조건값들과 뷰 구조의 관계에 있었습니다.

하지만 해당 조건값이 조회하는 데이터들이 어떤 성질을 가지고 있어서 이렇게 퍼포먼스의 차이가 나는지는

여전히 발견하지 못했습니다. 저는 풀린 과제와 새로 생긴 과제를 안고 원점으로 다시 되돌아왔습니다.

 

 

다섯 번째 시도

이번에는 임시테이블의 조건절들을 조합해 가며 조회를 했습니다. 여기서는 한 가지 이상한 점을 발견했습니다.

지금은 사장되어 사용하지 않는 코드값을 뷰와 조인된 테이블 사이에서 비교하는 조건이었는데 (CI.C = DI.C)

이 조건을 넣느냐 빼느냐가 다시 성능의 차이를 결정짓고 있었습니다.
스코프를 넓혀서 지금까지 조회했던 최하위 임시테이블뿐 아니라 최하위 임시테이블을 값으로  포함하는
임시테이블인 A (이하 A) 구조까지 포함하여 탐색했습니다.

 

---

결국 답을 찾지는 못했습니다.

임시방편으로 성능개선을 위해 1번 시도에서 찾은 방법을 쓰기로 했지만 직접적인 문제 해결은 아니었습니다.
조건 값에 따른 믿을 수 없는 성능차이의 문제는 언젠가 해결하고 결과를 공유하겠습니다.

 

도움이 되어드렸다면 구독 / 좋아요 부탁드립니다.👏🏻

감사합니다.🙂