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

Question

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.

This looks like SQL Server. Please edit the question and add the SQL Server tag with correct version. The correct version of SQL Server lets us know which special functions we can use. — Nick.Mc, Oct 10 '18 at 12:02
You are basically looking for a fast split function... If you had googled you may have found this: https://stackoverflow.com/questions/697519/split-function-equivalent-in-t-sql Does it really need to be dynamic like that - do you really need to use a varchar variable for the table name? — Nick.Mc, Oct 10 '18 at 13:18
Don't use that splitter. It will perform terribly. Instead, use one like Jeff Moden's. http://www.sqlservercentral.com/articles/Tally+Table/72993/ and split it as many times as you want, or do this outside of sql server. — S3S, Oct 10 '18 at 16:35
It's not clear from your question what you're after. These links are for splitters. If you need a more customised solution you'll need to explain in more detail what you want. For example providing sample input and output data and clarifying whether this actually really needs a table in a string. — Nick.Mc, Oct 11 '18 at 01:23

score 2 · Accepted Answer · answered Oct 10 '18 at 20:15

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

Thank you very much . this is best example to replace a cursor — , Feb 02 '19 at 10:11

score 0 · Answer 2 · edited Oct 10 '18 at 16:33

0

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;

edited Oct 10 '18 at 16:33

S3S

24,809
5
26
45

answered Oct 10 '18 at 13:04

China Syndrome

953
12
24

i have two while checking . please help me to where i have add – Oct 10 '18 at 13:12
replace the cursor itself and things will go faster, i tend to ad an identity int column on my temp table and use that ID to match my cnt and thats how i pull the data out, i will look for a real world example I have used – China Syndrome Oct 10 '18 at 13:49
ya i agree with your code for an extent , but initial value using while loop gives different result and cursor initial value pick up will give different result . when we try to return from first – Oct 10 '18 at 17:11

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

2 Answers2