Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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; enter image description here

share|improve this question

migrated from stackoverflow.com Oct 29 '12 at 16:59

This question came from our site for professional and enthusiast programmers.

2  
What is the execution plan? –  ta.speot.is Oct 27 '12 at 10:59
1  
Appart from @ta.speot.is 's suggestion (wich you should follow), your update could well be updating the same record multiple times wich is just a waste of processing time. Consider a component_id having two job_id's: the statement will update that component twice, perhaps even with different values (we don't know your application so it's not possible for us to tell from the statement). At first sight, I would remove a bunch of columns from your select, make sure you have properly indexed the tables and try again. –  Lieven Keersmaekers Oct 27 '12 at 11:05
1  
The clustered index scan on materials_progress suggests to me that there are no indexes on that table is for some combination job_id, line_no or component_name_id. You might need to index those fields in materials_progress. You might also have bad statistics. –  ta.speot.is Oct 27 '12 at 11:24
    
True, I did index those fields and that made the query quicker in 30 seconds. But it still takes 3 mins and 55 secs for the execution to complete!! :-( –  mithilatw Oct 27 '12 at 11:36
1  
Indexing the fields individually won't help as much as creating an index whose key is all three fields, or whose key and covering columns cover the three fields. –  ta.speot.is Oct 29 '12 at 11:24
show 1 more comment

3 Answers 3

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

share|improve this answer
add comment

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)
share|improve this answer
    
Thank you very much for your suggestion and I like this idea a lot!! But SQL Server says that Incorrect syntax near Group what should I do for that? :-s –  mithilatw Oct 27 '12 at 11:32
    
Also it says; Msg 157, Level 15, State 1, Line 5 An aggregate may not appear in the set list of an UPDATE statement. Which it refers to SUM() in the inner CASE statement. It doesn't look like this works!! :-( –  mithilatw Oct 27 '12 at 11:53
    
Just edited, try now. –  Kaf Oct 27 '12 at 12:06
    
It still takes the same amount of time for the execution! :-( Thank you very much for your time any way!! –  mithilatw Oct 27 '12 at 14:32
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.