none
how to remove process id's after update succeed from an index table RRS feed

  • Question

  • Dear all,

    I have the followinf sample querry :

    SELECT ID INTO #tmp FROM mytable WHERE myField LIKE '%a' create index my_idx on #tmp(id) WHILE 1 = 1 BEGIN UPDATE TOP (50000) CONTENT SET myField = REPLACE(myField, 'aaa', 'bbb') FROM CONTENT JOIN #tmp ON CONTENT.ID=#tmp.ID IF @@ROWCOUNT < 50000 BREAK;

    -- IF UPDATE SUCCEED REMOVE ID from #tmp table or update flag

    --- > what to add here ? END


    My goal is to process my record ID updated in batch but in order to be able to recover from a failure and improve scan performance, I would like to delete the ID from #tmp table of the update record or update a flag from #tmp table which tell that the ID record has already be updated ?

    How can I do that ?

    Thanks for help

    Thursday, September 12, 2019 7:47 AM

Answers

  • Thnaks for your reply.

    once small thing , the flag that I need to update is in the table IDX_BODYCONTENT

    Does the follwing syntax looks good to you ?

    WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		 --  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = REPLACE(BODY, '&egrave;', 'è') WHERE BODY LIKE '%filename=%&egrave;%'
    		  UPDATE TOP (@BatchSize) BODYCONTENT 
    			SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText),
    		  idb.RecordUpdated = 1
    		   FROM BODYCONTENT bc JOIN IDX_BODYCONTENT idb ON bc.CONTENTID=idb.CONTENTID
    			WHERE BODY LIKE '%filename=%&eacute;%' and idb.RecordUpdated=0
    		  
    		  SET @results = @@ROWCOUNT;
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
            --RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
    	    COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;

    Thnaks

    Sorry that wont work

    You cant update multiple tables within same update statement

    for that you need to do like this

    DECLARE @UPDATED_BODYCONTENT Table
    (
    CONTENTID int
    )
    
    
    WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		 --  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = REPLACE(BODY, '&egrave;', 'è') WHERE BODY LIKE '%filename=%&egrave;%'
    		  UPDATE TOP (@BatchSize) BODYCONTENT 
    			SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText)
    			  OUTPUT  INSERTED.CONTENTID INTO @UPDATED_BODYCONTENT
    		   FROM BODYCONTENT bc JOIN IDX_BODYCONTENT idb ON bc.CONTENTID=idb.CONTENTID
    			WHERE BODY LIKE '%filename=%&eacute;%' and idb.RecordUpdated=0
    
    
    		  SET @results = @@ROWCOUNT;
    
    		   UPDATE b
    			SET	  b.RecordUpdated = 1
    		   FROM IDX_BODYCONTENT b
    		   JOIN @UPDATED_BODYCONTENT u
    		   ON u.CONTENTID = b.CONTENTID
    
    
    
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
            --RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
    	    COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by wakefun Tuesday, September 17, 2019 7:04 AM
    Thursday, September 12, 2019 10:57 AM

All replies

  • Do you observe performance degradation? Do you have also an index on CONTENT.ID?

    Is that possible to add WHERE condition? I have doubts you improve performance by removing the ID but add complexity to the script


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, September 12, 2019 8:01 AM
    Answerer
  • Thanks for your reply,

    The reason why I want to remove or add a process flag to the Id is that the table to update contains 100 000 records and not necessary update in order. So due to that in order to avoid scanning the all table from begining for each update then I will continue from remaining ID's or ID which has Updated flag =false for instance.

    Then in case I loose connection to my database which is on azure, with the flag i could for instance restart processing record which has not been updated yet.

    But for that each time a record gets updated I need to update it .

    Dos it make sense ?

    my real querry is as below :

    INSERT INTO [IDX_BODYCONTENT]
    SELECT DISTINCT CONTENTID,0 
     FROM BODYCONTENT
    WHERE 
    BODY LIKE '%filename%&%.%' 
    OR BODY LIKE '%filename%[%]%.%';
    
    
      WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText) WHERE BODY LIKE '%filename=%&eacute;%'
    		  FROM BODYCONTENT JOIN [IDX_BODYCONTENT] ON BODYCONTENT.CONTENTID=[IDX_BODYCONTENT].CONTENTID
    		
    		  SET @results = @@ROWCOUNT;
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
           
    	    COMMIT TRANSACTION   
    	 
    
      END;


    Thanks for help



    • Edited by wakefun Thursday, September 12, 2019 8:16 AM
    Thursday, September 12, 2019 8:12 AM
  • I think its much better to update the flag also in the statement

    like

    INSERT INTO [IDX_BODYCONTENT]
    SELECT DISTINCT CONTENTID,0 
     FROM BODYCONTENT
    WHERE 
    BODY LIKE '%filename%&%.%' 
    OR BODY LIKE '%filename%[%]%.%';
    
    
      WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText),
    Flag = 1
     WHERE BODY LIKE '%filename=%&eacute;%'
    		  FROM BODYCONTENT JOIN [IDX_BODYCONTENT] ON BODYCONTENT.CONTENTID=[IDX_BODYCONTENT].CONTENTID
    WHERE BODYCONTENT.Flag = 0
    		
    		  SET @results = @@ROWCOUNT;
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
           
    	    COMMIT TRANSACTION   
    	 
    
      END;


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by wakefun Thursday, September 12, 2019 9:15 AM
    • Unmarked as answer by wakefun Thursday, September 12, 2019 9:29 AM
    Thursday, September 12, 2019 8:41 AM
  • Thnaks for your reply.

    once small thing , the flag that I need to update is in the table IDX_BODYCONTENT

    Does the follwing syntax looks good to you ?

    WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		 --  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = REPLACE(BODY, '&egrave;', 'è') WHERE BODY LIKE '%filename=%&egrave;%'
    		  UPDATE TOP (@BatchSize) BODYCONTENT 
    			SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText),
    		  idb.RecordUpdated = 1
    		   FROM BODYCONTENT bc JOIN IDX_BODYCONTENT idb ON bc.CONTENTID=idb.CONTENTID
    			WHERE BODY LIKE '%filename=%&eacute;%' and idb.RecordUpdated=0
    		  
    		  SET @results = @@ROWCOUNT;
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
            --RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
    	    COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;

    Thnaks

    Thursday, September 12, 2019 9:38 AM
  • Hi ,

    Can we try using OUTPUT clause while doing update statement and capture IDs in a temp table /table variable and later use this to delete in the main table


    Thanks and Regards Rajesh

    Thursday, September 12, 2019 10:27 AM
  • Thnaks for your reply.

    once small thing , the flag that I need to update is in the table IDX_BODYCONTENT

    Does the follwing syntax looks good to you ?

    WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		 --  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = REPLACE(BODY, '&egrave;', 'è') WHERE BODY LIKE '%filename=%&egrave;%'
    		  UPDATE TOP (@BatchSize) BODYCONTENT 
    			SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText),
    		  idb.RecordUpdated = 1
    		   FROM BODYCONTENT bc JOIN IDX_BODYCONTENT idb ON bc.CONTENTID=idb.CONTENTID
    			WHERE BODY LIKE '%filename=%&eacute;%' and idb.RecordUpdated=0
    		  
    		  SET @results = @@ROWCOUNT;
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
            --RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
    	    COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;

    Thnaks

    Sorry that wont work

    You cant update multiple tables within same update statement

    for that you need to do like this

    DECLARE @UPDATED_BODYCONTENT Table
    (
    CONTENTID int
    )
    
    
    WHILE (@results > 0)
      BEGIN
    	  BEGIN TRANSACTION
    		 --  UPDATE TOP (@BatchSize) BODYCONTENT SET BODY = REPLACE(BODY, '&egrave;', 'è') WHERE BODY LIKE '%filename=%&egrave;%'
    		  UPDATE TOP (@BatchSize) BODYCONTENT 
    			SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(MAX)),'&eacute;','é') AS NText)
    			  OUTPUT  INSERTED.CONTENTID INTO @UPDATED_BODYCONTENT
    		   FROM BODYCONTENT bc JOIN IDX_BODYCONTENT idb ON bc.CONTENTID=idb.CONTENTID
    			WHERE BODY LIKE '%filename=%&eacute;%' and idb.RecordUpdated=0
    
    
    		  SET @results = @@ROWCOUNT;
    
    		   UPDATE b
    			SET	  b.RecordUpdated = 1
    		   FROM IDX_BODYCONTENT b
    		   JOIN @UPDATED_BODYCONTENT u
    		   ON u.CONTENTID = b.CONTENTID
    
    
    
    		  -- PRINT N'break Row count before :' + @results;
    		  IF @@ROWCOUNT =0 --< @BatchSize
    			BEGIN
    			PRINT N'break Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
    				COMMIT TRANSACTION
    			BREAK;
    		  END
    		-- display tracking count
    		SET @count = @count + @results
    		PRINT N'Updated Row count :' + + RTRIM(CAST(@results AS nvarchar(30)))  
        + N'.';
            --RAISERROR('COUNT %d', 0, 1, @count) WITH NOWAIT
    	    COMMIT TRANSACTION   
    	  
    	  -- next batch
    	  --SET @id_control = @id_control + @batchSize
    
      END;


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by wakefun Tuesday, September 17, 2019 7:04 AM
    Thursday, September 12, 2019 10:57 AM
  • Thanks Visakh16 for your reply,

    I understand, but then one simple question based on the update.

    For the sample update I provide it  take in account only one simple case where there is only 1 LIKE criteria but in real I need to fetch for the same record to update the following :

     

    UPDATE bodycontent SET body = REPLACE(body, '&egrave;', 'è') WHERE body LIKE '%filename=%&egrave;%';

    UPDATE bodycontent SET body = REPLACE(body, '&eacute;', 'é') WHERE body LIKE '%filename=%&eacute;%';

    UPDATE bodycontent SET body = REPLACE(body, '&agrave;', 'à') WHERE body LIKE '%filename=%&agrave;%';

    UPDATE bodycontent SET body = REPLACE(body, '&amp;', '&') WHERE body LIKE '%filename=%&amp;%';

    UPDATE bodycontent SET body = REPLACE(body, '&rsquo;', '''') WHERE body LIKE '%filename=%&rsquo;%';

    Does it means I need have different querry for each update and run 1 update at a time or can I perfom all those update check and replace in one shot ?

    regards


    • Edited by wakefun Thursday, September 12, 2019 1:33 PM
    Thursday, September 12, 2019 1:32 PM
  • I think you may get answers here.



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 13, 2019 9:06 AM
  • Thanks Visakh16 for your reply,

    I understand, but then one simple question based on the update.

    For the sample update I provide it  take in account only one simple case where there is only 1 LIKE criteria but in real I need to fetch for the same record to update the following :

     

    UPDATE bodycontent SET body = REPLACE(body, '&egrave;', 'è') WHERE body LIKE '%filename=%&egrave;%';

    UPDATE bodycontent SET body = REPLACE(body, '&eacute;', 'é') WHERE body LIKE '%filename=%&eacute;%';

    UPDATE bodycontent SET body = REPLACE(body, '&agrave;', 'à') WHERE body LIKE '%filename=%&agrave;%';

    UPDATE bodycontent SET body = REPLACE(body, '&amp;', '&') WHERE body LIKE '%filename=%&amp;%';

    UPDATE bodycontent SET body = REPLACE(body, '&rsquo;', '''') WHERE body LIKE '%filename=%&rsquo;%';

    Does it means I need have different querry for each update and run 1 update at a time or can I perfom all those update check and replace in one shot ?

    regards


    you can merge them like this

    DECLARE @t table
    (
    SearchSTring varchar(100)
    )
    
    INSERT @t
    VALUES ('egrave'),('eacute'),('agrave'),('amp'),('rsquo')
    
    UPDATE b
    SET body  = STUFF(body,PATINDEX('&' + t.SearchSTring,b.body),LEN(t.SearchSTring) + 1,'è')
    FROM bodycontent  b
    JOIN @t t
    ON b.body LIKE '%filename=%&' + t.SearchSTring  + ';%';
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, September 17, 2019 10:55 AM