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

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

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

개선효과 : 98.37 %

* 원본 쿼리

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.selectContentListBySeriesId */
AIF.ALBUM_ID           AS CONTENT_ID ,
AIF.ALBUM_NAME         AS CONTENT_NAME ,
SER_MST.ALBUM_GROUP_ID AS SERIES_ID_IMCS ,
AIF.SERIES_NO ,
SER_MST.ALBUM_GROUP_NM AS SERIES_NAME
FROM IMCSUSER.PT_LA_ALBUM_GROUP SER_MST ,
IMCSUSER.PT_LA_ALBUM_RELATION SAM ,
IMCSUSER.PT_LA_ALBUM_INFO AIF,
SUPER SUPER
WHERE SER_MST.ALBUM_GROUP_ID = SUPER.SERIES_ID
AND SER_MST.ALBUM_GROUP_ID   = SAM.ALBUM_GROUP_ID
AND SER_MST.GROUP_TYPE       = SAM.GROUP_TYPE
AND SER_MST.GROUP_TYPE       = 'S'
AND SAM.ALBUM_ID             = AIF.ALBUM_ID;

개선 포인트

with 문안의 서브쿼리 in 문을 exists 문으로 변경 처리
기존 3sec => 에서 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.selectContentListBySeriesId */
AIF.ALBUM_ID AS CONTENT_ID ,
AIF.ALBUM_NAME AS CONTENT_NAME ,
SER_MST.ALBUM_GROUP_ID AS SERIES_ID_IMCS ,
AIF.SERIES_NO ,
SER_MST.ALBUM_GROUP_NM AS SERIES_NAME
FROM IMCSUSER.PT_LA_ALBUM_GROUP SER_MST ,
IMCSUSER.PT_LA_ALBUM_RELATION SAM ,
IMCSUSER.PT_LA_ALBUM_INFO AIF,
SUPER SUPER
WHERE SER_MST.ALBUM_GROUP_ID = SUPER.SERIES_ID
AND SER_MST.ALBUM_GROUP_ID = SAM.ALBUM_GROUP_ID
AND SER_MST.GROUP_TYPE = SAM.GROUP_TYPE
AND SER_MST.GROUP_TYPE = 'S'
AND SAM.ALBUM_ID = AIF.ALBUM_ID;

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