Return to Question

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;

--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).

--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).

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 (in which may be there are some urls).

To do so i update the tables in batches of 8129 items per iteration, so that the machine will not hang. Here the code i have written :

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
    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
 RETURN 0

I used a very similar code previously (to update path) without any issues (to be fair the tables previously involved was very very small). But now the code seems to hang, it will not print any message and the procedure keep running (without affecting any data).

I am trying to udpate to very different url (so one is not a substring of the other), and the above stored procedure seems to hang doing nothing and can't understand why.

Eg : FullOldUrl = 'https://old.com' FullNewUrl = 'https://new.com'

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).

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