Tuning Procedure

How best I can tune this on 2005 without using cursors, can anyone advise me.Declare @errorCode int,@rowc int, @StartTime SmallDateTime, @stepMarker nvarchar(300),@rowsUpdated int, @rowsinserted int,@Holdervarchar(10),@HolderNamevarchar(30) SET @errorCode = @@ERRORSET @StartTime = getdate()SET @rowsUpdated = 0 SET @rowsinserted = 0 --Get Holders DECLARE cur_Holders CURSOR FAST_FORWARD FORSELECT RTRIM(Holder) AS Holder, REPLACE(RTRIM(HolderName),' ',' ') AS HolderNameFROM tbHolders OPEN cur_Holders -- Set Error Handler SELECT @errorCode = @@ERROR, @rowc = @@rowcountIF @errorCode <> 0BEGIN set @stepMarker = 'fetch Holders cursor: time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50)) GOTO ErrorHandlerEND IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#tmpUSER_IDs')) DROP TABLE #tmpUSER_IDs SELECT UserID INTO #tmpUSER_IDs FROM LinkServer5.TekDb.dbo.Users UPDATE LinkServer5.TekDb.dbo.Users SET StateHolderCode = '' FROM LinkServer5.TekDb.dbo.Users u INNER JOIN #tmpUSER_IDs ON #tmpUSER_IDs.UserID = u.UserID IF EXISTS(select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#tmpUSER_IDs')) DROP TABLE #tmpUSER_IDs -- Set Error HandlerSELECT @errorCode = @@ERROR, @rowc = @@rowcountIF @errorCode <> 0BEGIN set @stepMarker = 'setting StateHolderCode to empty: time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50)) GOTO ErrorHandlerEND FETCH cur_Holders INTO @Holder, @HolderNameWHILE @@FETCH_STATUS = 0BEGIN IF EXISTS(SELECT UserID FROM LinkServer5.TekDb.DBO.Users WHERE ((FirstName + ' ' + LastName) = @HolderName)) BEGIN UPDATE LinkServer5.TekDb.DBO.Users SET StateHolderCode = @HolderWHERE ((FirstName + ' ' + LastName) = @HolderName) SET @rowsUpdated = @rowsUpdated + 1 -- Set Error Handler SELECT @errorCode = @@ERROR, @rowc = @@rowcount IF @errorCode <> 0 BEGIN set @stepMarker = 'Update Users..StateHolderCode 1: time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50)) + ' rows updated so far: ' + Cast(@rowsUpdated as nvarchar(50)) GOTO ErrorHandler END END ELSE BEGIN IF EXISTS(SELECT UserID FROM LinkServer5.TekDb.DBO.Users WHERE ((PreferredName + ' ' + LastName) = @HolderName)) BEGIN UPDATE LinkServer5.TekDb.DBO.Users SET StateHolderCode = @HolderWHERE ((PreferredName + ' ' + LastName) = @HolderName) SET @rowsUpdated = @rowsUpdated + 1 -- Set Error Handler SELECT @errorCode = @@ERROR, @rowc = @@rowcount IF @errorCode <> 0 BEGIN set @stepMarker = 'Update Users..StateHolderCode 2: time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50))+ ' rows updated so far: ' + Cast(@rowsUpdated as nvarchar(50)) GOTO ErrorHandler END END ELSE BEGIN IF EXISTS(SELECT UserID FROM LinkServer5.TekDb.DBO.Users WHERE (LOWER(SUBSTRING(UserName,1,10)) = LOWER(RTRIM(@Holder)))) BEGIN UPDATE LinkServer5.TekDb.DBO.Users SET StateHolderCode = @HolderWHERE (LOWER(SUBSTRING(UserName,1,10)) = LOWER(RTRIM(@Holder))) SET @rowsUpdated = @rowsUpdated + 1 -- Set Error Handler SELECT @errorCode = @@ERROR, @rowc = @@rowcount IF @errorCode <> 0 BEGIN set @stepMarker = 'Update Users..StateHolderCode 3: time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50))+ ' rows updated so far: ' + Cast(@rowsUpdated as nvarchar(50)) GOTO ErrorHandler END END ELSE BEGIN IF NOT EXISTS(SELECT UserID FROM LinkServer5.TekDb.DBO.Users WHERE (RTRIM(StateHolderCode) = @Holder)) BEGIN INSERT LinkServer5.TekDb.DBO.State_NewHolders( HolderName, StateCode ) VALUES( @HolderName, @Holder ) SET @rowsinserted = @rowsinserted + 1 --Set Error Handler SELECT @errorCode = @@ERROR, @rowc = @@rowcount IF @errorCode <> 0 BEGIN set @stepMarker = 'INSERT INTO State_NewHolders : time(' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50))+ ' rows inserted into NewHolders so far: ' + Cast(@rowsinserted as nvarchar(50)) GOTO ErrorHandler END END END END END FETCH cur_Holders INTO @Holder, @HolderNameEND CLOSE cur_HoldersDEALLOCATE cur_Holders PRINT 'Holders completed. Current time: (' + Cast(getdate() AS nvarchar(50)) + ') . Proc started at ' + Cast(@StartTime AS nvarchar(50)) + '. Rowcount:' + Cast(@rowc as nvarchar(50)) + '. elapsed time ' + Cast(DateDiff(n,@StartTime, GetDate()) AS nvarchar(10)) + ' minutes. Rows inserted into NewHolders: ' + Cast(@rowsinserted as nvarchar(50)) + '. Rows updated in Users: ' + Cast(@rowsUpdated as nvarchar(50)) RETURN @errorCode ErrorHandler: SET NOCOUNT OFF DECLARE @errorMessage nvarchar(1024) SET @errorMessage = 'ERROR: Stored Procedure xSynPortalToState_Holders failed at ' + @stepMarker + '.' PRINT @errorMessage DEALLOCATE cur_Holders RAISERROR ('%s. Error Number = %d', 11, 1, @errorMessage, @errorCode) WITH SETERROR

Hi,
Please find few alternative which you can use in place of Cursor.
WHILE LOOPS,temp tables ,derived tables ,correlated sub-queries ,CASE statement , multiple queries
Also i can see lots of GOTO statement it is better to replace them by using TRY/CATCH block which is much more efficient way of implementing business or systeme exception.

This could help!
http://www.sqlservercentral.com/articles/SQL Puzzles/eliminatingcursors/2223/

I dont see any benefit from WHILE LOOPS, how about using CTE's, i guess that wud work though am not much into CTE's.

On a side note, Moving forward, please take some care on the formatting of the code you submit. I found it extremely difficult to understand the code you have posted.

Hi Sir,
Please provide your code in simple manner.. I was feared on seeing the length code... Sorry for advising you, Please format your code so that...
we may find easy to help you..
Regards,
Venkatesan Prabu .J