쿼리 질문입니다....

Question

select distinct onsitemast0_.onsite_master_no as col_0_0_,onsitemast0_.onsite_master_name as col_1_0_,onsitemast0_.onsite_master_type as col_2_0_,onsitemast0_.dept2code as col_3_0_,onsitemast0_.end_date as col_4_0_,deptinfo5_.dept_name as col_5_0_,case when onsitemast0_.end_date>'03/27/2025 10:29:28.690' andsum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end)+sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end)+sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)>0 then '10'when onsitemast0_.end_date>'03/27/2025 10:29:28.690'and sum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end)+sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end)+sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)=0 then '30'when onsitemast0_.end_date<='03/27/2025 10:29:28.690' andsum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end)+sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end)+sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)>0 then '20' else '30' end as col_6_0_,date_format(onsitemast0_.start_date, '%Y-%m-%d %H:%i:%s') as col_7_0_,date_format(onsitemast0_.end_date, '%Y-%m-%d %H:%i:%s') as col_8_0_from t_onsite_master_work onsitemast0_left outer join t_onsite_master_work_device_mapping onsitemast1_ on (onsitemast0_.onsite_master_no=onsitemast1_.onsite_master_no)left outer join t_onsite_master_work_power_station_mapping onsitemast2_ on (onsitemast0_.onsite_master_no=onsitemast2_.onsite_master_no)left outer join t_onsite_master_work_unregister onsitemast3_ on (onsitemast0_.onsite_master_no=cast(onsitemast3_.unregister_no as char))left outer join t_dept_info deptinfo4_ on (deptinfo4_.dept_code=onsitemast0_.dept1code)left outer join t_dept_info deptinfo5_ on (deptinfo5_.dept_code=onsitemast0_.dept2code)left outer join t_dept_info deptinfo6_ on (deptinfo6_.dept_code=onsitemast0_.dept3code)where onsitemast0_.use_yn='Y'and onsitemast0_.dept1code='KTN000'and (onsitemast1_.status not in ('30' , '40') or onsitemast2_.status not in ('30' , '40'))and onsitemast0_.end_date>'03/27/2025 10:29:28.719'group by onsitemast0_.onsite_master_no , onsitemast0_.onsite_master_name , deptinfo4_.dept_name , deptinfo5_.dept_name , deptinfo6_.dept_name해당 쿼리가 데이터가 적으면 많이 느리진 않은데 데이터가 좀 맣으면 상당히 느립니다... 속도를 개선하고 싶은데.... 조언좀 들을수 있을까요

ahuynahcuy · Answer

SELECT om.onsite_master_no AS col_0_0_, om.onsite_master_name AS col_1_0_, om.onsite_master_type AS col_2_0_, om.dept2code AS col_3_0_, om.end_date AS col_4_0_, dept2.dept_name AS col_5_0_, CASE WHEN om.end_date > '2025-03-27 10:29:28.690' AND active_count > 0 THEN '10' WHEN om.end_date > '2025-03-27 10:29:28.690' AND active_count = 0 THEN '30' WHEN om.end_date <= '2025-03-27 10:29:28.690' AND active_count > 0 THEN '20' ELSE '30' END AS col_6_0_, DATE_FORMAT(om.start_date, '%Y-%m-%d %H:%i:%s') AS col_7_0_, DATE_FORMAT(om.end_date, '%Y-%m-%d %H:%i:%s') AS col_8_0_FROM (select * from t_onsite_master_work WHERE om.use_yn = 'Y' AND om.dept1code = 'KTN000' AND om.end_date > '2025-03-27 10:29:28.719') omLEFT JOIN t_onsite_master_work_device_mapping dmap ON om.onsite_master_no = dmap.onsite_master_noLEFT JOIN t_onsite_master_work_power_station_mapping pmap ON om.onsite_master_no = pmap.onsite_master_noLEFT JOIN t_onsite_master_work_unregister unreg ON om.onsite_master_no = CAST(unreg.unregister_no AS CHAR)LEFT JOIN t_dept_info dept2 ON dept2.dept_code = om.dept2codeLEFT JOIN ( SELECT onsite_master_no, SUM(CASE WHEN status NOT IN ('30', '40') THEN 1 ELSE 0 END) AS active_count FROM ( SELECT onsite_master_no, status FROM t_onsite_master_work_device_mapping UNION ALL SELECT onsite_master_no, status FROM t_onsite_master_work_power_station_mapping UNION ALL SELECT CAST(unregister_no AS CHAR) AS onsite_master_no, status FROM t_onsite_master_work_unregister ) AS combined_status GROUP BY onsite_master_no) AS active_status ON om.onsite_master_no = active_status.onsite_master_noGROUP BY om.onsite_master_no, om.onsite_master_name, om.onsite_master_type, om.dept2code, om.end_date, dept2.dept_name, om.start_date

시나몬롤먹고싶다 · Answer

전 보통 쿼리가 지나치게 느리다 싶으면 DISTINCT, LIMIT, 인덱스, OUTER JOIN 쪽을 보는 편입니다실행계획 보시면서 쿼리 튜닝 해보시죠

youngg**** · Answer

DISTINCT 제거했고 limit은 10으로 걸었습니다.

ahuynahcuy · Answer

select onsitemast0_.onsite_master_no as col_0_0_, onsitemast0_.onsite_master_name as col_1_0_, onsitemast0_.onsite_master_type as col_2_0_, onsitemast0_.dept2code as col_3_0_, onsitemast0_.end_date as col_4_0_, deptinfo5_.dept_name as col_5_0_, case when onsitemast0_.end_date>'03/27/2025 10:29:28.690' and sum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end) +sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end) +sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)>0 then '10' when onsitemast0_.end_date>'03/27/2025 10:29:28.690' and sum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end) +sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end) +sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)=0 then '30' when onsitemast0_.end_date<='03/27/2025 10:29:28.690' and sum(case when onsitemast1_.status<>'30' and onsitemast1_.status<>'40' then 1 else 0 end) +sum(case when onsitemast2_.status<>'30' and onsitemast2_.status<>'40' then 1 else 0 end) +sum(case when onsitemast3_.status<>'30' and onsitemast3_.status<>'40' then 1 else 0 end)>0 then '20' else '30' end as col_6_0_, date_format(onsitemast0_.start_date, '%Y-%m-%d %H:%i:%s') as col_7_0_, date_format(onsitemast0_.end_date, '%Y-%m-%d %H:%i:%s') as col_8_0_from (select * from t_onsite_master_work where onsitemast0_.use_yn='Y' and onsitemast0_.dept1code='KTN000' and onsitemast0_.end_date>'03/27/2025 10:29:28.719') onsitemast0_ left outer join t_onsite_master_work_device_mapping onsitemast1_ on (onsitemast0_.onsite_master_no=onsitemast1_.onsite_master_no) left outer join t_onsite_master_work_power_station_mapping onsitemast2_ on (onsitemast0_.onsite_master_no=onsitemast2_.onsite_master_no) left outer join t_onsite_master_work_unregister onsitemast3_ on (onsitemast0_.onsite_master_no=cast(onsitemast3_.unregister_no as char)) left outer join t_dept_info deptinfo4_ on (deptinfo4_.dept_code=onsitemast0_.dept1code) left outer join t_dept_info deptinfo5_ on (deptinfo5_.dept_code=onsitemast0_.dept2code) left outer join t_dept_info deptinfo6_ on (deptinfo6_.dept_code=onsitemast0_.dept3code)where onsitemast1_.status not in ('30' , '40') or onsitemast2_.status not in ('30' , '40')group by onsitemast0_.onsite_master_no , onsitemast0_.onsite_master_name , deptinfo4_.dept_name , deptinfo5_.dept_name , deptinfo6_.dept_name

youngg**** · Answer

DISTINCT는 제거 했고. 기반으로 통계쪽도 짯는데 그것도 느려서 개선하는 중입니다.

톰소여 · Answer

인덱스 걸릴 곳이 onsitemast0_.end_date>'03/27/2025 10:29:28.719' 여기밖에 없는데 확인해 보시고 정보가 없어서 딱히 개선점은 없어보이네요.

쿼리 질문입니다....

6개의 답변

기술 목록