TSql (sql server 2005) query running slow

Question

The following query is running slow. for one value in memberid, there are multiple entries in memberid0, memberid1........ memberid9. Therefore each statement is effecting multiple row updates. Ofcourse the table size is in MBs

Declare @memberName nvarchar(250)
Declare @memberID bigint
Declare @dimId int
Declare @levelId int
Declare @newName nvarchar(250)
Declare @updateSQL1 nvarchar(500)
Declare @updateSQL2 nvarchar(500)
Declare @cursorStmt nvarchar(300)
Declare @custCounter bigint
Declare @prodCounter bigint
Declare @regCounter bigint
Declare @memberCounter int

SET @custCounter = 1
SET @prodCounter = 1
SET @regCounter = 1
SET @memberCounter = 0

BEGIN TRANSACTION

While @memberCounter < 3
Begin

    Set @cursorStmt = 'Declare memberCursor CURSOR
        FOR Select name, memberid, dimensionId, levelNumber from member' + CAST(@memberCounter as NVARCHAR(1)) + ' where memberID <> 0 order by memberid'
    print @cursorStmt

    exec sp_executesql @cursorStmt  

    OPEN memberCursor
    FETCH NEXT FROM memberCursor INTO @memberName, @memberId, @dimId, @levelId

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @dimId = 0
            BEGIN
                SET @newName = 'Customer_' + CAST(@custCounter AS NVARCHAR(10)) + '_LEVEL_' + CAST(@levelId AS NVARCHAR(10))
                SET @custCounter = @custCounter + 1 
            END
        else if @dimId = 1
            BEGIN
                SET @newName = 'Product_' + CAST(@prodCounter AS NVARCHAR(10)) + '_LEVEL_' + CAST(@levelId AS NVARCHAR(10))
                SET @prodCounter = @prodCounter + 1 
            END
        else if @dimId = 2
            BEGIN
                SET @newName = 'Region_' + CAST(@regCounter AS NVARCHAR(10)) + '_LEVEL_' + CAST(@levelId AS NVARCHAR(10))
                SET @regCounter = @regCounter + 1   
            END

        SET @updateSQL1 = 'Update Member' + CAST(@dimId AS NVARCHAR(5)) + ' set name = ''' + @newName + ''' where memberId = ' + CAST(@memberId AS NVARCHAR(10))
        SET @updateSQL2 = 'Update Member' + CAST(@dimId AS NVARCHAR(5)) + ' set memberName' + CAST(@levelId-1 AS NVARCHAR(5)) + ' = ''' + @newName + ''' where memberId' + CAST(@levelId-1 AS NVARCHAR(5)) + ' = ' + CAST(@memberId AS NVARCHAR(10))

        --print @updateSQL1
        --print @updateSQL2

        exec sp_executesql @updateSQL1  
        exec sp_executesql @updateSQL2

        FETCH NEXT FROM memberCursor INTO @memberName, @memberId, @dimId, @levelId

    END

    CLOSE memberCursor  
    DEALLOCATE memberCursor

    Set @memberCounter = @memberCounter + 1

END

Steps to improve: (1) get rid of the cursor - that's **always** going to be sloooooooow - (2) check your execution plan for potential indices that could help (on columns that show up in WHERE or ORDER BY clauses) — marc_s, Apr 21 '11 at 09:20

score 0 · Answer 1 · edited May 23 '17 at 12:14

I double marc_s says, but if you don't want to do that, try creating a table which hold your Update statements and then execute them in a batch of minimum 10 updates per query.

Try to create two table to store two different kind of updates so in a batch, you can have only kind of update in minimum of 10 rows. I would like to update as many as I could in a single query.

Multiple updates in single SQL query is faster, see: Why are batch inserts/updates faster? How do batch updates work?

TSql (sql server 2005) query running slow

1 Answers1