SQL Job Slow calling stored procedure

It can be  a deadlock..

Check 4 Locks be4 u termicate it...


Melih SARICA

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.

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.

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

Oh yeah, and I'm thinking that it's not a lock because it actually does finish (45 minutes - ugh)

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.

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?

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

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.

Sorry you didn;t get the answer you were looking for; cheers for the points anyway!

s46.