SQL Server procedure, to find and replace data inside big table

Question

I need to write a store procedure to update link reference in my DB. Link can be contained in few nvarchar field that contains JSONs (that could contains some urls).

To do so i update the tables in batches of 8129 items per iteration, so that the machine will not hang (in theory).

But now the code seems to hang anyway, it will not print any message and the procedure keep running (without affecting any data) for many minutes, untill i have to kill the procedure (that in meanwhile seems to haven't affect any data).

If i try to use the same logic on a toy example, i get no issue, so i think my problem is due the fact the table is big (few hundred thousand of rows).

Here the minimal example that is working, the exact same code on bigger table hang apparently doing nothing (tested with SQL Server 2019).

Procedure code :

ALTER PROCEDURE [dbo].[SiteUrlChangeURL]
    @FullOldUrl nvarchar(500),
    @FullNewUrl nvarchar(500)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SET @FullOldUrl = ISNULL(@FullOldUrl,'');
    SET @FullNewUrl = ISNULL(@FullNewUrl,'');

    IF ( LEN(@FullOldUrl) <= 0 OR LEN(@FullNewUrl) <= 0 )
    BEGIN 
        PRINT('Invalid parameters');
        RETURN 1;
    END

    --ARTICLE
    RAISERROR ('updating articles',0,1) WITH NOWAIT;
    WHILE 1=1
    BEGIN
        UPDATE TOP (8196) [dbo].[tbl_ana_Articles]
           SET [_ATTRIBUTES]    = REPLACE([_ATTRIBUTES]   , @FullOldUrl, @FullNewUrl)
              ,[_DOCUMENTS]     = REPLACE([_DOCUMENTS]    , @FullOldUrl, @FullNewUrl)
              ,[_SEO]           = REPLACE([_SEO]          , @FullOldUrl, @FullNewUrl)
              ,[_TRANSLATIONS]  = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl)
              ,[_TAGS]          = REPLACE([_TAGS]         , @FullOldUrl, @FullNewUrl)
              ,[_NOTES]         = REPLACE([_NOTES]        , @FullOldUrl, @FullNewUrl)
        WHERE 
            [_ATTRIBUTES]   like '%' + @FullOldUrl + '%' OR
            [_DOCUMENTS]    like '%' + @FullOldUrl + '%' OR
            [_SEO]          like '%' + @FullOldUrl + '%' OR
            [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR
            [_TAGS]         like '%' + @FullOldUrl + '%' OR
            [_NOTES]        like '%' + @FullOldUrl + '%'

        IF (@@ROWCOUNT <= 0)
        BEGIN
            BREAK;
        END
    END

 RETURN 0;

Example :

CREATE TABLE [dbo].[tbl_ana_Articles](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ID_BRAND] [int] NOT NULL,
    [CODE] [nvarchar](40) NOT NULL,
    [CODFOR] [nvarchar](40) NOT NULL,
    [COD_ALT01] [nvarchar](50) NOT NULL,
    [COD_ALT02] [nvarchar](50) NOT NULL,
    [COD_ALT03] [nvarchar](50) NOT NULL,
    [ID_UOM] [int] NOT NULL,
    [IS_ACTIVE] [bit] NOT NULL,
    [_ATTRIBUTES] [nvarchar](max) NOT NULL,
    [_DOCUMENTS] [nvarchar](max) NOT NULL,
    [_SEO] [nvarchar](max) NOT NULL,
    [_TRANSLATIONS] [nvarchar](max) NOT NULL,
    [_TAGS] [nvarchar](max) NOT NULL,
    [_NOTES] [nvarchar](max) NOT NULL,
    [_METADATA] [nvarchar](max) NOT NULL,
    [IS_B2B] [bit] NOT NULL,
    [IS_B2C] [bit] NOT NULL,
    [IS_PROMO] [bit] NOT NULL,
    [IS_NEWS] [bit] NOT NULL,
    [CAN_BE_RETURNED] [bit] NOT NULL,
    [IS_SHIPPABLE] [bit] NOT NULL,
    [HAS_SHIPPING_COSTS] [bit] NOT NULL,
    [IS_PURCHEASABLE] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_ana_articles] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



INSERT INTO [dbo].[tbl_ana_Articles]
           ([ID_BRAND]
           ,[CODE]
           ,[CODFOR]
           ,[COD_ALT01]
           ,[COD_ALT02]
           ,[COD_ALT03]
           ,[ID_UOM]
           ,[IS_ACTIVE]
           ,[_ATTRIBUTES]
           ,[_DOCUMENTS]
           ,[_SEO]
           ,[_TRANSLATIONS]
           ,[_TAGS]
           ,[_NOTES]
           ,[_METADATA]
           ,[IS_B2B]
           ,[IS_B2C]
           ,[IS_PROMO]
           ,[IS_NEWS]
           ,[CAN_BE_RETURNED]
           ,[IS_SHIPPABLE]
           ,[HAS_SHIPPING_COSTS]
           ,[IS_PURCHEASABLE])
     VALUES
           (1
           ,'COD1'
           ,'SUPPLIER1'
           ,'CATEGORY1'
           ,'CATEGORY1-BIS'
           ,'CATEGORY2'
           ,1
           ,1
           ,'{ "url" : "https://old.com" }'
           ,''
           ,''
           ,''
           ,''
           ,''
           ,''
           ,1
           ,0
           ,0
           ,0
           ,1
           ,1
           ,0
           ,1);
           
    DECLARE @FullOldUrl AS NVARCHAR(50) = 'https://old.com';
    DECLARE @FullNewUrl AS NVARCHAR(50) = 'https://new.com';

    --ARTICLE
    PRINT('updating articles');
    WHILE 1=1
    BEGIN
        UPDATE TOP (8196) [dbo].[tbl_ana_Articles]
           SET [_ATTRIBUTES]    = REPLACE([_ATTRIBUTES]   , @FullOldUrl, @FullNewUrl)
              ,[_DOCUMENTS]     = REPLACE([_DOCUMENTS]    , @FullOldUrl, @FullNewUrl)
              ,[_SEO]           = REPLACE([_SEO]          , @FullOldUrl, @FullNewUrl)
              ,[_TRANSLATIONS]  = REPLACE([_TRANSLATIONS] , @FullOldUrl, @FullNewUrl)
              ,[_TAGS]          = REPLACE([_TAGS]         , @FullOldUrl, @FullNewUrl)
              ,[_NOTES]         = REPLACE([_NOTES]        , @FullOldUrl, @FullNewUrl)
        WHERE 
            [_ATTRIBUTES]   like '%' + @FullOldUrl + '%' OR
            [_DOCUMENTS]    like '%' + @FullOldUrl + '%' OR
            [_SEO]          like '%' + @FullOldUrl + '%' OR
            [_TRANSLATIONS] like '%' + @FullOldUrl + '%' OR
            [_TAGS]         like '%' + @FullOldUrl + '%' OR
            [_NOTES]        like '%' + @FullOldUrl + '%'

        IF (@@ROWCOUNT <= 0)
        BEGIN
            BREAK;
        END
    END
    
    SELECT * FROM [dbo].[tbl_ana_Articles]
    PRINT('Finished');

Here the execution plan produced by the toy example (i can't get the execution plan of real scenario).

https://www.brentozar.com/pastetheplan/?id=SJhVTcMTo

I am really puzzled about what caused that issue

--EDIT :

i have run the procedure again, and discovered that if i let run long enough (~30 min) i get the right behaviour. So apparently i have a performance issue here. I am not really intereseted in performance here, because the procedure is used only rarely (manually) during mantainance (only if the site change domain).

But i am curious if I made some blunder here, to get such low performance

Answer 1

worker

I might try to avoid hitting the main table over and over again on each update, for two reasons featured in the query plan:

1. A filter operator because predicates can't be pushed when dealing with max data types

2. A table spool for Halloween Protection

It would potentially be a good idea to provide some manual phase separation by storing a list of primary keys to work off of in a temp table:

SELECT
    a.ID
INTO #tbl_ana_Articles
FROM [dbo].[tbl_ana_Articles] AS a
WHERE 
    [_ATTRIBUTES]   like N'%' + @FullOldUrl + N'%' OR
    [_DOCUMENTS]    like N'%' + @FullOldUrl + N'%' OR
    [_SEO]          like N'%' + @FullOldUrl + N'%' OR
    [_TRANSLATIONS] like N'%' + @FullOldUrl + N'%' OR
    [_TAGS]         like N'%' + @FullOldUrl + N'%' OR
    [_NOTES]        like N'%' + @FullOldUrl + N'%';

At the very least, it would help give you better clues as to which portion of the query is truly slow: finding the data, or updating th data.

Adding a primary key to the temp table may also speed things along, but that's a horse for you to race.

Keeping track of the current primary key values as Martin suggested by following Michael's post would also be useful, of course:

• Take Care When Scripting Batches

There are other things to experiment with as well such as PAGLOCK and RECOMPILE hints on the UPDATE, the size of each batch, parallelism, batch mode on the initial SELECT to populate the temp table, etc.

Like all query tuning suggestions, whether they're successful or not will depend on a variety of local factors, as a wise bird once said.