Kumar AU

Kumar AU

  • 1.9k
  • 252
  • 42.8k

How to replace While loop with other loop - SQL Performance

Jun 10 2021 11:34 AM

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 


    WHILE EXISTS(SELECT Top 1 1 FROM #tempPolicyData)
       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 + '%')
        SELECT PolicyNumber,AccountNumber,Amount
             INTO  #tempImpactedPolicyData 
              FROM #tempPolicyData where AccountNumber = @AccountNumber


       Delete FROM #tempPolicyData where AccountNumber = @AccountNumber

    Select * from #tempImpactedPolicyData


Answers (2)