[완료]/* cuesheet.base.getCueSheetItemDetailForLayer */

원샷원따봉 2022. 8. 29. 12:09

Select 절 내의 스칼라 서브 쿼리를 JOIN문으로 대처

AS-IS
2.5 sec

TO-BE
150ms

원본 쿼리

SELECT
/* 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

튜닝 쿼리

SELECT
/* 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

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