Alternative to While Loop In SQL Server stored procedure-CodePudding

I have a stored procedure where a populate a base table with key fields and then loop through that table to get those key fields to requery a source data table to get detailed counts. The issue I am having is that when there are a lot of rows in the base table, the SP takes a long time to run. I've loaded the source data into temp tables and created index's and made the base table a temp table with an index as well.

CREATE TABLE #SupplementalData1 
(
    ROWID int IDENTITY(1, 1),
    LOB varchar(100),
    Program varchar(100),
    Project varchar(100),
    Container varchar(255),
    RPTNG_Week date,
    Scheduled_Open int,
    Still_Open int,
    Scheduled_Closed int,
    Actual_Closed int
);

CREATE INDEX t1
ON #SupplementalData1 (LOB, Program, Project, Container, RPTNG_Week);

INSERT INTO #SupplementalData1 (LOB, Program, Project, Container, RPTNG_Week)
    SELECT DISTINCT
        a.LOB_CODE,
        a.PRGRM_NAME,
        a.PRJCT_NAME,
        a.CNTNR_NAME,
        b.Monday
    FROM 
        #data a,
        Schedule_Date_Lookup b
    WHERE 
        b.Monday >= @MinMonday
        AND b.Monday <= @MaxMonday
    ORDER BY 
        a.LOB_CODE,
        a.PRGRM_NAME,
        a.PRJCT_NAME,
        b.Monday;

DELETE FROM #SupplementalData1
WHERE RPTNG_Week > @EndDate;

-- Get the number of rows in the looping table
DECLARE @RowCount int;
SET @RowCount = (SELECT COUNT(ROWID)FROM #SupplementalData1);

-- Declare an iterator
DECLARE @I int;
-- Initialize the iterator
SET @I = 1;
--Declare Common Variables
DECLARE @iLOB varchar(MAX),
        @iProgram varchar(MAX),
        @iProject varchar(MAX),
        @iContainer varchar(MAX),
        @iRPTNG_Week date,
        @Value int;

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN

    -- Declare variables to hold the data which we get after looping each record 

    -- Get the data from table and set to variables
    SELECT @iLOB = LOB,
           @iProgram = Program,
           @iProject = Project,
           @iContainer = Container,
           @iRPTNG_Week = RPTNG_Week
    FROM #SupplementalData1
    WHERE ROWID = @I;

    SET @Value = (SELECT COUNT(CNTNR_NAME) AS Scheduled_Open_Sum
                  FROM #data c
                  WHERE (c.NEED_DATE >= @iRPTNG_Week)
                    AND c.LOB_CODE = @iLOB
                    AND c.PRGRM_NAME = @iProgram
                    AND c.PRJCT_NAME = @iProject
                    AND c.CNTNR_NAME = @iContainer);

    UPDATE #SupplementalData1
    SET Scheduled_Open = @Value
    WHERE LOB = @iLOB
      AND Program = @iProgram
      AND Project = @iProject
      AND Container = @iContainer
      AND RPTNG_Week = @iRPTNG_Week;


    -- -- Increment the iterator
    SET @I = @I   1;

END;

Is there an alternative way that would improve speed?

CodePudding user response：

Without sample data, desired output and your logic the following wasn't tested, but should get you moving in the right direction.

Do away with the entire while statement and go with a set based approach.

Here is the while loop rewritten as a SELECT. I will usually do that first to double check and validate data.

SELECT      *
FROM        [#SupplementalData1] [supdata]
CROSS APPLY (
                SELECT COUNT([CNTNR_NAME]) AS [Scheduled_Open_Sum]
                FROM   [#data] [c]
                WHERE  [c].[NEED_DATE] >= [supdata].[RPTNG_Week]
                       AND [c].[LOB_CODE] = [supdata].[LOB]
                       AND [c].[PRGRM_NAME] = [supdata].[Program]
                       AND [c].[PRJCT_NAME] = [supdata].[Project]
                       AND [c].[CNTNR_NAME] = [supdata].[Container]
            ) AS [cd];

Then once you have validated that is correct you can easily rewrite that has an update. Which would be what replaces your while loop.

UPDATE      [supdata]
SET         [Scheduled_Open] = [cd].[Scheduled_Open_Sum]
FROM        [#SupplementalData1] [supdata]
CROSS APPLY (
                SELECT COUNT([CNTNR_NAME]) AS [Scheduled_Open_Sum]
                FROM   [#data] [c]
                WHERE  [c].[NEED_DATE] >= [supdata].[RPTNG_Week]
                       AND [c].[LOB_CODE] = [supdata].[LOB]
                       AND [c].[PRGRM_NAME] = [supdata].[Program]
                       AND [c].[PRJCT_NAME] = [supdata].[Project]
                       AND [c].[CNTNR_NAME] = [supdata].[Container]
            ) AS [cd];