Stored Procedure Taking 4 Hours To Run

Below is my stored procedure. I am performing some basic calculations. it used to run faster before but now all of a sudden it takes 4 hours to execute. Our database size is 30GB.

Solution 1:

Pretty sure that whole looping thing could be rewritten along these lines. A couple of suggestions. You should ALWAYS be explicit with insert statements. Specify the columns in the insert statement and list out the columns in your select statement. When using an ORDER BY you should avoid using the ordinal position in favor of using the column name. If your query changes at some point and you don't fix the order by your results may come in an unexpected order.

ALTERPROCEDURE [dbo].[spWIPMatl]
WITH RECOMPILE ASBEGINwith NewValues as
    (
        SELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0.35* DATA0095_1.QUANTITY) AS Sold_cost
        FROM DATA0095 AS DATA0095_1 
        INNERJOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNERJOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHTOUTERJOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M ='P'andLEFT(data0017_1.INV_PART_NUMBER, 3) in ('25-', '85-')
            and DATA0095_1.TRAN_TP in (13, 14)
        GROUPBY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER

        unionallSELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0.8* DATA0095_1.QUANTITY) AS Sold_cost
        FROM DATA0095 AS DATA0095_1 
        INNERJOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNERJOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHTOUTERJOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M ='P'andLEFT(data0017_1.INV_PART_NUMBER, 3) ='35-'and DATA0095_1.tran_tp in (13, 14)
        GROUPBY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER

        unionallSELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0* DATA0095_1.QUANTITY) AS Sold_cost

        FROM DATA0095 DATA0095_1
        INNERJOIN DATA0017 DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY
        INNERJOIN DATA0067 DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY
        RIGHTOUTERJOIN DATA0006 DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE data0017_1.P_M ='P'andLEFT(data0017_1.INV_PART_NUMBER, 3) notin ('35-', '85-', '25-') 
            and DATA0095_1.tran_tp in (13, 14)
        GROUPBY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER
    )

    UPDATE a SET WIPmatl = WIPmatl + nv.Material_cost
        , WIP_sold = WIP_sold + nv.Sold_cost 
    from tempWIPAeroV1 a
    join NewValues nv on nv.RKEY = a.RKEY

    truncatetable WIPAeroV1 --truncate will be quicker because it only has to log page drops instead of every row.insertinto WIPAeroV1 select*from tempWIPAeroV1 --you should always specify the columns in insert statementsEND

Solution 2:

It's really hard to say just given a huge chunk of a query. But a couple of pointers:

1. Make sure you have indexes in your database. Typically on columns used for joining (foreign keys) and used for comparison in where-clauses.
2. Avoid the use of cursors in large queries, they are known for being very slow (https://www.sqlshack.com/sql-server-cursor-performance-problems/).
3. Those tables names, *cringe*