Select 시 12초 정도 나옵니다.. 무엇이 문제인지 잘몰라서요.. 확인좀 부탁드리겠습니다. 0 2 843

by DBA_꿈꾸는개발자 [SQL Query] Mysql Query 튜닝 [2022.05.10 14:06:35]


안녕하세요. spring mybatis로 List 목록을 뿌리는 SQl문입니다. 현재 이 쿼리로 List 목록이 화면에 나타나는 시간이 12초 정도 걸립니다. 아직 SQL에 초보여서, 감이 없어서요, 혹시 이 쿼리를 보고 문제점이 있으시면 ㅠㅠ... 지적 부탁드리겠습니다.

 

SELECT
        COUNT(*)
        FROM T_Car_Repair AS CR
        LEFT JOIN T_Car_Repair_Info AS CRI ON CRI.repair_seq = CR.repair_seq
        LEFT JOIN T_Car AS C ON C.car_seq = CR.car_seq
        LEFT JOIN T_Area AS AR ON AR.area_seq = CR.area_seq
        LEFT JOIN T_Camp AS CP ON CP.camp_seq = CR.camp_seq
        LEFT JOIN T_Car_Equipment AS CE ON CE.car_equipment_seq = CRI.car_equipment_seq
        LEFT JOIN T_Equipment AS E ON E.equipment_seq = CE.equipment_seq
        LEFT JOIN T_Garage AS G ON G.garage_seq = CRI.schedule_garage_seq
        INNER JOIN TB_CODE AS C1 ON CRI.state = C1.code_val AND C1.group_cd = 'AST'
        INNER JOIN TB_CODE AS C3 ON CRI.repair_type = C3.code_val AND C3.group_cd = 'WTY'
        WHERE
      1 = 1
        AND (CR.is_del = 'N' OR CR.is_del IS NULL)
            AND CRI.state NOT IN ('AST00010', 'AST00020', 'AST00030', 'AST00040', 'AST00050')
            AND CR.repair_seq IN (
            SELECT
            CRI.repair_seq
            FROM T_Car_Repair_Info AS CRI
            LEFT JOIN T_Car_Repair AS CR ON CR.repair_seq = CRI.repair_seq
            LEFT JOIN T_Car AS C ON C.car_seq = CR.car_seq
            LEFT JOIN T_Garage_Work AS GW ON GW.work_type = CRI.repair_type
            LEFT JOIN t_garage AS G ON GW.garage_seq = G.garage_seq
            WHERE 1=1
            AND C.singulation_vendor_seq = 115
            AND GW.work_type = 'WTY00030' #상태 : 업체A
            AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115)
            AND (G.is_del = 'N' OR G.is_del IS NULL)

            UNION

            SELECT
            A.repair_seq
            FROM
            (
            SELECT CRI.repair_seq, GW.garage_seq
            FROM T_Car_Repair_Info AS CRI
            LEFT JOIN t_car_equipment AS CE ON CE.car_equipment_seq = CRI.car_equipment_seq
            LEFT JOIN T_Garage_Work AS GW ON GW.equipment_seq = CE.equipment_seq
            WHERE 1=1
            AND GW.work_type ='WTY00090' #상태 : 업체B
            AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115)
            ) AS A,
            (
            SELECT CR.repair_seq, G.garage_seq
            FROM T_Car_Repair AS CR
            LEFT JOIN T_Garage AS G ON G.camp_seq = CR.camp_seq
            WHERE 1=1
            AND G.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115)
            AND (G.is_del = 'N' OR G.is_del IS NULL)
            ) AS B
            WHERE 1=1
            AND A.repair_seq = B.repair_seq
            AND A.garage_seq = B.garage_seq

            UNION

            SELECT
            A.repair_seq
            FROM
            (
            SELECT CRI.repair_seq, GW.garage_seq
            FROM T_Car_Repair_Info AS CRI
            LEFT JOIN T_Garage_Work AS GW ON GW.work_type = CRI.repair_type
            WHERE 1=1
            AND GW.work_type NOT IN ('WTY00030', 'WTY00090') #업체 A, 업체 B
            AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115)
            ) AS A,
            (
            SELECT CR.repair_seq, G.garage_seq
            FROM T_Car_Repair AS CR
            LEFT JOIN T_Garage AS G ON G.camp_seq = CR.camp_seq
            WHERE 1=1
            AND G.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115)
            AND (G.is_del = 'N' OR G.is_del IS NULL)
            ) AS B
            WHERE 1=1
            AND A.repair_seq = B.repair_seq
            AND A.garage_seq = B.garage_seq
            )
by 마농 [2022.05.10 15:16:55]

동일 테이블을 너무 여러번 사용하네요.


by DBA_꿈꾸는개발자 [2022.05.10 16:09:57]

아!! 그렇군요.. 한번 중복 된 건은 삭제 후 해보겠습니다!

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