-
[완료] /* modifySchedule.base.selectContentListBySeriesId */Postgresql / PPAS/Query tuning 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;- 개선 포인트
기존 3sec => 에서 60ms 로 대폭 개선- 변경 쿼리
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;'Postgresql / PPAS > Query tuning' 카테고리의 다른 글
[완료] /* content.base.seriesListPageData */ (0) 2022.08.29 [완료]/* abtest.content.base.popupAbtestFormRouteListPageDataSeries */ (0) 2022.08.29 [완료]schedule.distr.newScheduleListPageData (0) 2022.08.29 [완료] /* modifySchedule.base.selectContentListBySeriesIdDup */ (2) 2022.08.29 [튜닝불가] /* categoryform.dr.getAlbumSeriesList */ (0) 2022.08.29