I am very sorry if this question is structured in not a very helpful manner or the question itself is not a very good one!
I need to
component every 10 minutes based on information from another
I have nearly 60000 records in
component and more than 10000 records in
I wrote an
update query to do the job, but it takes longer than 4 minutes to complete execution!
Here is the
UPDATE component SET stage_id = CASE WHEN t.required_quantity <= t.total_received THEN 27 WHEN t.total_ordered < t.total_received THEN 18 ELSE 18 END FROM ( SELECT mp.job_id, mp.line_no, mp.component, l.quantity AS line_quantity, CASE WHEN mp.component_name_id = 2 THEN l.quantity*2 ELSE l.quantity END AS required_quantity, SUM(ordered) AS total_ordered, SUM(received) AS total_received , c.component_id FROM line l LEFT JOIN component c ON c.line_id = l.line_id LEFT JOIN materials_progress mp ON l.job_id = mp.job_id AND l.line_no = mp.line_no AND c.component_name_id = mp.component_name_id WHERE mp.job_id IS NOT NULL AND (mp.cancelled IS NULL OR mp.cancelled = 0) AND (mp.manual_override IS NULL OR mp.manual_override = 0) AND c.stage_id = 18 GROUP BY mp.job_id, mp.line_no, mp.component, l.quantity, mp.component_name_id, component_id ) AS t WHERE component.component_id = t.component_id
I am not going to explain the scenario as it too complex.. could somebody please please tell me what makes this query this much expensive and a way to get around it?
Thank you very very much in advance!!!
Edit 1: Here is the execution plan;