Tsql batch script does not stop

  • We have the following update script we are trying to do in batches.

    Currently the execution never stops running - so needed help understanding what's wrong with the logic or syntax thats missing.

    --Create Temp table
    create table #TempIFSC
    (
    [EnrolledPaymentMethodAccountId] uniqueidentifier,
    [PaymentAccountId] uniqueidentifier,
    [ExternalSystemId] int,
    [EnrolledPaymentMethodAccountStatusId] int,
    [Extension] xml,
    [EnrollmentAccountRevisionId] int,
    [BankName] NVARCHAR(100) NULL,
    [BankBranch] NVARCHAR(100) NULL,
    [IFSC] NVARCHAR(100) NULL
    )

    --Insert record into temp table who does not have IFSC code and currency INR
    Insert Into #TempIFSC
    Select EP.[EnrolledPaymentMethodAccountId],EP.[PaymentAccountId],EP.[ExternalSystemId],EP.[EnrolledPaymentMethodAccountStatusId],CAST(EP.[Extension] AS XML),EP.[EnrollmentAccountRevisionId],NULL,NULL,NULL
    from [dbo].[EnrolledPaymentMethodAccount] EP With (NOLOCK)
    INNER JOIN [dbo].[PaymentAccount] PA With (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId and PA.CurrencyCode = 'INR'
    where ISNULL(CAST(EP.Extension AS XML).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''
    and EP.ExternalSystemId = 52
    and EP.EnrolledPaymentMethodAccountStatusId = 1

    --Update the BankName and BankBranch value in temp table
    Declare @Rowcount INT = 1;
    WHILE (@Rowcount > 0)
    Begin
    UPDATE TOP (4999) #TempIFSC
    SET
    BankName = b.Name,
    BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)')
    FROM #TempIFSC TMP
    INNER JOIN [dbo].[Bank] b ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)')
    Where b.ExternalSystemId = TMP.ExternalSystemId and b.CurrencyCode = 'INR'

    SET @Rowcount = @@ROWCOUNT;

    print @Rowcount

    CHECKPOINT;
    End

    --Update IFSC value in temp table
    Declare @IFSCRowcount INT = 1;
    WHILE (@IFSCRowcount > 0)
    BEGIN
    UPDATE TOP (4999) #TempIFSC
    SET IFSC = IM.IFSC
    FROM #TempIFSC TMP
    INNER JOIN [taurus].[IFSCMasterList] IM (NOLOCK) ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName

    SET @IFSCRowcount = @@ROWCOUNT;

    CHECKPOINT; --<-- to commit the changes with each batch
    End

    --Remove blank node of IFSC
    Declare @XMLRowcount INT = 1;

    WHILE (@XMLRowcount > 0)
    BEGIN
    DECLARE @NodeName NVARCHAR(500) = 'NameValueEntity'
    Update TOP (4999) #TempIFSC
    SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]')
    FROM #TempIFSC
    Where Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1 and ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

    SET @XMLRowcount = @@ROWCOUNT;
    Print @XMLRowcount
    CHECKPOINT; --<-- to commit the changes with each batch
    END

    --Update extension value in temp table
    Declare @CodeRowcount INT = 1;

    WHILE (@CodeRowcount > 0)
    BEGIN
    Update TOP (4999) #TempIFSC
    SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
    into (/ArrayOfNameValueEntity)[1]')
    FROM #TempIFSC
    WHERE ISNULL(IFSC, '') <> ''

    SET @CodeRowcount = @@ROWCOUNT;

    CHECKPOINT; --<-- to commit the changes with each batch
    END


    • This topic was modified 2 weeks, 1 day ago by  murad.ahmed. Reason: code settings
  • Nothing stands  out, but that's a lot of code, and not formatted (code button in the edit box).

    However, is this running? Do you  have open transactions? Depending on data sizes, this might not just run as quick as you expect.

    I'd add logging in here, capturing some rowcounts or ranges of things updated and put those in a table, so you can track progress.

  • In the following snippet of your code, tell me why it won't just update the same 4999 rows forever?

    --Update the BankName and BankBranch value in temp table
    Declare @Rowcount INT = 1;
    WHILE (@Rowcount > 0)
    Begin
    UPDATE TOP (4999) #TempIFSC
    SET
    BankName = b.Name,
    BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)')
    FROM #TempIFSC TMP
    INNER JOIN [dbo].[Bank] b ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)')
    Where b.ExternalSystemId = TMP.ExternalSystemId and b.CurrencyCode = 'INR'

    SET @Rowcount = @@ROWCOUNT;

    print @Rowcount

    CHECKPOINT;
    End​

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • You're not checking to see whether the bank name has already been updated, so you keep updating the same 4999 records repeatedly.  Add something to the WHERE clause to check whether the bank name is NULL.

    There are probably similar issues throughout.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • thank you all for advice

    code formatted for code apologies.

    I will look down the where clause to check if NULL

    and redirect the where clause to different rows for each execution

     

  • So have made changes to the original script but the last while loops end ups in an infinite loop still but the rest is a lot better

    --Create Temp table
    create table #TempIFSC
    (
    [EnrolledPaymentMethodAccountId] uniqueidentifier,
    [PaymentAccountId] uniqueidentifier,
    [ExternalSystemId] int,
    [EnrolledPaymentMethodAccountStatusId] int,
    [Extension] xml,
    [EnrollmentAccountRevisionId] int,
    [BankName] NVARCHAR(100) NULL,
    [BankBranch] NVARCHAR(100) NULL,
    [IFSC] NVARCHAR(100) NULL,
    [IsUpdate] bit NULL
    )

    --Insert record into temp table who does not have IFSC code and currency INR
    Insert Into #TempIFSC
    Select EP.[EnrolledPaymentMethodAccountId],EP.[PaymentAccountId],EP.[ExternalSystemId],EP.[EnrolledPaymentMethodAccountStatusId],CAST(EP.[Extension] AS XML),EP.[EnrollmentAccountRevisionId],NULL,NULL,NULL,0
    from [dbo].[EnrolledPaymentMethodAccount] EP With (NOLOCK)
    INNER JOIN [dbo].[PaymentAccount] PA With (NOLOCK) ON EP.PaymentAccountId = PA.PaymentAccountId and PA.CurrencyCode = 'INR'
    where ISNULL(CAST(EP.Extension AS XML).value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''
    and EP.ExternalSystemId = 52
    and EP.EnrolledPaymentMethodAccountStatusId = 1

    --Update the BankName and BankBranch value in temp table
    Declare @Rowcount INT = 1;
    WHILE (@Rowcount > 0)
    Begin
    UPDATE TOP (4999) #TempIFSC
    SET
    BankName = b.Name,
    BankBranch = TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),
    IFSC = IM.IFSC,
    IsUpdate = 1
    FROM #TempIFSC TMP
    INNER JOIN [dbo].[Bank] b WITH(NOLOCK) ON b.ExternalSystembankId = TMP.Extension.value('(//*[local-name()="Value"])[2]', 'NVARCHAR(255)') AND b.ExternalSystemId = TMP.ExternalSystemId
    INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK) ON b.Name = IM.BankName AND TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)') = IM.BankBranchName
    Where b.CurrencyCode = 'INR'
    AND b.Name IS NOT NULL
    AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[3]', 'NVARCHAR(255)'),'') != ''

    SET @Rowcount = @@ROWCOUNT;

    print @Rowcount

    RETURN
    End

    --Remove the records which does not have BankName or BankBranch
    DELETE FROM #TempIFSC WHERE BankName IS NULL OR BankBranch IS NULL OR IFSC IS NULL

    --Update IFSC value in temp table
    --Declare @IFSCRowcount INT = 1;
    --WHILE (@IFSCRowcount > 0)
    --BEGIN
    --UPDATE TOP (4999) #TempIFSC
    --SET IFSC = IM.IFSC
    --FROM #TempIFSC TMP
    --INNER JOIN [taurus].[IFSCMasterList] IM WITH(NOLOCK)
    --ON TMP.BankBranch = IM.BankBranchName and TMP.BankName = IM.BankName
    --WHERE IM.BankName IS NOT NULL
    --AND IM.IFSC IS NOT NULL
    --AND IM.BankBranchName IS NOT NULL
    ----AND TMP.Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1
    ----OR ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

    --SET @IFSCRowcount = @@ROWCOUNT;

    --CHECKPOINT; --<-- to commit the changes with each batch
    --End


    --Remove blank node of IFSC
    Declare @XMLRowcount INT = 1;

    WHILE (@XMLRowcount > 0)
    BEGIN
    DECLARE @NodeName NVARCHAR(500) = 'NameValueEntity'
    Update TOP (4999) #TempIFSC
    SET Extension.modify('delete /ArrayOfNameValueEntity/*[local-name(.) eq sql:variable("@NodeName")][6]')
    FROM #TempIFSC
    Where Extension.exist(N'/*/NameValueEntity/Name[text()="IFSCCode"]') = 1 and ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

    SET @XMLRowcount = @@ROWCOUNT;
    Print @XMLRowcount
    CHECKPOINT; --<-- to commit the changes with each batch
    END

    --Update extension value in temp table
    Declare @CodeRowcount INT = 1;

    WHILE (@CodeRowcount > 0)
    BEGIN
    Update TOP (4999) #TempIFSC
    SET Extension.modify('insert <NameValueEntity><Name>IFSCCode</Name><Value>{sql:column("#TempIFSC.IFSC")}</Value></NameValueEntity>
    into (/ArrayOfNameValueEntity)[1]')
    --FROM #TempIFSC
    WHERE ISNULL(IFSC,'') <> ''
    AND BankName IS NOT NULL
    AND IsUpdate = 1
    AND ISNULL(Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') = ''

    SET @CodeRowcount = @@ROWCOUNT;

    CHECKPOINT; --<-- to commit the changes with each batch
    END

    --Actual table updated from ##TempIFSC
    Declare @TblRowcount INT = 1;

    --WHILE (@TblRowcount > 0)
    --BEGIN
    --Update TOP (4999) [dbo].[EnrolledPaymentMethodAccount_20220921]
    --SET [Extension] = CAST(TMP.[Extension] AS nvarchar(max))
    --FROM [dbo].[EnrolledPaymentMethodAccount_20220921] EP WITH(NOLOCK)
    --INNER JOIN #TempIFSC TMP WITH(NOLOCK) ON EP.[PaymentAccountId] = TMP.PaymentAccountId AND EP.[EnrolledPaymentMethodAccountId] = TMP.[EnrolledPaymentMethodAccountId]
    --Where EP.[ExternalSystemId] = TMP.ExternalSystemId
    --AND EP.[EnrolledPaymentMethodAccountStatusId] = TMP.EnrolledPaymentMethodAccountStatusId
    --AND EP.[EnrollmentAccountRevisionId] = TMP.EnrollmentAccountRevisionId
    --AND TMP.IsUpdate = 1
    --AND TMP.IFSC <> '' OR TMP.IFSC IS NULL
    --AND TMP.BankName IS NOT NULL AND TMP.BankBranch IS NOT NULL
    --AND ISNULL(TMP.Extension.value('(//*[local-name()="Value"])[6]', 'NVARCHAR(255)'),'') != ''

    --SET @TblRowcount = @@ROWCOUNT;

    --CHECKPOINT; --<-- to commit the changes with each batch
    --END

    Select * from #TempIFSC WITH(NOLOCK)

    DROP Table #TempIFSC
  • Do you have any null or empty string IFSC values?

  • Maybe this ...

    AND TMP.IFSC <> '' OR TMP.IFSC IS NULL

    ... should be  this ...

    AND (TMP.IFSC <> '' OR TMP.IFSC IS NULL)

    ???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • thanks all for help on the logic !

  • murad.ahmed wrote:

    thanks all for help on the logic !

    You're welcome.  Were you able to fix your problem and, if so, what was the fix you employed?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply