CODEWITHSUNDEEP
sql-server
Saaharjit Singh
Saaharjit Singh

Reputation: 125

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. Any reason why i takes so long. Do i need to increase the size on our tempdb?

We have the same procedure running on a different server which is smaller in size(5GB) and that takes only 2 mins to execute.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spWIPMatl]
WITH RECOMPILE
AS
    Declare @RKEY numeric(10,0)
    Declare @totMcost numeric(13,5)
    Declare @totsoldcost numeric (13,5)

    Declare @approved int
    Declare @nValue numeric(10,0)

    Declare c1 CURSOR FOR
        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 
        INNER JOIN
            DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY  
        INNER JOIN
            DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHT OUTER JOIN
            DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE     
            (DATA0017_1.P_M = 'P') 
            AND (LEFT(data0017_1.INV_PART_NUMBER, 3) = '25-' 
                 OR LEFT(data0017_1.INV_PART_NUMBER, 3) = '85-') 
            AND (DATA0095_1.TRAN_TP = 13 or DATA0095_1.TRAN_TP = 14)
        GROUP BY 
            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

        UNION ALL

SELECT     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 INNER JOIN
                      DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY INNER JOIN
                      DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY RIGHT OUTER JOIN
                      DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
/*WHERE     (DATA0017_1.P_M = 'P') and (LEFT(data0017_1.INV_PART_NUMBER, 3) ='35-' ) and DATA0095_1.tran_tp=13 */
WHERE     (DATA0017_1.P_M = 'P') and (LEFT(data0017_1.INV_PART_NUMBER, 3) ='35-' ) and (DATA0095_1.tran_tp=13 or DATA0095_1.TRAN_TP = 14)
GROUP BY 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

union all

SELECT     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 INNER JOIN
                      DATA0017 DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY INNER JOIN
                      DATA0067 DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY RIGHT OUTER JOIN
                      DATA0006 DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY

/* WHERE  data0017_1.P_M ='P' and (LEFT(data0017_1.INV_PART_NUMBER, 3) <> '35-' and LEFT(data0017_1.INV_PART_NUMBER, 3) <> '85-' and LEFT(data0017_1.INV_PART_NUMBER, 3) <> '25-'  ) and DATA0095_1.tran_tp=13 */
WHERE data0017_1.P_M ='P' and (LEFT(data0017_1.INV_PART_NUMBER, 3) <> '35-' and LEFT(data0017_1.INV_PART_NUMBER, 3) <> '85-' and LEFT(data0017_1.INV_PART_NUMBER, 3) <> '25-'  ) and (DATA0095_1.tran_tp=13 or DATA0095_1.TRAN_TP = 14)
GROUP BY 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
ORDER BY 2, 1

OPEN c1
FETCH NEXT FROM c1
INTO @RKEY, @totMcost, @totsoldcost

WHILE @@FETCH_STATUS = 0
BEGIN

        /*SELECT @nValue = (SELECT RKEY FROM WIPStatusV3 WHERE RKEY=@RKEY)*/

        UPDATE tempWIPAeroV1 SET WIPmatl = WIPmatl + @totMcost, WIP_sold = WIP_sold + @totsoldcost WHERE RKEY=@RKEY


    FETCH NEXT FROM c1
    INTO @RKEY, @totMcost, @totsoldcost
END
delete from WIPAeroV1
insert into WIPAeroV1 select * from tempWIPAeroV1

CLOSE c1
DEALLOCATE c1`

Upvotes: 1

Views: 1203

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

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.

ALTER PROCEDURE [dbo].[spWIPMatl]
WITH RECOMPILE AS
BEGIN

    with 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 
        INNER JOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNER JOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHT OUTER JOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M = 'P'
            and LEFT(data0017_1.INV_PART_NUMBER, 3) in ('25-', '85-')
            and DATA0095_1.TRAN_TP in (13, 14)
        GROUP BY 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

        union all

        SELECT 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 
        INNER JOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNER JOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHT OUTER JOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M = 'P'
            and LEFT(data0017_1.INV_PART_NUMBER, 3) = '35-'
            and DATA0095_1.tran_tp in (13, 14)
        GROUP BY 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

        union all

        SELECT 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
        INNER JOIN DATA0017 DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY
        INNER JOIN DATA0067 DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY
        RIGHT OUTER JOIN DATA0006 DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE data0017_1.P_M = 'P' 
            and LEFT(data0017_1.INV_PART_NUMBER, 3) not in ('35-', '85-', '25-') 
            and DATA0095_1.tran_tp in (13, 14)
        GROUP BY 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

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

Upvotes: 1

Tobb
Tobb

Reputation: 12205

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*

Upvotes: 0

Related Questions