SQL Server Performance

Tuning Procedure

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Reddy, Jul 15, 2008.

  1. Reddy New Member

    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
  2. SQL2000DBA New Member

    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.
  3. Anoop New Member

  4. Reddy New Member

    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.
  5. preethi Member

    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.
  6. venkatesanj@hcl.in New Member

    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

Share This Page