SQL 조건(WHERE)절 함수를 사용할 때 성능 향상하기

-- 오라클

String TargetDate = '2023-01-01'

-- 변경 전
SELECT * 
FROM BOARD_TABLE
WHERE TO_CHAR(REG_DATE, 'YYYY-MM-DD') <= #{TargetDate}


-- 변경 후
SELECT *
FROM BOARD_TABLE 
WHERE REG_DATE <= TO_DATE(#{TargetDate})

 

날짜 비교시 많이 하는 실수인데

함수로 포맷팅을 할때는 컬럼을 하지말고 반드시 상수를 해야한다.

 

컬럼을 함수로 포맷팅하면 데이터베이스 건수만큼 포맷팅을 하기 때문에 부하의 요인이 된다.

 

 

-- MS-SQL

 

2023년7월1일부터 2023년 9월30일까지 일요일,토요일에만 해당하는 날짜 출력하기

 

-- 변수
String weekNo = '1000001'; // 1111111 = 일월화수목금토일 , 1010101 = 일화목토 

-- 변경 전
SELECT A.MEMBER_ID, A.MEMBER_NAME, A.REG_DATE
FROM TABLE_A A WITH(NOLOCK) 
INNER JOIN TABLE_B B WITH(NOLOCK) 
ON A.MEMBER_ID = B.MEMBER_ID 
LEFT OUTER JOIN TABLE_C C WITH(NOLOCK) 
ON A.MEMBER_ID = C.MEMBER_ID
AND C.USE_YN = 'Y' 
LEFT OUTER JOIN TABLE_E E WITH(NOLOCK) 
ON A.MEMBER_NAME = E.MEMBER_NAME 
WHERE 1=1 
AND SUBSTRING(#{weekNo}, DATEPART(WEEKDAY, A.REG_DATE), 1) = '1'
AND A.REG_DATE >= '20230701' AND A.REG_DATE < DATEADD(DAY, 1, '20230930') 
ORDER BY A.REG_DATE DESC 

-- 변경 후
WITH AAA AS ( 
	SELECT * FROM ( 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 1, 1), '1', 1) AS WEEKDAY 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 2, 1), '1', 2) 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 3, 1), '1', 3) 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 4, 1), '1', 4) 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 5, 1), '1', 5) 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 6, 1), '1', 6) 
		UNION ALL 
		SELECT REPLACE(SUBSTRING(#{weekNo}, 7, 1), '1', 7) 
	) AA 
	WHERE AA.WEEKDAY != '0' 
)
SELECT A.MEMBER_ID, A.MEMBER_NAME, A.REG_DATE
FROM TABLE_A A WITH(NOLOCK) 
INNER JOIN TABLE_B B WITH(NOLOCK) 
ON A.MEMBER_ID = B.MEMBER_ID 
LEFT OUTER JOIN TABLE_C C WITH(NOLOCK) 
ON A.MEMBER_ID = C.MEMBER_ID
AND C.USE_YN = 'Y' 
LEFT OUTER JOIN TABLE_E E WITH(NOLOCK) 
ON A.MEMBER_NAME = E.MEMBER_NAME 
WHERE 1=1 
AND DATEPART(WEEKDAY, A.REG_DATE) IN (SELECT WEEKDAY FROM AAA)
AND A.REG_DATE >= '20230701' AND A.REG_DATE < DATEADD(DAY, 1, '20230930') 
ORDER BY A.REG_DATE DESC

이 쿼리에서 부하가 걸리는 요소는 SUBSTRING 함수안에 등록날짜 컬럼이 있는 부분이다.

사용자가 검색 값으로 넘긴 요일 번호 파라미터를 with 문을 사용하여 요일 번호를 추출 후
IN 검색을 이용하여 훨씬 더 속도가 빨라졌다.