Dear Team, Could you please suggest me why the below query takes more time to run and How do I improve the performance on the same.
Here I am trying loop a temp table (#tempPolicyData) which has data and then using this temp table I am trying to insert into another final temp table if the IF condition returns any data. So My final result is 2nd Temp table #tempImpactedPolicyData
Kindly find the same Data for #tempPolicyData with table structure
https://i.stack.imgur.com/lcrj6.png
WHILE EXISTS(SELECT Top 1 1 FROM #tempPolicyData) BEGIN DECLARE @AccountNumber NVARCHAR(50) DECLARE @CreatedDate NVARCHAR(10) SELECT TOP 1 @AccountNumber =AccountNumber,@CreatedDate=CreateTimestamp FROM #tempPolicyData
IF Exists( Select Top 1 1 FROM [dbo].[CheckRecords] where DetailRecord like '%' + @AccountNumber + '%') BEGIN SELECT PolicyNumber,AccountNumber,Amount INTO #tempImpactedPolicyData FROM #tempPolicyData where AccountNumber = @AccountNumber
END
Delete FROM #tempPolicyData where AccountNumber = @AccountNumber END
Select * from #tempImpactedPolicyData