I have a query that is taking over 500 seconds to run. I managed to figure out the culprit to be my 2nd left join in the below query. When I comment out that left join, and the two columns I select from that table the query takes 3 seconds. When I run it as I need it, with nothing commented out, it takes over 500 seconds. Is there anyway to refactor the 2nd left join statement into this query? My guess is no but I had to try. This is pervasive and as far as I know the tables are indexed but not in any convenient way. Our vendor doesn't allow us to mess with the indexes either unfortunately. Any help is greatly appreciated! If all else fails, I think I will use a 2nd query in my php application to get the info I need.
Query
select concat(concat(job_header.job,'-'),job_header.suffix) as Job,job_header.part,job_header.qty_order,
sum(case when job_operations_wc.workcenter = '0750' then job_operations_wc.hours_actual end) as WaterJet,
sum(case when job_operations_wc.workcenter IN ('0705','0710','0715') then job_operations_wc.hours_actual end) as Laser,
sum(case when job_operations_wc.workcenter = ('1006') then job_operations_wc.hours_actual end) as Prep,
sum(case when job_operations_wc.workcenter IN ('1310','0755') then job_operations_wc.hours_actual end) as Machining,
sum(case when job_operations_wc.workcenter IN ('1002','1003') then job_operations_wc.hours_actual end) as Fab,
sum(case when job_operations_wc.workcenter = '1100' then job_operations_wc.hours_actual end) as Paint,
sum(case when job_operations_wc.workcenter = '1000' then job_operations_wc.hours_actual end) as Belts,
sum(case when job_operations_wc.workcenter = '1001' then job_operations_wc.hours_actual end) as Electrical,
sum(case when job_operations_wc.workcenter = '1520' then job_operations_wc.hours_actual end) as Crating_Skids,
sum(case when job_operations_wc.workcenter IN ('1004','1005','1350','1201') then job_operations_wc.hours_actual end) as Final_Assy,
sum(job_operations_wc.hours_estimated) as total_hours_estimated
/* min(gab_source_cause_codes.source) as source,
min(gab_source_cause_codes.cause) as cause */
from job_header
left join job_operations_wc on job_operations_wc.job = job_header.job and job_header.suffix = job_operations_wc.suffix
/* left join gab_source_cause_codes on gab_source_cause_codes.job = job_operations_wc.job and gab_source_cause_codes.suffix = job_operations_wc.suffix and gab_source_cause_codes.seq = job_operations_wc.seq */
where job_header.product_line = '05' and job_header.date_closed > '000000' and job_operations_wc.LMO = 'L' and job_operations_wc.seq < '99000'
group by Job,job_header.part,job_header.qty_order