Tsql batch script does not stop

Question

Post reply

Tsql batch script does not stop

murad.ahmed

SSC Veteran

Points: 271

September 15, 2022 at 3:41 pm

Go to Answer

#4088171

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 1 year, 7 months ago by murad.ahmed. Reason: code settings

Jeff Moden

SSC Guru

Points: 1003894
More actions
September 22, 2022 at 1:30 am
Answer
#4090553
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)

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

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

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

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)

drew.allen SSC Guru Points: 76948 More actions · Answer 3

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

murad.ahmed SSC Veteran Points: 271 More actions · Answer 4

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

murad.ahmed SSC Veteran Points: 271 More actions · Answer 5

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

Ed B Hall of Fame Points: 3623 More actions · Answer 6

Ed B

Hall of Fame

Points: 3623

September 21, 2022 at 10:03 pm

#4090524

Do you have any null or empty string IFSC values?

murad.ahmed SSC Veteran Points: 271 More actions · Answer 7

murad.ahmed

SSC Veteran

Points: 271

September 28, 2022 at 7:43 am

#4092797

thanks all for help on the logic !

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

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)