SQL Job Slow calling stored procedure

I have a stored procedure that runs in about 120 seconds. It has a cursor and loops through numerous records. When I call this as a scheduled job (every 6 hours) it has been taking more than 30 minutes (and then I terminated it).

What could be causing this?
LVL 1
r_i_xAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Melih SARICAOwnerCommented:
It can be  a deadlock..

Check 4 Locks be4 u termicate it...


Melih SARICA
0
muzzy2003Commented:
It could well be a deadlock, with the difference in execution being caused by a difference in the transactional state used by SQL Server Agent. How are you declaring the cursor - are you being explicit about static/dynamic/keyset, for example? Does the cursor itself need to be updateable, or are any updates made being made separately? If you could post the code it would help.
0
solution46Commented:
To check for deadlocks, open QA and run sp_who2

This will list all processes and indicate if they are being locked by another.

Regards,

s46.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

r_i_xAuthor Commented:
The stored procedure is the following:

CREATE PROCEDURE [dbo].[ImportItemData] AS
BEGIN

      exec billofmaterialsupdate
      exec branchplantupdate
      exec itembranchupdate
      exec itemmasterupdate
      exec itemxrefupdate
      exec suppliercatalogupdate
      exec uomupdate
END

-- All of the SPs in the previous are the same structure. It loops through a recordset and calls the ItemUpdate SP. The ItemUpdate SP updates if the primary key exists in the table or inserts if it doesn't.

CREATE PROCEDURE [dbo].[BillOfMaterialsUpdate]
AS
BEGIN
      DECLARE      @JDEORGID       integer
      DECLARE      @JDEKITID      numeric
      DECLARE      @JDEITEMID      numeric
      DECLARE      @QUANTITY      numeric
      DECLARE      @UOM            varchar(10)
      DECLARE      @DELETEFLAG      bit

      --  mark items with delete flag
      UPDATE tblJDEBillOfMaterials SET DeleteFlag = 1

      -- open the recordset from the view
      DECLARE x_cursor CURSOR FOR
      SELECT JDEOrgID, JDEKitID, JDEItemID, Quantity, UOM FROM JDEBillOfMaterials
      
      OPEN x_cursor

      FETCH NEXT FROM x_cursor
      INTO @JDEORGID, @JDEKITID, @JDEITEMID, @QUANTITY, @UOM

      WHILE @@FETCH_STATUS = 0
      BEGIN
            EXEC BillOfMaterialsItemUpdate @JDEORGID, @JDEKITID, @JDEITEMID, @QUANTITY, @UOM

            FETCH NEXT FROM x_cursor
            INTO @JDEORGID, @JDEKITID, @JDEITEMID, @QUANTITY, @UOM

            SET @DELETEFLAG = 1
      END

      IF @DELETEFLAG = 1
      BEGIN
            DELETE FROM tblJDEBillOfMaterials WHERE DeleteFlag = 1
      END

      CLOSE x_cursor
      DEALLOCATE x_cursor

END

CREATE PROCEDURE [dbo].[BillOfMaterialsItemUpdate]
      @JDEORGID      integer,
      @JDEKITID      numeric,
      @JDEITEMID      numeric,
      @QUANTITY      numeric,
      @UOM            varchar(10)
AS
BEGIN
      UPDATE tblJDEBillOfMaterials SET
            Quantity = @QUANTITY,
            UOM = @UOM,
            DeleteFlag = 0
      WHERE JDEOrgID = @JDEORGID
            AND JDEKitID = @JDEKITID
            AND JDEItemID = @JDEITEMID

      IF @@ROWCOUNT <> 1
      BEGIN
            -- insert a new record
            INSERT INTO tblJDEBillOfMaterials
            (JDEOrgID, JDEKitID, JDEItemID, Quantity, UOM, DeleteFlag)
            VALUES
            (@JDEORGID, @JDEKITID, @JDEITEMID, @QUANTITY, @UOM, 0)
      END
END
0
r_i_xAuthor Commented:
Oh yeah, and I'm thinking that it's not a lock because it actually does finish (45 minutes - ugh)
0
solution46Commented:
r_i_x,

from looking at this, I would say the cursor is unnecessary. You appear to be:
1. updating all matching rows in the BOM table with corresponding values from the BOM view
2. inserting all rows that exist only in the BOM view into the BOM table
3. deleting all rows that do not appear in the BOM view from the BOM table.

If this is correct, you could rewrite the process as...
"make sure the BOM table only has the rows specified int he BOM view", in which case you could write the sproc like this...


CREATE PROCEDURE [dbo].[BillOfMaterialsUpdate]
AS

TRUNCATE TABLE tblJDEBillOfMaterials     -- may need to change this to DELETE FROM tblJDEBillOfMaterials depending on permissions...
GO

INSERT INTO tblJDEBillOfMaterials
SELECT * FROM JDEBillOfMaterials       -- may need to do something about the deleted flag, like delete it.
GO


This seems like quite a drastic change so please have a think about it and let me know if I've missed something crucial.

Regards,

s46.

0
r_i_xAuthor Commented:
The one issue with that is that there will be a period of time when the data is unavailable. I did look at that solution previously and yes, it is faster by about a factor of three.

The data from these tables must be accurate and always there.

One thing I didn't know, if I did the TRUNCATE and INSERT within a transaction, will that give seemless data?
0
solution46Commented:
Yes, but it will be read only. Also, I don't think you can include truncate in a transaction so you may haveto use delete from ...

Anyway, is it better to have no data briefly, or oncorrect data for longer? If somebody decided to print of a BOM mid-update and got the wrong list, how would the impact of that compare to that of them not being able to print it at all?

s46


0
Scott PletcherSenior DBACommented:
>>  I don't think you can include truncate in a transaction so you may haveto use delete from ... <<

FYI, actually you can use TRUNCATE in a transaction and you *can* roll it back (unlike in Oracle).  SQL logs the page deallocations rather than by row, but it can still be committed and rolled back just like any other SQL statement in a trans.
0
solution46Commented:
Fairy Nuff, do that then :).

s46.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
r_i_xAuthor Commented:
Tried all these solutions and for what I'm trying to achieve, I have to use cursors for the time being.

Thanks for your help.
0
solution46Commented:
Sorry you didn;t get the answer you were looking for; cheers for the points anyway!

s46.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.