[완료] /* modifySchedule.base.selectContentListBySeriesIdDup */

원샷원따봉 2022. 8. 29. 11:29

DEVSTP Mylg DB 수행 시간 :
AS-IS : 2.9 sec
TO-BE : 60ms

개선효과 : 97.93 %

원본 쿼리

WITH SUPER AS (
SELECT ALBUM_ID AS SERIES_ID
FROM IMCSUSER.PT_LA_ALBUM_RELATION A
WHERE A.ALBUM_GROUP_ID IN (SELECT ALBUM_GROUP_ID
FROM IMCSUSER.PT_LA_ALBUM_RELATION
WHERE ALBUM_ID IN
(
'0154348001'
)
AND QUALITY_TYPE = A.QUALITY_TYPE
AND SALE_TYPE = A.SALE_TYPE
AND GROUP_TYPE = 'C'
GROUP BY ALBUM_GROUP_ID)
UNION
SELECT ALBUM_GROUP_ID AS SERIES_ID
FROM IMCSUSER.PT_LA_ALBUM_GROUP
WHERE GROUP_TYPE = 'S'
AND ALBUM_GROUP_ID IN
(
'0154348001'
)
)
SELECT
/* modifySchedule.base.selectContentListBySeriesIdDup */
SER_MST.ALBUM_GROUP_ID AS CONTENT_ID ,
SER_MST.ALBUM_GROUP_NM AS CONTENT_NAME
FROM IMCSUSER.PT_LA_ALBUM_GROUP SER_MST ,
SUPER SUPER
WHERE SER_MST.ALBUM_GROUP_ID = SUPER.SERIES_ID
AND SER_MST.GROUP_TYPE = 'S';

개선 포인트

with 문안의 서브쿼리 in 문을 exists 문으로 변경 처리
기존 2 sec => 에서 60ms 로 대폭 개선

변경 쿼리

WITH SUPER AS (
SELECT ALBUM_ID AS SERIES_ID
FROM IMCSUSER.PT_LA_ALBUM_RELATION A
WHERE exists (SELECT 1
FROM IMCSUSER.PT_LA_ALBUM_RELATION
WHERE ALBUM_ID IN
(
'0154348001'
)
AND QUALITY_TYPE = A.QUALITY_TYPE
AND SALE_TYPE = A.SALE_TYPE
AND GROUP_TYPE = 'C'
and ALBUM_GROUP_ID = A.ALBUM_GROUP_ID
GROUP BY ALBUM_GROUP_ID)
UNION
SELECT ALBUM_GROUP_ID AS SERIES_ID
FROM IMCSUSER.PT_LA_ALBUM_GROUP
WHERE GROUP_TYPE = 'S'
AND ALBUM_GROUP_ID IN
(
'0154348001'
)
)
SELECT
/* modifySchedule.base.selectContentListBySeriesIdDup */
SER_MST.ALBUM_GROUP_ID AS CONTENT_ID ,
SER_MST.ALBUM_GROUP_NM AS CONTENT_NAME
FROM IMCSUSER.PT_LA_ALBUM_GROUP SER_MST ,
SUPER SUPER
WHERE SER_MST.ALBUM_GROUP_ID = SUPER.SERIES_ID
AND SER_MST.GROUP_TYPE = 'S';

저작자표시 비영리 변경금지 (새창열림)