CODEWITHSUNDEEP
performancestored-proceduresoptimizationsql-server-2008-r2cursors
user9912287
user9912287

Reputation:

How to replace cursor inside Stored Procedure for faster Execution in MS SQL?

I have written the below code for splitting string, but it takes a long time to execute. Please help me re-write my code to optimize the query. I have tried hard to find the result, but i didn't get how to apply the logic to replace the cursor. 

declare @table as nvarchar(50),@column as nvarchar(max),@delimiter
as nvarchar(1),@tablekey as nvarchar(max)

BEGIN  
SET NOCOUNT ON;  
 set @table='QAT_Tsentences'
 set @column='SentenceElID'
 set @delimiter='|'
 set @tablekey='ID'

 declare @sql as nvarchar(max), @tabkey as nvarchar(max),  @txt as 
 nvarchar(1000), @txtSplitted as nvarchar(255)  
 DECLARE @pos integer--, @delimiter as nchar(1)  
 DECLARE @Leftpiece nvarchar(255), @Rightpiece as nvarchar(255)  


 CREATE TABLE #t(tablekey  nvarchar(max), txt nvarchar(1000))  
 set @sql= 'INSERT #t select '+@tablekey+','+@column+' from '+@table+' where 
 '+@column+' is not null'  

  exec(@sql)  

  drop table QAT_txtsplitted  
  CREATE TABLE QAT_txtsplitted(tablekey  nvarchar(max), txt [nvarchar] 
 (max), txtSplitted [nvarchar](max), ID INT NOT NULL IDENTITY(1,1))  



  DECLARE c1 CURSOR   
  FOR  
   SELECT tablekey, txt  
    FROM #t  

   OPEN c1  

   FETCH NEXT FROM c1  
   INTO @tabkey,@txt  
    While (@@Fetch_status = 0)  
    BEGIN   

    SET @Rightpiece = @txt  
    IF RIGHT(RTRIM(@Rightpiece),1) <> @delimiter  
    SET @Rightpiece = @Rightpiece  + @delimiter  
    SET @pos =  CHARINDEX(@delimiter, @Rightpiece)  

    WHILE @pos <> 0   
    BEGIN  
    SET @Leftpiece = LEFT(@Rightpiece, @pos - 1)  
     INSERT INTO  QAT_txtsplitted (tablekey,txt,txtsplitted) VALUES  
      (@tabkey,@txt, @Leftpiece);  
      SET @Rightpiece = STUFF(@Rightpiece, 1, @pos, '')  
      SET @pos =  CHARINDEX(@delimiter, @Rightpiece)  
       END  
       FETCH NEXT FROM c1  
       INTO @tabkey,@txt  

       END  

  CLOSE c1  
     DEALLOCATE c1  
     drop table #t  
     nchar(1),@tablekey as decimal(15))  
     print 'table results : QAT_txtsplitted'  

     END

Please find below my result where i have tried to split for a particular tablekey. enter image description here

Upvotes: 0

Views: 3031

Answers (2)

China Syndrome
China Syndrome

Reputation: 993

Here is part of a SP THAT I wrote to remove a cursor

BEGIN
    DECLARE @Modules TABLE
    (
        ID INT IDENTITY(1,1)
        , ModuleId INT
        , ModuleShortName NVARCHAR(256)
        , ModuleTableName NVARCHAR(260)
    )

    INSERT INTO @Modules select ModuleId,ModuleShortName ,ModuleTableName  from Mods

    DECLARE @Count INT, @Counter INT, @ModuleId INT, @ModuleTableName NVARCHAR(260)
    SELECT @Count = COUNT(*) FROM @Modules m
    SET @Counter = 1



    WHILE @Counter <= @Count
    BEGIN
        SELECT @ModuleId = ModuleId, @ModuleTableName = ModuleTableName FROM @Modules m WHERE ID = @Counter -- extracting values from the table by counternum

         -- do something with data
        -- puttin inner logic


        SET @Counter = @Counter + 1
    END

Upvotes: 2

China Syndrome
China Syndrome

Reputation: 993

Cursors can be poor performers in SQL Server which is designed for set-based operations. There are some methods to increase the performance of a cursor by applying additional arguments which can be read about here. If you only want to sequentially step through your cursor, then you could alleviate a lot of overhead by using the LOCAL FAST_FORWARD arguements.

DECLARE C1 CURSOR LOCAL FAST_FORWARD FOR <your query>

Another method, which could perform better, is a while loop.

DECLARE @cnt INT = 0;
Declare @t_CNT = Select Count(*) from t 
WHILE @cnt < @t_CNT
BEGIN
 // do your work here
   SET @cnt = @cnt + 1;
END;

Upvotes: 0

Related Questions