SQL Server Performance

cut down execution time on query with cursor

Discussion in 'T-SQL Performance Tuning for Developers' started by boutwater, Feb 13, 2008.

  1. boutwater Member

    Hello, I have a SP that calls a second SP. They both use cursors, and the purpose is to scan through a table (classSKUInv) and update each row with data that matches what the database has for each class and SKU. Its basically a maintenance thing to fix discrepencies. My problem is that it runs for 5 hours. I'm thinking that it has to do with the line:
    AND ct.[RecDocID]+ct.[RecDocType]+ct.[CartonID] NOT IN
    (SELECT [ID]+[DocType]+[CartonID] FROM [PutAway])
    in the second stored procedure, but i'm not sure how else to do this to gain speed and keep the accuracy. Let me know if I need to clarify at all. Thanks,
    Ben
    /* FUNCTION DCS_ResetAllInventory
    NOTE: This function assumes that the ClassSKUInv records already exist
    */
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'DCS_ResetAllInventory' AND type = 'P')
    DROP PROCEDURE DCS_ResetAllInventory
    GO
    CREATE PROCEDURE DCS_ResetAllInventory
    AS
    BEGIN
    DECLARE @MyClassID int
    DECLARE @MyIMSSKU int
    DECLARE @MySKUSuffix int
    DECLARE ClassSKU_Cursor CURSOR FOR
    SELECT [ClassID],[IMSSKU],[SKUSuffix]
    FROM [ClassSKUInv]
    GROUP BY [ClassID],[IMSSKU],[SKUSuffix]
    OPEN ClassSKU_Cursor
    FETCH NEXT FROM ClassSKU_Cursor INTO @MyClassID,@MyIMSSKU,@MySKUSuffix
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC DCS_ResetClassSKUInv @MyClassID, @MyIMSSKU, @MySKUSuffix
    FETCH NEXT FROM ClassSKU_Cursor INTO @MyClassID,@MyIMSSKU,@MySKUSuffix
    END
    CLOSE ClassSKU_Cursor
    DEALLOCATE ClassSKU_Cursor
    RETURN
    END

    GO

    /* How to call
    EXEC DCS_ResetAllInventory

    */


    /* FUNCTION DCS_ResetClassSKUInv */
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'DCS_ResetClassSKUInv' AND type = 'P')
    DROP PROCEDURE DCS_ResetClassSKUInv
    GO
    CREATE PROCEDURE DCS_ResetClassSKUInv @ClassID int, @IMSSKU int, @SKUSuffix int
    AS
    BEGIN
    DECLARE @MyFacilityID int
    DECLARE @MyCasePackQty int
    DECLARE @MyCartonQty int
    DECLARE @MyQuantityRequired int
    DECLARE @MyQuantityWaved int
    DECLARE Facility_Cursor CURSOR FOR
    SELECT [FacilityID]
    FROM [Facility]
    OPEN Facility_Cursor
    FETCH NEXT FROM Facility_Cursor INTO @MyFacilityID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    /* Delete existing Inventory records */
    DELETE FROM [ClassSKUInv]
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix
    AND [FacilityID] = @MyFacilityID
    /* Find all the non palletized Cartons and insert Inventory */
    DECLARE NonPalletized_Cursor CURSOR FOR
    SELECT cc.[Quantity],
    count(*)
    FROM [CartonContent] cc WITH (INDEX = [IdxViaClassSKU]),
    [Carton] ct,
    [Location] loc
    WHERE ct.[RecDocID] = cc.[RecDocID]
    AND ct.[RecDocType] = cc.[DocType]
    AND ct.[CartonID] = cc.[CartonID]
    AND cc.[ClassID] = @ClassID
    AND cc.[IMSSKU] = @IMSSKU
    AND cc.[SKUSuffix] = @SKUSuffix
    AND (ct.[WasPackAndHold] != 'Y' OR ct.[WasPackAndHold] IS NULL)
    AND ct.[StatusID] NOT IN ('63','65','70','90')
    AND ct.[StoreID] = 15
    AND ct.[LocTypeID] IN ('1','2','3','4','A','Q','G','T')
    AND ct.[RecDocID]+ct.[RecDocType]+ct.[CartonID] NOT IN
    (SELECT [ID]+[DocType]+[CartonID] FROM [PutAway])
    AND ct.[FacilityID] = @MyFacilityID
    AND loc.[CurrentFacilityID] = ct.[FacilityID]
    AND loc.[LocTypeID] = ct.[LocTypeID]
    AND loc.[LocationID] = ct.[LocationID]
    GROUP BY cc.[Quantity]
    OPEN NonPalletized_Cursor
    FETCH NEXT FROM NonPalletized_Cursor INTO @MyCasePackQty, @MyCartonQty
    WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT INTO [ClassSKUInv] ([ClassID],[IMSSKU],[SKUSuffix],[FacilityID],
    [PackQty],[QuantityOnHand],
    [QuantityRequired],[QuantityWaved],[DateTimeCreated],[DateTimeChanged])
    VALUES (@ClassID,@IMSSKU,@SKUSuffix,@MyFacilityID,
    @MyCasePackQty,@MyCartonQty,
    0,0,getdate(),getdate())
    FETCH NEXT FROM NonPalletized_Cursor INTO @MyCasePackQty, @MyCartonQty
    END
    CLOSE NonPalletized_Cursor
    DEALLOCATE NonPalletized_Cursor
    /* Find all palletized Cartons and insert or update the Inventory */
    DECLARE Palletized_Cursor CURSOR FOR
    SELECT cc.[Quantity],
    count(*)
    FROM [CartonContent] cc WITH (INDEX = [IdxViaClassSKU]),
    [Carton] ct,
    [XLoc]
    WHERE ct.[RecDocID] = cc.[RecDocID]
    AND ct.[RecDocType] = cc.[DocType]
    AND ct.[CartonID] = cc.[CartonID]
    AND cc.[ClassID] = @ClassID
    AND cc.[IMSSKU] = @IMSSKU
    AND cc.[SKUSuffix] = @SKUSuffix
    AND (ct.[WasPackAndHold] != 'Y' OR ct.[WasPackAndHold] IS NULL)
    AND ct.[StatusID] NOT IN ('63','65','70','90')
    AND ct.[StoreID] = 15
    AND ct.[LocTypeID] IN ('P')
    AND ct.[RecDocID]+ct.[RecDocType]+ct.[CartonID] NOT IN
    (SELECT [ID]+[DocType]+[CartonID] FROM [PutAway])
    AND [XLoc].[SubFacilityID] = ct.[FacilityID]
    AND [XLoc].[SubLocTypeID] = ct.[LocTypeID]
    AND [XLoc].[SubLocationID] = ct.[LocationID]
    AND [XLoc].[ParFacilityID] = @MyFacilityID
    GROUP BY cc.[Quantity]
    OPEN Palletized_Cursor
    FETCH NEXT FROM Palletized_Cursor INTO @MyCasePackQty, @MyCartonQty
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF (SELECT count(*) from [ClassSKUInv] WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix
    AND [FacilityID] = @MyFacilityID
    AND [PackQty] = @MyCasePackQty) < 1
    BEGIN
    INSERT INTO [ClassSKUInv] ([ClassID],[IMSSKU],[SKUSuffix],[FacilityID],
    [PackQty],[QuantityOnHand],
    [QuantityRequired],[QuantityWaved],[DateTimeCreated],[DateTimeChanged])
    VALUES (@ClassID,@IMSSKU,@SKUSuffix,@MyFacilityID,
    @MyCasePackQty,@MyCartonQty,
    0,0,getdate(),getdate())
    END ELSE BEGIN
    UPDATE [ClassSKUInv] SET [QuantityOnHand] = [QuantityOnHand] + @MyCartonQty
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix
    AND [FacilityID] = @MyFacilityID
    AND [PackQty] = @MyCasePackQty
    END
    FETCH NEXT FROM Palletized_Cursor INTO @MyCasePackQty, @MyCartonQty
    END
    CLOSE Palletized_Cursor
    DEALLOCATE Palletized_Cursor
    /* Fix any problems with over completion of PRPackQtyPlan Records */
    UPDATE [PRPackQtyPlan] SET [PicksCompleted] = [PicksRequired], [ItemsCompleted] = [ItemsRequired], [DateTimeChanged] = getdate()
    WHERE [PicksCompleted] > [PicksRequired]
    AND [FacilityID] = @MyFacilityID
    AND [RequestID] IN (SELECT [ID] FROM [PickRequest]
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix)
    /* Fix any problems with over completion of PRWave Records */
    UPDATE [PRWave] SET [PicksCompleted] = [PicksRequired], [ItemsCompleted] = [ItemsRequired], [DateTimeChanged] = getdate()
    WHERE [PicksCompleted] > [PicksRequired]
    AND [FacilityID] = @MyFacilityID
    AND [RequestID] IN (SELECT [ID] FROM [PickRequest]
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix)
    /* Update the QuantityRequired and QuantityPlanned for each ClassSKUInv.PackQty found in inventory */
    DECLARE Inv_Cursor CURSOR FOR
    SELECT [PackQty]
    FROM [ClassSKUInv]
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix
    AND [FacilityID] = @MyFacilityID
    OPEN Inv_Cursor
    FETCH NEXT FROM Inv_Cursor INTO @MyCasePackQty
    WHILE @@FETCH_STATUS = 0
    BEGIN
    /* Get QuantityRequired from PRPackQtyPlan */
    SET @MyQuantityRequired = (SELECT SUM([PicksRequired] - [PicksCompleted])
    FROM [PRPackQtyPlan],[PickRequest]
    WHERE [PRPackQtyPlan].[RequestID] = [PickRequest].[ID]
    AND [PRPackQtyPlan].[FacilityID] = @MyFacilityID
    AND [PRPackQtyPlan].[PackQty] = @MyCasePackQty
    AND [PickRequest].[ClassID] = @ClassID
    AND [PickRequest].[IMSSKU] = @IMSSKU
    AND [PickRequest].[SKUSuffix] = @SKUSuffix)
    IF @MyQuantityRequired IS NULL
    BEGIN
    SET @MyQuantityRequired = 0
    END
    /* Get QuantityPlanned from PRPlan */
    SET @MyQuantityWaved = (SELECT SUM([PicksRequired] - [PicksCompleted])
    FROM [PRWave],[PickRequest]
    WHERE [PRWave].[RequestID] = [PickRequest].[ID]
    AND [PRWave].[FacilityID] = @MyFacilityID
    AND [PRWave].[PackQty] = @MyCasePackQty
    AND [PickRequest].[ClassID] = @ClassID
    AND [PickRequest].[IMSSKU] = @IMSSKU
    AND [PickRequest].[SKUSuffix] = @SKUSuffix)
    IF @MyQuantityWaved IS NULL
    BEGIN
    SET @MyQuantityWaved = 0
    END
    UPDATE [ClassSKUInv]
    SET [QuantityRequired] = @MyQuantityRequired, [QuantityWaved] = @MyQuantityWaved, [DateTimeChanged] = getdate()
    WHERE [ClassID] = @ClassID
    AND [IMSSKU] = @IMSSKU
    AND [SKUSuffix] = @SKUSuffix
    AND [FacilityID] = @MyFacilityID
    AND [PackQty] = @MyCasePackQty
    FETCH NEXT FROM Inv_Cursor INTO @MyCasePackQty
    END
    CLOSE Inv_Cursor
    DEALLOCATE Inv_Cursor
    FETCH NEXT FROM Facility_Cursor INTO @MyFacilityID
    END
    CLOSE Facility_Cursor
    DEALLOCATE Facility_Cursor
    RETURN
    END

    GO

    /* How to call
    EXEC DCS_ResetClassSKUInv 1001, 111, 0

    */
  2. Adriaan New Member

    You have cursor A from which you read details into variables, then you call a procedure that opens a cursor B based on the values from the variables.
    I haven't looked at the script in any finer detail than that, but this is usually the same as a single query that joins the tables on the corresponding columns.
    The UPDATE syntax supports a FROM clause with joined tables, although most examples that you see don't have a FROM clause.
    Then again, perhaps there is some more complex processing going on in the second cursor. In that case, try to see if there are repeating groups of values for which the results in the second cursor will always be the same, pre-process those results into a temp table (with the key values) and finally update the first table against the temp table.
  3. boutwater Member

    The reason for the two procedures, is that the second one can be called individually, and does not rely on the first. the only thing that the first is for is to call the second when I want the second SP to run for every record in the ClassSKUInv table. I'd like to figure out a quicker way to do the subquery that excludes records that have a putaway record out there.
  4. Adriaan New Member

    For a NOT IN subquery in a WHERE clause, there are two alternatives that usually perform better:
    (1) a correlated NOT EXISTS subquery in the WHERE clause,
    (2) instead of a subquery, add a LEFT JOIN on the putaway table (or a derived table) to the FROM clause, and add the (first of the) matching column(s) from the putaway table to the WHERE clause, with IS NULL as the criteria.
    The first option can perform as poorly as the NOT IN version, never worse but sometimes much better, whereas the second option has a better chance of doing better.
    And in the end it all depends on having appropriate indexes on the tables.
  5. boutwater Member

    That makes sense. Thanks for the quick replies,
    Ben

Share This Page