미디어솔루션

우째 짜면 쿼리가 4분이니

5 minute read

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/d29f09d3-3f7b-4dde-bcfd-b55f622f27a4/db1.png

이 쿼리에 잠이 오냐?

4.3 분

통계를 내는 쿼리를 짜고 실제 데이터를 이용해 실행하고 결과까지 나오는데 걸리는 시간이다.

본 쿼리의 목적은 다음과 같다.

년도 검색일 경우, 1월 ~ 12월 까지 매장에서 나온 로그들을 셈을 세 각 매장과 월에 대해 결과를 낼 것 월 검색일 경우, 1일 ~ 해당 월의 마지막 일 까지 매장에서 나온 로그들을 셈을 세 각 매장과 일에 대해 결과를 낼 것

쿼리 후 결과 화면

  • 년 기준 검색 시

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/08cc5a0f-4d17-4a2f-bc5f-cde6b564816e/db4.png

  • 월 기준 검색 시

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/6c235729-d598-4bad-908d-f0a60c4d35c2/db5.png

위와 같은 결과를 내기위해 쿼리를 작성하였다. 근데 그게 성능이 아주 좋지 못하게 될 줄은 몰랐다.

덕분에? 웹 페이지에서도 실제로 데이터를 조회하고, 그래프를 그리는데 까지 5분이 넘었고, 일부로 쿼리 종료 30초 대기 값도 수정해야만 억지로라도 데이터가 반환되는 상황이었다.

근데 이런건 용납이 되지 않는다.

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/52dfa2cf-8fa1-452a-8a52-7a018c556902/.png

위의 사진은 웹 페이지 로드 시간에 따른 유저 이탈률인데, 1초도 기달려 주지 않는 유저들이 대부분이기 때문이다. 무조건 개선을 해야 되었다.

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/9d10caff-aafb-4a9a-a2fc-7ad834c27b80/611211110013060278_1.jpg

왜 느릴까?

기존의 쿼리를 보자

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [DB].[VER1]
	-- Add the parameters for the stored procedure here
	@date DATETIME,
	@dateEnd DATETIME,
	@monthly TINYINT = 1
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @nextDate DATETIME;

	--DROP TABLE
	--	#Schedule_Result;
	
	PRINT '[START] DECLARE Schedule_Result table'
	SET STATISTICS TIME ON
	DECLARE @Schedule_Result table (
				...
	)
	
	SET STATISTICS TIME OFF

	PRINT '[END] DECLARE Schedule_Result table'

	WHILE @date < @dateEnd
	BEGIN
		
		PRINT '[START] ADD nextDate: ' + (CONVERT(VARCHAR(24), @nextDate, 23))
		SET STATISTICS TIME ON
		IF @monthly = 1
		BEGIN
			SET @nextDate = DATEADD(month, 1, @date);
		END
		ELSE
		BEGIN
			SET @nextDate = DATEADD(day, 1, @date);
		END
		SET STATISTICS TIME OFF
		PRINT '[END] ADD nextDate: ' + (CONVERT(VARCHAR(24), @nextDate, 23))
		
		PRINT '[START] INSERT '
		SET STATISTICS TIME ON
		INSERT INTO
			@Schedule_Result
			(
				...
			)
			SELECT
				@date,
				...
				(
					SELECT
						COUNT(...)
					FROM
						...
					WHERE
						@date <= Insdate and Insdate < @nextDate and 
						...
				) 
			FROM
				...
			WHERE
				...
				
		SET STATISTICS TIME OFF
		PRINT '[END] INSERT'
		
		PRINT '[START] ADD date: ' + (CONVERT(VARCHAR(24), @date, 23))
		SET STATISTICS TIME ON

		IF @monthly = 1
		BEGIN
			SET @date = DATEADD(month, 1, @date);
		END
		ELSE
		BEGIN
			SET @date = DATEADD(day, 1, @date);
		END

		SET STATISTICS TIME OFF
		PRINT '[END] ADD date: ' + (CONVERT(VARCHAR(24), @date, 23))
	END

	SELECT
		*
	FROM
		@Schedule_Result
	ORDER BY
				...
END

일단 좀 길으니 나누어서 보자면,

	DECLARE @Schedule_Result table (
				...
	)

결과를 담을 변수 테이블을 만들고

SELECT
				@date,
				...
				(
					SELECT
						COUNT(...)
					FROM
						...
					WHERE
						@date <= Insdate and Insdate < @nextDate and 
						...
				) 
			FROM
				...
			WHERE
				...

하루치 혹은 1달 치의 데이터의 합계를 내어

INSERT INTO
			@Schedule_Result
			(
				...
			)
			SELECT
				@date,
				...
				(
					SELECT
						COUNT(...)
					FROM
						...
					WHERE
						@date <= Insdate and Insdate < @nextDate and 
						...
				) 
			FROM
				...
			WHERE
				...

테이블 변수에 INSERT 해주며

WHILE @date < @dateEnd
	BEGIN

WHILE 문을 이용해 검색 하고자 하는 기간내 있는 내용을 조사한다.

IF @monthly = 1
		BEGIN
			SET @date = DATEADD(month, 1, @date);
		END
		ELSE
		BEGIN
			SET @date = DATEADD(day, 1, @date);
		END

그 기간을 계산하는건 조건에 따라 월을 더하거나 일을 더하게 된다. 이게 4.3분 쿼리이다.

SET STATISTICS TIME ON
...
SET STATISTICS TIME OFF

문제를 파악하기 위해 의심이 되는 구간을 나누어 시간초를 계산하여 출력해보기로 하였다.

  1. 테이블 변수 생성 구간이 느리다.
  2. 날짜 계산 구간이 느리다

  3. 실질적인 통계 계산 구간이 느리다.

    [START] ADD nextDate: 2020-01-30

    SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 1ms.

    SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 1ms. [END] ADD nextDate: 2020-01-31 [START] INSERT

    SQL Server 실행 시간: CPU 시간 = 8971ms, 경과 시간 = 8980ms. [END] INSERT [START] ADD date: 2020-01-30

    SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 1ms.

    SQL Server 실행 시간: CPU 시간 = 0ms, 경과 시간 = 1ms. [END] ADD date: 2020-01-31

결과는 꽤 충격적이었다. 일단 느린 부분은 찾아서 다행이긴 한데 생각보다 많이 느렸다. 날짜 계산은 거의 시간을 쓰지 않았지만

INSERT INTO
			@Schedule_Result
			(
				...
			)
			SELECT
				@date,
				...
				(
					SELECT
						COUNT(...)
					FROM
						...
					WHERE
						@date <= Insdate and Insdate < @nextDate and 
						...
				) 
			FROM
				...
			WHERE
				...

이 부분이 느렸다. 이를 개선하고자 쿼알못이 맨땅에 헤딩을 하게 된다.

1번째 쿼리 개선

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/02d9112b-a354-46e7-a8ed-ef541214f915/db2.png

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/ff339df2-78f3-41f8-a94a-91be41dd81b0/de719a355e71e046f1b70113207b2212e12075237c23be12619f9b4b06ba2932de3a92632c14029b20e6c8c7a581d89f5a6a1c0e56b0244a8f38443fe78abefb00fb7ff3d17cc1423e605e14c58f84f238eb6ca64d33906fefcce6c765824022.gif

와! 4.5분!

개선 했다면서 더 느려졌다!

INSERT INTO
			@Schedule_Result
			(
				...
			)
			SELECT
				@date,
				...
			FROM
				(
					SELECT
						*
					FROM
						...
					WHERE
						...
				) p
			LEFT OUTER JOIN
				(
					SELECT
						...
						COUNT(...) AS cnt
					FROM
						...
					WHERE
						@date <= Insdate and Insdate < @nextDate and Sended = 1
					GROUP BY
						...
				) c
			ON
				...

생각했던 문제점이 퍼포먼스 개선으로 가지 않고 오히려 역행했다. 느리다고 생각했던건 SELECT 문에서 SELECT 할 컬럼에 또 SELECT 를 하는 바람에 매 SELECT 마다 느려졌다고 예상을 하였다.

하지만 아니었다. INSERT 가 문제였다.

DML의 INSERT 성능 향상

모든 INSERT 는 매 작업마다 디스크 I/O 가 걸린다. 이를 수 천번 돌리니 당연히 느려질 수 밖에 없었다. 개선을 하기위해선 INSERT 문을 제거해야 했는데, 그렇게 되면 각 날짜에 발생한 통계를 계산하는 부분이 막히게 되었다.

우선 날짜 계산이 필요했다.

2번째 개선

의외로 날짜 계산은 쉬웠다. 구글 검색을 하면 늘 이미 만들어놓은 쿼리가 존재 하였는데, 그 중 하나인 재귀방식으로 날짜를 만들어내는 방식이었다.

;WITH Calender AS
	(
		SELECT @date AS CalanderDate
		-- 처음은 @date 를 CalanderDate 란 별명으로 SELECT
		UNION ALL
		-- 이후 쿼리 결과를 합침
		SELECT 
			(
				CASE 
					WHEN (@monthly = 1) THEN DATEADD(MONTH, 1, CalanderDate)
					WHEN (@monthly = 0) THEN DATEADD(DAY, 1, CalanderDate)
					-- Calender 테이블을 재귀 호출 하되 이전에 출력한 CalanderDate 보다 +1 (월 / 일) 하여
				END
			)
		FROM 
			Calender
		WHERE 
			(
				CASE
					WHEN (@monthly = 1) THEN DATEADD(MONTH, 1, CalanderDate)
					WHEN (@monthly = 0) THEN DATEADD(DAY, 1, CalanderDate)
				END
			) < @dateEnd
		-- Calender 테이블의 결과가 사용자가 원하는 마지막 날보다 작을 때 까지 재귀
		-- 이 경우 재귀가 원하는 날짜 까지 돌 것이므로 1년치면 365 번의 재귀가 돌 수 있어
		-- 최종 쿼리에 OPTION (MAXRECURSION 0) 을 넣어줘야 한다

	)

Calander 테이블이 재귀적으로 호출을 하였고, 이 결과 값을 UNION ALL 로 합쳐지면 검색할 기간의 날짜가 만들어 졌다. INSERT 없이도!

이후 메인 소스는 거의 같았는데 중간에 문제가 생긴 부분이 있었다.

년도 기준 검색시 2020-02-01 , 2020-03-01 … 으로 날짜가 계산되는데 이를 JOIN할 로그 테이블의 결과는 2020-02-01, 2020-02-02 … 으로 흘러 가므로 JOIN 시 연결이 되지 않을 경우가 존재 하였다.

이를 위해선 로그 날짜를 그 달의 첫 째 날로 바꿔주는 작업이 필요했다.

DATEADD(month, DATEDIFF(month, 0, ...), 0)

해당 날짜와 DB 시스템의 첫 날짜 1900-01-01 의 차이를 달로 계산하고 그 차이가 난 달 만큼을 1900-01-01 에 더해주면 그 달의 첫 날이 계산 되었다.

결론

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/7085c1fe-f366-46fa-9c8e-28af82e6a555/db3.png

0.5 초! 와우! 알게된 교훈은 INSERT 문을 쓸땐 주의하자!

Categories:

Updated:

You May Also Enjoy