Query Optimizing Request

Question

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!!!

Edit 1: Here is the execution plan;

Answer 1

There are joins used in the query. Also, too many conditions put in WHERE Clause. You can create Indexes as per query execution plan.

Refer: Execution Plan

EDIT: Following Indexes might be helpful:

INDEX on component_id column of component table
INDEX ON job_id, line_no, component_name_id column of materials_progress table

Answer 2

I am not sure your logic behind those joins but some part of the query can be optimised as; (for ex; WHEN t.total_ordered < t.total_received THEN 18 of your first CASE does not make sense at all as ELSE part is also equal to18`)

EDIT: Sub query brought back in with some optimisations. Syntax looks alright now.

UPDATE c2 SET stage_id = 
         CASE 
           WHEN t.required_quantity  <= t.total_received THEN 27
         --WHEN t.total_ordered < t.total_received THEN 18 (NOT NEED THIS BIT)
           ELSE 18
         END


FROM component c2 INNER JOIN 
     (SELECT c.component_id,
            CASE mp.component_name_id WHEN 2 THEN l.quantity*2
                 ELSE l.quantity
            END AS required_quantity,
            SUM(received) AS total_received,
                    --(NOT NEED THIS BIT AND OTHER LIST OF COLUMNS)
                    --SUM(ordered) AS total_ordered
     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 (ISNULL(mp.cancelled,0) = 0) --(OR CONDITION REMOVED)
            AND (ISNULL(mp.manual_override,0) = 0) --(OR CONDITION REMOVED)
            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

ON c2.component_id = t.component_id --(INNER JOIN)

Answer 3

I think there is not any major problem with your query . you need to remove some unused columns from group by close . I can see there are mp.job_id, mp.line_no, mp.component, l.quantity, mp.component_name_id, component_id columns in group by close . Group by close take lot of time to execute. you need to calculate sum directly in core query then join it with outer query it will definitely solve your problem . I dot know the table structure of your query if you provide me a schema then I can help you more. Thanks