ABOUT ME

    -

    Today
    -
    Yesterday
    -
    Total
    -
    • [완료] /* content.base.seriesListPageData */
      Postgresql / PPAS/Query tuning 2022. 8. 29. 11:57
      • 개선 포인트
        • AS-IS 수행결과
          Total rows: 1000 of 4534
          Query complete 00:00:03.300
          Ln 153, Col 7
        • TO-BE 수행결과
          Query complete 00:00:01.600
        • 특별히 개선할 포인트가 없지만, 불필요한 구문을 제거하여, 50% 정도의 수행 시간 단축 효과가 있었음.

       

      * 원본 SQL

      SELECT
      /* content.base.seriesListPageData */
      ROW_NUMBER() OVER (ORDER BY AI.CREATE_DATE DESC, AI.SERIES_ID_IMCS, AI.SCREEN_TYPE ) RNUM,
      AI.SERIES_ID_IMCS,
      AI.ALB_SER_TYPE,
      ALP.ALBUM_ID,
      AI.SERIES_NAME,
      AI.SCREEN_TYPE,
      COALESCE(NULLIF(ALS.GENRE_LARGE, ''), '') AS GENRE_LARGE_NAME,
      COALESCE(NULLIF(ALS.GENRE_MID, ''), '') AS GENRE_MID_NAME,
      COALESCE(NULLIF(ALS.GENRE_SMALL, ''), '') AS GENRE_SMALL_NAME,
      COALESCE(NULLIF(
              COALESCE(NULLIF(GENRE_LARGE, ''), '') 
              || CASE WHEN NULLIF(GENRE_MID, '') IS NULL THEN '' ELSE '|' || GENRE_MID END 
              || CASE WHEN NULLIF(GENRE_SMALL, '') IS NULL THEN '' ELSE '|' || GENRE_SMALL END
              ,''), '-') AS GENRE,
      COALESCE(NULLIF(AI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      TO_CHAR(TO_DATE(AI.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD' ) AS CREATE_DATE,
      AI.SERIES_GROUP_CNT,
      COUNT(1) OVER (PARTITION BY AI.SERIES_ID_IMCS) ALBUM_ID_CNT, 
          COALESCE(REGEXP_REPLACE(NULLIF(ALP.MAXIMUM_VIEWING_LENGTH, ''),'[^0-9]','','g') :: INTEGER / 2400,'0') AS MAXIMUM_VIEWING_DAY,
      UFLIX_PROD_YN,
      VIEWING_FLAG
      FROM
      (
      SELECT
      SERIES_ID_IMCS,
      MAX(ALBUM_ID) AS ALBUM_ID,
      MAX(ALB_SER_TYPE) AS ALB_SER_TYPE,
      SCREEN_TYPE,
      COUNT(DISTINCT ALBUM_ID) AS SERIES_GROUP_CNT,
      MAX(RELEASE_DATE) AS RELEASE_DATE,
      MAX(SERIES_NAME) AS SERIES_NAME,
      MAX(SUGGESTED_PRICE) AS SUGGESTED_PRICE,
      MAX(CREATE_DATE) AS CREATE_DATE,
      MAX(UFLIX_PROD_YN) AS UFLIX_PROD_YN,
      MAX(VIEWING_FLAG) AS VIEWING_FLAG
      FROM
      (
      SELECT
      ALI.ALB_SER_TYPE,
      ALI.ALBUM_ID,
      ALI.SERIES_NAME,
      ALI.RELEASE_DATE,
      ALI.SERIES_ID_IMCS,
      ALI.ASSET_ID,
      COALESCE(NULLIF(ALI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      ALI.CREATE_DATE,
      ALI.SCREEN_TYPE,
      CASE
      WHEN COALESCE(NULLIF(C.VOD_NSC_GB, ''), 'NNNN') = 'UFX' THEN 'Y'
      ELSE 'N'
      END UFLIX_PROD_YN,
      P.IMCS_PRODUCT_NAME,
      P.IMCS_EXPIRED_DATE,
      P.IMCS_PRICE,
      (
       CASE
       WHEN ALI.SCREEN_TYPE = 'I' THEN (CASE
      WHEN ALI.ASSET_TYPE = '3D' THEN '1'
      WHEN ALI.ASSET_TYPE = 'HD' THEN '2'
      WHEN ALI.ASSET_TYPE = 'SH' THEN '3'
      WHEN ALI.ASSET_TYPE = 'SD' THEN '4'
      WHEN ALI.ASSET_TYPE = 'U1' THEN '5'
      WHEN ALI.ASSET_TYPE = 'U2' THEN '6'
      WHEN ALI.ASSET_TYPE = 'PR' THEN '7'
      ELSE '8'
      END)
      ELSE ALI.ASSET_TYPE
      END
      ) AS ASSET_TYPE,
      (
      CASE
      WHEN ALI.SCREEN_TYPE = 'N'
      AND C.VOD_CATEGORY_GB = 'NSC' THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
      WHEN ALI.SCREEN_TYPE = 'I'
      AND C.VOD_CATEGORY_GB IN ('I20', 'I30') THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
      ELSE ''
      END
      ) AS VIEWING_FLAG,
      DENSE_RANK () OVER (PARTITION BY SERIES_ID_IMCS, SCREEN_TYPE ORDER BY ALI.ASSET_TYPE ) RNK
      FROM
      (
      SELECT
      (
      CASE
      WHEN NULLIF(SAM.ALBUM_GROUP_ID,
      '') IS NULL THEN 'CON'
      ELSE 'S'
      END
      ) AS ALB_SER_TYPE,
      (
      CASE
      WHEN SAM.ALBUM_GROUP_ID IS NULL
      OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_ID
      ELSE SAM.ALBUM_GROUP_ID
      END
      ) AS SERIES_ID_IMCS,
      (
      CASE
      WHEN SAM.ALBUM_GROUP_ID IS NULL
      OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_NAME
      ELSE SER_MST.ALBUM_GROUP_NM
      END
      ) AS SERIES_NAME,
      ALI.ALBUM_ID,
      ASI.ASSET_ID,
      ALI.RELEASE_DATE,
      COALESCE(NULLIF(ASI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      ASI.CREATE_DATE,
      ASI.SCREEN_TYPE,
      ASI.ASSET_TYPE
      FROM
      imcsuser.PT_LA_ALBUM_INFO ALI
      INNER JOIN imcsuser.PT_LA_ASSET_INFO ASI ON
      ALI.ALBUM_ID = ASI.ALBUM_ID
      LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SAM ON
      ALI.ALBUM_ID = SAM.ALBUM_ID
      AND 'S' = SAM.GROUP_TYPE
      LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_GROUP SER_MST ON
      SAM.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID
      AND SAM.GROUP_TYPE = SER_MST.GROUP_TYPE
      WHERE
      1 = 1
      AND ALI.ALBUM_ID LIKE 'M01211' || '%'
      ) ALI
      LEFT OUTER JOIN voduser.PT_VO_CATEGORY_MAP_UNITED CM ON
      CM.VOD_CONTENTS_ID = ALI.ALBUM_ID
      LEFT OUTER JOIN voduser.PT_VO_CATEGORY_UNITED C ON
      CM.VOD_CATEGORY_ID = C.VOD_CATEGORY_ID
      LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_DETAIL PD ON
      PD.CONTENTS_ID = ALI.ASSET_ID
      LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_UNITED P ON
      PD.PRODUCT_ID = P.IMCS_PRODUCT_ID
      WHERE
      1 = 1
      ) A
      WHERE
      RNK = 1
      GROUP BY
      SERIES_ID_IMCS,
      SCREEN_TYPE
      ) AI
      INNER JOIN imcsuser.PT_LA_ALBUM_SUB ALS ON 
      AI.ALBUM_ID = ALS.ALBUM_ID
      INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALP ON
      AI.ALBUM_ID = ALP.ALBUM_ID
      AND AI.SCREEN_TYPE = ALP.SCREEN_TYPE
      LEFT OUTER JOIN imcsuser.PT_CD_CP_MST CP ON
      ALP.CP_ID = CP.CP_ID
      WHERE
      1 = 1

       

       

      * 튜닝 SQL

      SELECT
      /* content.base.seriesListPageData /
      ROW_NUMBER() OVER (ORDER BY AI.CREATE_DATE DESC, AI.SERIES_ID_IMCS, AI.SCREEN_TYPE ) RNUM,
      AI.SERIES_ID_IMCS,
      AI.ALB_SER_TYPE,
      ALP.ALBUM_ID,
      AI.SERIES_NAME,
      AI.SCREEN_TYPE,
      COALESCE(NULLIF(ALS.GENRE_LARGE, ''), '') AS GENRE_LARGE_NAME,
      COALESCE(NULLIF(ALS.GENRE_MID, ''), '') AS GENRE_MID_NAME,
      COALESCE(NULLIF(ALS.GENRE_SMALL, ''), '') AS GENRE_SMALL_NAME,
      COALESCE(NULLIF(
      COALESCE(NULLIF(GENRE_LARGE, ''), '')
      || CASE WHEN NULLIF(GENRE_MID, '') IS NULL THEN '' ELSE '|' || GENRE_MID END
      || CASE WHEN NULLIF(GENRE_SMALL, '') IS NULL THEN '' ELSE '|' || GENRE_SMALL END
      ,''), '-') AS GENRE,
      COALESCE(NULLIF(AI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      TO_CHAR(TO_DATE(AI.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD' ) AS CREATE_DATE,
      AI.SERIES_GROUP_CNT,
      COUNT(1) OVER (PARTITION BY AI.SERIES_ID_IMCS) ALBUM_ID_CNT,
      COALESCE(REGEXP_REPLACE(NULLIF(ALP.MAXIMUM_VIEWING_LENGTH, ''),'[^0-9]','','g') :: INTEGER / 2400,'0') AS MAXIMUM_VIEWING_DAY,
      UFLIX_PROD_YN,
      VIEWING_FLAG
      FROM
      (
      SELECT
      SERIES_ID_IMCS,
      MAX(ALBUM_ID) AS ALBUM_ID,
      MAX(ALB_SER_TYPE) AS ALB_SER_TYPE,
      SCREEN_TYPE,
      COUNT(DISTINCT ALBUM_ID) AS SERIES_GROUP_CNT,
      MAX(RELEASE_DATE) AS RELEASE_DATE,
      MAX(SERIES_NAME) AS SERIES_NAME,
      MAX(SUGGESTED_PRICE) AS SUGGESTED_PRICE,
      MAX(CREATE_DATE) AS CREATE_DATE,
      MAX(UFLIX_PROD_YN) AS UFLIX_PROD_YN,
      MAX(VIEWING_FLAG) AS VIEWING_FLAG
      FROM
      (
      SELECT
      ALI.ALB_SER_TYPE,
      ALI.ALBUM_ID,
      ALI.SERIES_NAME,
      ALI.RELEASE_DATE,
      ALI.SERIES_ID_IMCS,
      ALI.ASSET_ID,
      COALESCE(NULLIF(ALI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      ALI.CREATE_DATE,
      ALI.SCREEN_TYPE,
      CASE
      WHEN COALESCE(NULLIF(C.VOD_NSC_GB, ''), 'NNNN') = 'UFX' THEN 'Y'
      ELSE 'N'
      END UFLIX_PROD_YN,
      /* 불필요한 부분
      P.IMCS_PRODUCT_NAME,
      P.IMCS_EXPIRED_DATE,
      P.IMCS_PRICE,
      */
      (
      CASE
      WHEN ALI.SCREEN_TYPE = 'I' THEN (CASE
      WHEN ALI.ASSET_TYPE = '3D' THEN '1'
      WHEN ALI.ASSET_TYPE = 'HD' THEN '2'
      WHEN ALI.ASSET_TYPE = 'SH' THEN '3'
      WHEN ALI.ASSET_TYPE = 'SD' THEN '4'
      WHEN ALI.ASSET_TYPE = 'U1' THEN '5'
      WHEN ALI.ASSET_TYPE = 'U2' THEN '6'
      WHEN ALI.ASSET_TYPE = 'PR' THEN '7'
      ELSE '8'
      END)
      ELSE ALI.ASSET_TYPE
      END
      ) AS ASSET_TYPE,
      (
      CASE
      WHEN ALI.SCREEN_TYPE = 'N'
      AND C.VOD_CATEGORY_GB = 'NSC' THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
      WHEN ALI.SCREEN_TYPE = 'I'
      AND C.VOD_CATEGORY_GB IN ('I20', 'I30') THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
      ELSE ''
      END
      ) AS VIEWING_FLAG,
      DENSE_RANK () OVER (PARTITION BY SERIES_ID_IMCS, SCREEN_TYPE ORDER BY ALI.ASSET_TYPE ) RNK
      FROM
      (
      SELECT
      (
      CASE
      WHEN NULLIF(SAM.ALBUM_GROUP_ID,
      '') IS NULL THEN 'CON'
      ELSE 'S'
      END
      ) AS ALB_SER_TYPE,
      (
      CASE
      WHEN SAM.ALBUM_GROUP_ID IS NULL
      OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_ID
      ELSE SAM.ALBUM_GROUP_ID
      END
      ) AS SERIES_ID_IMCS,
      (
      CASE
      WHEN SAM.ALBUM_GROUP_ID IS NULL
      OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_NAME
      ELSE SER_MST.ALBUM_GROUP_NM
      END
      ) AS SERIES_NAME,
      ALI.ALBUM_ID,
      ASI.ASSET_ID,
      ALI.RELEASE_DATE,
      COALESCE(NULLIF(ASI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
      ASI.CREATE_DATE,
      ASI.SCREEN_TYPE,
      ASI.ASSET_TYPE
      FROM
      imcsuser.PT_LA_ALBUM_INFO ALI
      INNER JOIN imcsuser.PT_LA_ASSET_INFO ASI ON
      ALI.ALBUM_ID = ASI.ALBUM_ID
      LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SAM ON
      ALI.ALBUM_ID = SAM.ALBUM_ID
      AND 'S' = SAM.GROUP_TYPE
      LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_GROUP SER_MST ON
      SAM.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID
      AND SAM.GROUP_TYPE = SER_MST.GROUP_TYPE
      WHERE
      1 = 1
      AND ALI.ALBUM_ID LIKE 'M01211' || '%'
      ) ALI
      LEFT OUTER JOIN voduser.PT_VO_CATEGORY_MAP_UNITED CM ON
      CM.VOD_CONTENTS_ID = ALI.ALBUM_ID
      LEFT OUTER JOIN voduser.PT_VO_CATEGORY_UNITED C ON
      CM.VOD_CATEGORY_ID = C.VOD_CATEGORY_ID
      /* 불필요한 부분
      LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_DETAIL PD ON
      PD.CONTENTS_ID = ALI.ASSET_ID
      LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_UNITED P ON
      PD.PRODUCT_ID = P.IMCS_PRODUCT_ID
      */
      WHERE
      1 = 1
      ) A
      WHERE
      RNK = 1
      GROUP BY
      SERIES_ID_IMCS,
      SCREEN_TYPE
      ) AI
      INNER JOIN imcsuser.PT_LA_ALBUM_SUB ALS ON
      AI.ALBUM_ID = ALS.ALBUM_ID
      INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALP ON
      AI.ALBUM_ID = ALP.ALBUM_ID
      AND AI.SCREEN_TYPE = ALP.SCREEN_TYPE
      LEFT OUTER JOIN imcsuser.PT_CD_CP_MST CP ON
      ALP.CP_ID = CP.CP_ID
      WHERE
      1 = 1
      저작자표시 비영리 변경금지

      'Postgresql / PPAS > Query tuning' 카테고리의 다른 글

      [완료]/*abtest.categoryForm.base.getAlbumNameInSeriesAlbum*/  (0) 2022.08.29
      [완료]/* cuesheet.base.getCueSheetItemDetailForLayer */  (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

      관련글 관련글 더보기

    Designed by Tistory.

    티스토리툴바