쿼리 속도 관련해서 질문드립니다. 0 3 149

by 김태민 [2018.03.13 13:42:04]


안녕하세요.

작업중인 쿼리 속도 문제로 질문드립니다.

이것도 나름대로 연구하여 속도를 줄인다고 줄여봤지만,

여전히 속도문제가 있어서 더 좋은 방법을 알고 싶어서 조언을 구합니다.

데이터는 총 10000건 정도 됩니다.

 

아래 1번 테이블이 조회시 속도가 나오지 않는 쿼리입니다.

혹시 몰라서 View도 같이 적어뒀습니다.

 

감사합니다.

//------------------------------------------------------------------------------------------------------------------------------------------------------


1. 사용중인 조회쿼리

SELECT
        c.COMPANY_IDX
        ,c.NAME
        , c.COMPANY_NUM1
        , c.COMPANY_NUM2
        , c.CEO_NAME
        , (SELECT CODE_VALUE FROM V_CODE_ACTIVE V1 WHERE V1.CODE_IDX = (SELECT PARENT_CODE_IDX FROM V_CODE_ACTIVE V2 WHERE V2.CODE_VALUE = c.CLUSTER_VALUE))AS CLUSTER_VALUE1
        , c.CLUSTER_VALUE AS CLUSTER_VALUE2
        , c.FOUNDATION_DATE
        , c.OFFICE_ZIP_CODE
        , c.OFFICE_ADDRESS1
        , c.OFFICE_ADDRESS2
        , c.OFFICE_PHONE
        , c.OFFICE_FAX
        , c.CENTER_IDX
        , (SELECT LOCATION FROM TBL_CENTER WHERE c.CENTER_IDX = CENTER_IDX) as LOCATION
        , c.INDUSTRY
        , c.PRODUCT
        , c.INDUSTRY_DEPART_NAME1
        , c.INDUSTRY_DEPART_NAME2
        , c.INDUSTRY_DEPART_NAME3
        , c.INDUSTRY_DEPART_NAME4
        , 
          (SELECT CODE_TEXT FROM TBL_CODE WHERE CODE_VALUE = c.INDUSTRY_DEPART_NAME4) as industry_complex
        , c.HOMEPAGE
        , TO_CHAR(c.REGIST_DATE, 'yyyy-mm-dd') as REGIST_DATE
        , c.REGIST_MEMBER_IDX
                , (SELECT name FROM TBL_MEMBER m WHERE c.REGIST_MEMBER_IDX = m.MEMBER_IDX)AS REGIST_MEMBER_NAME
        , TO_CHAR(c.UPDATE_DATE, 'yyyy-mm-dd') as UPDATE_DATE
        , c.UPDATE_MEMBER_IDX
        , c.IS_DEL
        , c.PM_DOCTOR_MEMBER_IDX
        , c.MANAGEMENT_DOCTOR_MEMBER_IDX
        , c.FINANCE_DOCTOR_MEMBER_IDX
        , c.TECHNOLOGY_DOCTOR_MEMBER_IDX
        , ( (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.PM_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.MANAGEMENT_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.FINANCE_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.TECHNOLOGY_DOCTOR_MEMBER_IDX) ) AS DOCTOR_NAME
            
        , (SELECT name FROM TBL_MEMBER m WHERE c.PM_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS PM_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.MANAGEMENT_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS MANAGEMENT_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.FINANCE_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS FINANCE_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.TECHNOLOGY_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS TECHNOLOGY_DOCTOR_MEMBER_NAME
        
        , c.COMPANY_SCALE_TYPE
        , c.COMPANY_TYPE
        , (SELECT CODE_VALUE FROM V_CODE_ACTIVE V1 WHERE V1.CODE_IDX = (SELECT PARENT_CODE_IDX FROM V_CODE_ACTIVE V2 WHERE V2.CODE_VALUE = c.INDUSTRY_VALUE))AS INDUSTRY_VALUE1
        , c.INDUSTRY_VALUE AS INDUSTRY_VALUE2
        , c.INDUSTRY_ETC_VALUE
        , (SELECT TO_DATE(MAX(CHOICE_DATE), 'yyyy-mm-dd')
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE = 'IA'
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX) as PROMOTE_DATE
        , (SELECT ADD_MONTHS(MAX(CHOICE_DATE), 36) -1
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE = 'IA'
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX)as SCHEDULED_END_PROMOTE_DATE
        , (SELECT TO_DATE(MAX(CHOICE_DATE), 'yyyy-mm-dd')
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE IN ('GA')
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX)AS GRADUATE_DATE
        , i.SELL_1_YEAR
        , i.SELL_2_YEAR
        , i.SELL_3_YEAR
        , i.SELL_1_PRICE
        , i.SELL_2_PRICE
        , i.SELL_3_PRICE
        , i.MAKE_1_YEAR
        , i.MAKE_2_YEAR
        , i.MAKE_3_YEAR
        , i.MAKE_1_PRICE
        , i.MAKE_2_PRICE
        , i.MAKE_3_PRICE
        , i.OUT_1_YEAR
        , i.OUT_2_YEAR
        , i.OUT_3_YEAR
        , i.OUT_1_PRICE
        , i.OUT_2_PRICE
        , i.OUT_3_PRICE
        , i.EMP_1_YEAR
        , i.EMP_2_YEAR
        , i.EMP_3_YEAR
        , i.EMP_1_NUMBER
        , i.EMP_2_NUMBER
        , i.EMP_3_NUMBER
        , i.PROFIT_1_YEAR
        , i.PROFIT_2_YEAR
        , i.PROFIT_3_YEAR
        , i.PROFIT_1_PRICE
        , i.PROFIT_2_PRICE
        , i.PROFIT_3_PRICE
        , i.PATENT_1_YEAR
        , i.PATENT_2_YEAR
        , i.PATENT_3_YEAR
        , i.PATENT_1_NUMBER
        , i.PATENT_2_NUMBER
        , i.PATENT_3_NUMBER
        , i.RESEARCH_1_YEAR
        , i.RESEARCH_2_YEAR
        , i.RESEARCH_3_YEAR
        , i.RESEARCH_1_NUMBER
        , i.RESEARCH_2_NUMBER
        , i.RESEARCH_3_NUMBER
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type1'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as LONG_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type2'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as MISSION_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type3'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as CONSULT_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type4'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as SHORT_CNT

    , (SELECT
        MAX(CHOICE_DATE) CHOICE_DATE
      from TBL_company_promote_hist cph
      where promote_type = 'GA'
        AND cph.company_idx = c.company_idx
        AND NOT EXISTS ( SELECT
                              1
                          FROM TBL_COMPANY_PROMOTE_HIST
                          WHERE cph.company_idx = company_idx
                            and promote_type = 'GU')
        GROUP BY COMPANY_IDX, CHOICE_DATE) as CHOICE_DATE
FROM tbl_company c
   , TBL_COMPANY_ADD_PRICE_INFO i
WHERE c.COMPANY_IDX = i.COMPANY_IDX(+)
;


2. 참조하는 View

CREATE OR REPLACE FORCE VIEW V_CODE_ACTIVE
( CODE_IDX, PARENT_CODE_IDX, CODE_VALUE, CODE_TEXT, 
  REG_DATE, IS_DEL, DISPLAY_ORDER_BY, USE_YN )
AS
SELECT "CODE_IDX","PARENT_CODE_IDX","CODE_VALUE","CODE_TEXT","REG_DATE","IS_DEL","DISPLAY_ORDER_BY","USE_YN" FROM tbl_code
WHERE IS_DEL = 0
;

 

by 우리집아찌 [2018.03.13 13:53:42]

똑같은 테이블을 억세스하는 스칼라 서브 쿼리가 너무 많네요.

원하는 데이터를 그룹핑해서 INLINE 뷰형태로 만들어 OUTER JOIN으로 가져오셔야 할듯합니다.


by 우리집아찌 [2018.03.13 14:04:51]
-- 예를 들자면 
SELECT
     .
     . 
     .
     .
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type1'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as LONG_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type2'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as MISSION_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type3'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as CONSULT_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type4'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as SHORT_CNT  
      .
      .
      .
FROM tbl_company c
   , TBL_COMPANY_ADD_PRICE_INFO i
WHERE c.COMPANY_IDX = i.COMPANY_IDX(+)

------ 바꾸면 -------

 SELECT
     .
     . 
     .
     .
     T1.LONG_CNT
    ,T1.MISSION_CNT
    ,T1.CONSULT_CNT
    ,T1.SHORT_CNT
FROM tbl_company c
   , TBL_COMPANY_ADD_PRICE_INFO i
   , ( SELECT COMPANY_IDX 
	       , COUNT(CASE WHEN PROJECT_TYPE = 'promote_type1' THEN 1 END )) LONG_CNT
	       , COUNT(CASE WHEN PROJECT_TYPE = 'promote_type2' THEN 1 END )) MISSION_CNT
	       , COUNT(CASE WHEN PROJECT_TYPE = 'promote_type3' THEN 1 END )) CONSULT_CNT
	       , COUNT(CASE WHEN PROJECT_TYPE = 'promote_type4' THEN 1 END )) SHORT_CNT
            FROM TBL_PROJECTMANAGER
           WHERE RUN_STATE = 0
           GROUP BY COMPANY_IDX
	) T1
WHERE c.COMPANY_IDX = i.COMPANY_IDX(+)
   AND c.COMPANY_IDX = T1.COMPANY_IDX(+)

 


by 마농 [2018.03.13 14:26:14]

스칼라서브쿼리 부분 전부 다 주석 처리 하시고
주석 하나씩 풀어가면서 느려지는 부분 찾아보세요.
느린 부분 찾으셨으면 해당 테이블에 적절한 인덱스 있는지 확인하시고
스칼라서브쿼리를 아우터 조인 형태로 바꿀 수 있는지 검토해 보세요.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입