StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTSql (sql server 2005) query running slow
text
Body
copied!<p>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</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload