CODEWITHSUNDEEP
sqlsql-serveroptimizationwhile-loopcursor
user9912287
user9912287

Reputation:

How to replace the Cursor with set based solution in MS SQL Server?

I had cursor in my stored procedure. I have updated to while loop to execute faster. But my while loops takes long time. Please help me to debug my script. Please find my below code which I have removed cursor and updated with while loop. I have did compare of output which gives same. But here takes long time compared to cursor.

DECLARE @orderArray INT
    ,@LeftTSMKEY NVARCHAR(250)
    ,@ListElid NVARCHAR(250)
    ,@ListType NVARCHAR(250)
    ,@ListType_Prev NVARCHAR(250)
    ,@inc INT

SET @ListType_Prev = ''
SET @inc = 0

DECLARE @cnt INT
DECLARE @t_cnt INT
DECLARE @Modules TABLE (
    ID INT IDENTITY(1, 1)
    ,ListElid VARCHAR(4000)
    ,ListType VARCHAR(30)
    ,orderArray VARCHAR(4000)
    ,LeftTSMKEY VARCHAR(30)
    )

INSERT INTO @Modules (
    ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY
    )
SELECT ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY
FROM QAT_ListElid
ORDER BY ListType
    ,orderArray

SET @cnt = 1

SELECT @t_cnt = count(*)
FROM @Modules

PRINT @t_cnt
PRINT 'loop started  in ' + cast(getdate() AS NVARCHAR(30))

WHILE @cnt <= @t_cnt
BEGIN
    SELECT @ListElid = ListElid
        ,@ListType = ListType
        ,@orderArray = orderArray
        ,@LeftTSMKEY = LeftTSMKEY
    FROM @Modules
    WHERE ID = @cnt

    IF @ListType <> @ListType_Prev
    BEGIN
        SET @inc = 0
        SET @ListType_Prev = @ListType
    END
    ELSE IF @ListType = @ListType_Prev
    BEGIN
        SET @inc = @inc + 1
    END

    IF @inc <> @orderArray
    BEGIN
        INSERT [QAT_ListElid2] (
            [ListElid]
            ,[ListType]
            ,[orderArray]
            ,[LeftTSMKEY]
            )
        VALUES (
            @ListElid
            ,@ListType
            ,@inc
            ,@LeftTSMKEY + CAST(@inc AS NVARCHAR(10)) + ']'
            )
    END

    SET @cnt = @cnt + 1
END

Upvotes: 0

Views: 308

Answers (1)

davidc2p
davidc2p

Reputation: 320

You didn't supply a dataset and a resultset for validation, but like almost always, a cursor is rarely needed. Instead, you can use queries to achieve the same result.

I don't know if this will work, but the following is a close substitute to what you wrote:

DECLARE @Modules TABLE (
    ID INT IDENTITY(1, 1)
    ,ListElid VARCHAR(4000)
    ,ListType VARCHAR(30)
    ,orderArray VARCHAR(4000)
    ,LeftTSMKEY VARCHAR(30)
    ,OrderedList INT
    )

INSERT INTO @Modules (
    ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY
    ,OrderedList
    )
SELECT ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY
    ,ROW_NUMBER() OVER (partition by ListType order by ListType,orderArray) - 1 as OrderedList
FROM QAT_ListElid

I maintain your temporary table, but add one field OrderedList to store your @inc variable at row level. it's a simple counter, begins with 0, and reinitiates each time ListType changes.

INSERT [QAT_ListElid2] (
    [ListElid]
    ,[ListType]
    ,[orderArray]
    ,[LeftTSMKEY]
    )
SELECT ListElid
    ,ListType
    ,orderArray
    ,LeftTSMKEY + CAST(OrderedList AS NVARCHAR(10)) + ']'
FROM @Modules
WHERE OrderedList <> orderArray

Then it's a simple matter of inserting into the result table QAT_ListElid2, all values but only when our calculated is different from orderArray.

I believe this is what you want to achieve. If not exactly, you can adapt to fit your requirements. The point is to prove that you can live well without cursors and gain in performance.

Upvotes: 1

Related Questions