깨비
98
2019-01-24 10:42:08 작성 2019-01-24 10:44:32 수정됨
10
711

안녕하세요 쿼리 성능 질문좀 드리고자합니다!


SELECT Z.TIME_STR

    , A.ACMSR_GEN

    , A.PREDICT_GEN

    , A.GEN_EROR_RATE

    , A.ACMSR_GEN_OUTPT

    , A.PREDICT_GEN_OUTPT

    , A.GEN_EROR_RATE_OUTPT

    , A.ACMSR_GEN_OUTPT_MAX

    , A.ACMSR_GEN_OUTPT_MIN

, A.ACMSR_GEN_OUTPT_AVG

    , B.ACMSR_WS

    , B.PREDICT_WS

    , B.WS_EROR_RATE

    , B.ACMSR_SOLRAD

    , B.PREDICT_SOLRAD

    , B.SOLRAD_EROR_RATE

    , B.ACMSR_TP

        , B.PREDICT_TP

        , B.TP_EROR_RATE

FROM TB_CM_MS_TIMEFORM Z

LEFT JOIN  (

             SELECT '봄' AS DATE_M

                   , SUM(ACMSR_GEN) AS ACMSR_GEN

                   , SUM(PREDICT_GEN) AS PREDICT_GEN

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN),SUM(PREDICT_GEN),2) AS GEN_EROR_RATE

                   , ROUND(MAX(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MAX

                   , ROUND(MIN(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MIN

                   , ROUND(AVG(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_AVG

                   , SUM(ACMSR_GEN_OUTPT) AS ACMSR_GEN_OUTPT

                   , SUM(PREDICT_GEN_OUTPT) AS PREDICT_GEN_OUTPT

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN_OUTPT),SUM(PREDICT_GEN_OUTPT),2) AS GEN_EROR_RATE_OUTPT

              FROM TB_PF_MS_SRCELCTGEN A

              WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

              AND CYCLE = '1HOUR'

              AND TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('03', '04', '05')

              UNION ALL

              SELECT '여름' AS DATE_M

                   , SUM(ACMSR_GEN) AS ACMSR_GEN

                   , SUM(PREDICT_GEN) AS PREDICT_GEN

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN),SUM(PREDICT_GEN),2) AS GEN_EROR_RATE

                   , SUM(ACMSR_GEN_OUTPT) AS ACMSR_GEN_OUTPT

                   , ROUND(MAX(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MAX

                   , ROUND(MIN(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MIN

                   , ROUND(AVG(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_AVG

                   , SUM(PREDICT_GEN_OUTPT) AS PREDICT_GEN_OUTPT

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN_OUTPT),SUM(PREDICT_GEN_OUTPT),2) AS GEN_EROR_RATE_OUTPT

              FROM TB_PF_MS_SRCELCTGEN A

              WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

              AND CYCLE = '1HOUR'

              AND TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('06', '07', '08')

              UNION ALL

              SELECT '가을' AS DATE_M

                   , SUM(ACMSR_GEN) AS ACMSR_GEN

                   , SUM(PREDICT_GEN) AS PREDICT_GEN

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN),SUM(PREDICT_GEN),2) AS GEN_EROR_RATE

                   , ROUND(MAX(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MAX

                   , ROUND(MIN(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MIN

                   , ROUND(AVG(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_AVG

                   , SUM(ACMSR_GEN_OUTPT) AS ACMSR_GEN_OUTPT

                   , SUM(PREDICT_GEN_OUTPT) AS PREDICT_GEN_OUTPT

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN_OUTPT),SUM(PREDICT_GEN_OUTPT),2) AS GEN_EROR_RATE_OUTPT

              FROM TB_PF_MS_SRCELCTGEN A

              WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

              AND CYCLE = '1HOUR'

              AND TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('09', '10')

              UNION ALL

              SELECT '겨울' AS DATE_M

                   , SUM(ACMSR_GEN) AS ACMSR_GEN

                   , SUM(PREDICT_GEN) AS PREDICT_GEN

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN),SUM(PREDICT_GEN),2) AS GEN_EROR_RATE

                   , ROUND(MAX(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MAX

                   , ROUND(MIN(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_MIN

                   , ROUND(AVG(ACMSR_GEN_OUTPT),2) AS ACMSR_GEN_OUTPT_AVG

                   , SUM(ACMSR_GEN_OUTPT) AS ACMSR_GEN_OUTPT

                   , SUM(PREDICT_GEN_OUTPT) AS PREDICT_GEN_OUTPT

                   , FN_GET_EROR_RATE(SUM(ACMSR_GEN_OUTPT),SUM(PREDICT_GEN_OUTPT),2) AS GEN_EROR_RATE_OUTPT

              FROM TB_PF_MS_SRCELCTGEN A

              WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

              AND CYCLE = '1HOUR'

              AND TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('11', '12', '01','02')

            ) A ON (Z.TIME = A.DATE_M)

LEFT JOIN (

            SELECT '봄' AS DATE_M

                 , ROUND(AVG(ACMSR_WS),2) AS ACMSR_WS

                 , ROUND(AVG(PREDICT_WS),2) AS PREDICT_WS

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_WS),2),ROUND(AVG(PREDICT_WS),2),2) AS WS_EROR_RATE

                 , ROUND(AVG(ACMSR_SOLRAD),2) AS ACMSR_SOLRAD

                 , ROUND(AVG(PREDICT_SOLRAD),2) AS PREDICT_SOLRAD

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_SOLRAD),2),ROUND(AVG(PREDICT_SOLRAD),2),2) AS SOLRAD_EROR_RATE

                , ROUND(AVG(ACMSR_TP),2) AS ACMSR_TP

                 , ROUND(AVG(PREDICT_TP),2) AS PREDICT_TP

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_TP),2),ROUND(AVG(PREDICT_TP),2),2) AS TP_EROR_RATE

            FROM  TB_PF_MS_SRCELCTWETHER

            WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

            AND   CYCLE = '1HOUR'

            AND   TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('03', '04', '05')

            AND   GEN_ID = '960' -- 임시 코드

            UNION ALL

            SELECT '여름' AS DATE_M

                 , ROUND(AVG(ACMSR_WS),2) AS ACMSR_WS

                 , ROUND(AVG(PREDICT_WS),2) AS PREDICT_WS

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_WS),2),ROUND(AVG(PREDICT_WS),2),2) AS WS_EROR_RATE

                 , ROUND(AVG(ACMSR_SOLRAD),2) AS ACMSR_SOLRAD

                 , ROUND(AVG(PREDICT_SOLRAD),2) AS PREDICT_SOLRAD

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_SOLRAD),2),ROUND(AVG(PREDICT_SOLRAD),2),2) AS SOLRAD_EROR_RATE

                 , ROUND(AVG(ACMSR_TP),2) AS ACMSR_TP

                 , ROUND(AVG(PREDICT_TP),2) AS PREDICT_TP

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_TP),2),ROUND(AVG(PREDICT_TP),2),2) AS TP_EROR_RATE

            FROM  TB_PF_MS_SRCELCTWETHER

            WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

            AND   CYCLE = '1HOUR'

            AND   TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('06', '07', '08')

            AND   GEN_ID = '960' -- 임시 코드 

                    UNION ALL

            SELECT '가을' AS DATE_M

                 , ROUND(AVG(ACMSR_WS),2) AS ACMSR_WS

                 , ROUND(AVG(PREDICT_WS),2) AS PREDICT_WS

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_WS),2),ROUND(AVG(PREDICT_WS),2),2) AS WS_EROR_RATE

                 , ROUND(AVG(ACMSR_SOLRAD),2) AS ACMSR_SOLRAD

                 , ROUND(AVG(PREDICT_SOLRAD),2) AS PREDICT_SOLRAD

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_SOLRAD),2),ROUND(AVG(PREDICT_SOLRAD),2),2) AS SOLRAD_EROR_RATE

                 , ROUND(AVG(ACMSR_TP),2) AS ACMSR_TP

                 , ROUND(AVG(PREDICT_TP),2) AS PREDICT_TP

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_TP),2),ROUND(AVG(PREDICT_TP),2),2) AS TP_EROR_RATE

            FROM  TB_PF_MS_SRCELCTWETHER

            WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

            AND   CYCLE = '1HOUR'

            AND   TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('09', '10')

            AND   GEN_ID = '960'  -- 임시코드

                          UNION ALL

            SELECT '겨울' AS DATE_M

                 , ROUND(AVG(ACMSR_WS),2) AS ACMSR_WS

                 , ROUND(AVG(PREDICT_WS),2) AS PREDICT_WS

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_WS),2),ROUND(AVG(PREDICT_WS),2),2) AS WS_EROR_RATE

                 , ROUND(AVG(ACMSR_SOLRAD),2) AS ACMSR_SOLRAD

                 , ROUND(AVG(PREDICT_SOLRAD),2) AS PREDICT_SOLRAD

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_SOLRAD),2),ROUND(AVG(PREDICT_SOLRAD),2),2) AS SOLRAD_EROR_RATE

                 , ROUND(AVG(ACMSR_TP),2) AS ACMSR_TP

                 , ROUND(AVG(PREDICT_TP),2) AS PREDICT_TP

                 , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_TP),2),ROUND(AVG(PREDICT_TP),2),2) AS TP_EROR_RATE

            FROM  TB_PF_MS_SRCELCTWETHER

            WHERE TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'YYYY') = '2019'

            AND   CYCLE = '1HOUR'

            AND   TO_CHAR(TO_DATE(DATA_YMD,'YYYYMMDD'),'MM') IN ('11', '12', '01','02')

            AND   GEN_ID = '960'  -- 임시 코드

          ) B ON (Z.TIME = B.DATE_M)

WHERE Z.FORM_TY = 'SEASON'

ORDER BY Z.TIME_ORDR


봄여름가을겨울별로 합계데이터를 조회하는데

4~5초정도 나오더라구요

여기서 성능을 개선하고 싶은데 어느부분부터 수정해야할지 감이 잘 안옵니다.

도움 부탁드리겠습니다!

0
0
  • 답변 10

  • ....
    2019-01-24 10:56:51

    전 이렇게 서브쿼리안에 서브쿼리 넣는 것 싫어합니다.ㄷㄷ

    서브 사이드 언어인 자바에서 처리후 디비에 값을 지정하는 것은 어떨가요?


    가져오는 데이터 항목과

    조인한 테이블이 무엇무엇이 있는 건가요?



    0
  • 깨비
    98
    2019-01-24 11:03:38

    가져오는 데이터 항목은 A테이블의 합계,정확율,최대,최소데이터 B테이블의 평균,정확율,최대,최소 데이터구요 

    조인한 테이블은 TIME_FORM이라는 봄,여름,가을,겨울 항목이 들어있는 테이블입니다. 


    한 테이블안에 봄 데이터가 3,4,5월 여름이 6,7,8월 가을이 9,10월 겨울이 11,12,1,2월로 각각 뽑아야 해서 유니온으로 쌓았습니다.

    0
  • ....
    2019-01-24 11:08:21 작성 2019-01-24 11:20:15 수정됨

    일단 한가지 말씀드릴께요

    이것부터 수정해주세요

    같은 테이블을 연결할때는 UNION ALL 쓰지말고

    필드값으로 여름인지 겨울인지 따로 체크하시길 바랍니다.


    서브쿼리 하나당 부하가 큽니다.

    근데 서브쿼리가 너무 많습니다.

    서브쿼리를 줄이시기를 바랍니다.

    가능하다면 없애는 것을 추천드립니다.


    또한 조인으로 바꾸시길 바랍니다.

    조인의 연결하는 테이블 갯수는 최대 3개로 하시길 바랍니다.

    0
  • basscraft
    2k
    2019-01-24 11:09:25

    이런 질문을 커뮤니티에 올리시는게 참 이해가 안가요... 

    테이블 명세도 없고 요구 조건도 제대로 설명 없이...

    본인 이라면 답변을 쓰실 수 있을까 생각을 해보심이...


    일단 DATA_YMD 에 인덱스가 존재 하는지 물어 보고 싶네요 (없다면 추가를 검토해 보시고)

    WHERE DATA_YMD BETWEEN '20190101' AND '20191231'

    계절 별로 월을 비교하는 부분은

    CASE ~ WHEN ~ THEN ~ END 구분으로 바꾸시고 UNION ALL 을 제거 하시는게 일단 한가지 방법 같습니다...


    난 왜 이것을 대답하고 있는가... ㅠㅠ


    1
  • 메로메로
    261
    2019-01-24 11:12:33


    SELECT Z.TIME_STR
        , A.ACMSR_GEN
        , A.PREDICT_GEN
        , A.GEN_EROR_RATE
        , A.ACMSR_GEN_OUTPT
        , A.PREDICT_GEN_OUTPT
        , A.GEN_EROR_RATE_OUTPT
        , A.ACMSR_GEN_OUTPT_MAX
        , A.ACMSR_GEN_OUTPT_MIN
        , A.ACMSR_GEN_OUTPT_AVG
        , B.ACMSR_WS
        , B.PREDICT_WS
        , B.WS_EROR_RATE
        , B.ACMSR_SOLRAD
        , B.PREDICT_SOLRAD
        , B.SOLRAD_EROR_RATE
        , B.ACMSR_TP
        , B.PREDICT_TP
        , B.TP_EROR_RATE
      FROM TB_CM_MS_TIMEFORM Z LEFT OUTER JOIN 
        (
          SELECT CASE
                   WHEN TO_CHAR(TO_DATE(DATA_YMD, 'YYYYMMDD'), 'MM') IN ('03', '04', '05') THEN '봄'
                   WHEN TO_CHAR(TO_DATE(DATA_YMD, 'YYYYMMDD'), 'MM') IN ('06', '07', '08') THEN '여름'
                   WHEN TO_CHAR(TO_DATE(DATA_YMD, 'YYYYMMDD'), 'MM') IN ('09', '10') THEN '가을'
                   WHEN TO_CHAR(TO_DATE(DATA_YMD, 'YYYYMMDD'), 'MM') IN ('11', '12', '01', '02') THEN '겨울'
            END                                                                                  AS DATE_M
               , ROUND(AVG(ACMSR_WS), 2)                                                         AS ACMSR_WS
               , ROUND(AVG(PREDICT_WS), 2)                                                       AS PREDICT_WS
               , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_WS), 2), ROUND(AVG(PREDICT_WS), 2), 2)         AS WS_EROR_RATE
               , ROUND(AVG(ACMSR_SOLRAD), 2)                                                     AS ACMSR_SOLRAD
               , ROUND(AVG(PREDICT_SOLRAD), 2)                                                   AS PREDICT_SOLRAD
               , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_SOLRAD), 2), ROUND(AVG(PREDICT_SOLRAD), 2), 2) AS SOLRAD_EROR_RATE
               , ROUND(AVG(ACMSR_TP), 2)                                                         AS ACMSR_TP
               , ROUND(AVG(PREDICT_TP), 2)                                                       AS PREDICT_TP
               , FN_GET_EROR_RATE(ROUND(AVG(ACMSR_TP), 2), ROUND(AVG(PREDICT_TP), 2), 2)         AS TP_EROR_RATE
          FROM TB_PF_MS_SRCELCTWETHER
          WHERE DATA_YMD LIKE '2019%'
            AND CYCLE = '1HOUR'
            AND GEN_ID = '960' -- 임시 코드
        ) B
       ON Z.TIME = B.DATE_M
    WHERE Z.FORM_TY = 'SEASON'
    ORDER BY Z.TIME_ORDR

    UNION ALL 많이 쓰는건 좋은 방법은 아닙니다. 저렇게 짰다가 오류낸거 많이 보았습니다.

    그리고 제가 약간 수정해보았습니다만 데이터를 보지 않아서 솔직히 효과가 있는지는 모릅니다.


    마지막으로 거북왕님 의견에 동감합니다.

    1
  • 깨비
    98
    2019-01-24 11:14:37

    답변주신분들 정말 감사합니다 ㅠㅠ

    제가 아직 많이 부족하네요.

    공부 많이 하겠습니다.

    0
  • 메로메로
    261
    2019-01-24 11:15:40
    짜신걸 근거로 고치긴 했는데, 어디까지나 예시입니다. 직접 검증하시면서 보셔야 합니다.
    0
  • 깨비
    98
    2019-01-24 11:19:07

    넵 감사합니다.

    0
  • basscraft
    2k
    2019-01-24 11:21:14

    UNION ALL 제거도 중요한 요소이긴 하지만...

    제 생각에는 성능에 큰 영향을 주지는 않을 것 같구요

    DATA_YMD 컬럼의 인덱스 존재 여부, 존재 하지 않는다면 데이터 분포도를 파악하셔서 인덱스를 생성하시길 권합니다. 사실 이 부분은 개발자 영역이라기 보다 DBA 영역이라고 보고 DBA랑 상의 해 보시고

    인덱스가 이미 존재하거나 생성을 하셨다면...

    인덱스 컬럼을 가공하지 않아야 하는 이유에 대해서 구글링을 해보시고,

    가능하면 좌변을 가공하지 않는 습관을 들이시는게 중요한 포인트 같습니다.

    0
  • 깨비
    98
    2019-01-24 11:32:03

    넵 좌변 가공하지 않는 습관 명심하겠습니다.

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.