TSql (sql server 2005) query running slow

The following query is running slow. for one value in memberid, t开发者_JAVA百科here 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

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?