Query Optimizing Request
- by mithilatw
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 update a MSSQL table call component every 10 minutes based on information from another table call materials_progress
I have nearly 60000 records in component and more than 10000 records in materials_progress
I wrote an update query to do the job, but it takes longer than 4 minutes to complete execution!
Here is the query :
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!!!
