• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

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?
0
r_i_x
Asked:
r_i_x
2 Solutions
 
Melih SARICACommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now