-
[완료]/* cuesheet.base.getCueSheetItemDetailForLayer */Postgresql / PPAS/Query tuning 2022. 8. 29. 12:09
Select 절 내의 스칼라 서브 쿼리를 JOIN문으로 대처
AS-IS
2.5 sec
TO-BE
150ms- 원본 쿼리
/* cuesheet.base.getCueSheetItemDetailForLayer */
ROW_NUMBER() OVER (
ORDER BY
TO_NUMBER(DTL.CAM_NO)
) as no,
DTL.CUESHEET_ID AS cuesheet_id,
DTL.ITEM_NO AS item_no,
DTL.OMNIV_NO AS omniv_no,
DTL.OMNIV_TYPE AS omniv_type,
DTL.CAM_NO AS cam_no,
DTL.ALBUM_ID AS album_id,
(
SELECT
AI.ALBUM_NAME
FROM
imcsuser.PT_LA_ALBUM_INFO AI
WHERE
AI.ALBUM_ID = DTL.ALBUM_ID
) AS album_name,
DTL.ACTOR_ID AS actor_id,
DTL.ACTOR_NAME AS actor_name,
DTL.ACTOR_IMG_URL AS actor_img_url,
DTL.ACTOR_IMG_FILE_NAME AS actor_img_file_name,
DTL.VIEWING_FLAG AS viewing_flag,
(
SELECT
COALESCE(MEMBER_CNT, 1)
FROM
imcsuser.PT_CD_PERSON_MST
WHERE
PERSON_ID = 'G120'
) AS member_cnt
FROM
imcsuser.PT_LB_CUESHEET_ITEM_DETAIL DTL
WHERE
DTL.CUESHEET_ID = 'C202207003'
AND DTL.ITEM_NO = '001'
AND DTL.CAM_NO :: integer BETWEEN 5
AND 24- 튜닝 쿼리
/* cuesheet.base.getCueSheetItemDetailForLayer */
ROW_NUMBER() OVER (
ORDER BY
TO_NUMBER(DTL.CAM_NO)
) as no,
DTL.CUESHEET_ID AS cuesheet_id,
DTL.ITEM_NO AS item_no,
DTL.OMNIV_NO AS omniv_no,
DTL.OMNIV_TYPE AS omniv_type,
DTL.CAM_NO AS cam_no,
DTL.ALBUM_ID AS album_id,
AI.ALBUM_NAME AS album_name,
DTL.ACTOR_ID AS actor_id,
DTL.ACTOR_NAME AS actor_name,
DTL.ACTOR_IMG_URL AS actor_img_url,
DTL.ACTOR_IMG_FILE_NAME AS actor_img_file_name,
DTL.VIEWING_FLAG AS viewing_flag,
(
SELECT
COALESCE(MEMBER_CNT, 1)
FROM
imcsuser.PT_CD_PERSON_MST
WHERE
PERSON_ID = 'G120'
) AS member_cnt
FROM
imcsuser.PT_LB_CUESHEET_ITEM_DETAIL DTL left join imcsuser.PT_LA_ALBUM_INFO AI
on DTL.ALBUM_ID = AI.ALBUM_ID
WHERE
DTL.CUESHEET_ID = 'C202207003'
AND DTL.ITEM_NO = '001'
AND DTL.CAM_NO :: integer BETWEEN 5
AND 24'Postgresql / PPAS > Query tuning' 카테고리의 다른 글
[ 완료]/*new4dSchedule.distr.new4dScheduleListPageData*/ (0) 2022.08.29 [완료]/*abtest.categoryForm.base.getAlbumNameInSeriesAlbum*/ (0) 2022.08.29 [완료] /* content.base.seriesListPageData */ (0) 2022.08.29 [완료]/* abtest.content.base.popupAbtestFormRouteListPageDataSeries */ (0) 2022.08.29 [완료]schedule.distr.newScheduleListPageData (0) 2022.08.29