none
Removing WHILE loop to boost performance RRS feed

  • Question

  • Hi,

    I have a query which runs within a loop.  Although the query is quite fast, but when run thousand of times within the loop the performance degrades and can take up to an hour.

    I was thinking one way of removing the while from below example is to use Recursive CTE.  Can anyone please suggest if this is the only way of removing query from the loop, or there are some other/better approaches?  And what would be the impact from the performance point of view, as this the reason I am trying to do that.

    The example below shows the simple query within the loop

    WHILE @counter <= 5
    BEGIN	
    	INSERT INTO @tmpFinalTable
    	SELECT ad1.observationNo, @firstObservYear + (CAST((ad1.observationNo-1) * 3 AS FLOAT) / CAST(12 AS FLOAT)) AS NumberOfYears,
    		 (@firstCoupon + (@snowballCoupon * (ad1.observationNo-1))) + 1 As KickOutValue, ad1.wkCycleNo, @counter AS mainCounter
    	FROM @anniversaryDates AS ad1
    		JOIN 
    		(SELECT MIN(anniversaryDate) AS firstAnnivDate, wkCycleNo FROM @anniversaryDates 
    		WHERE anniversaryUnd1 >= strikeValue * @kickOutLevel1 
    		GROUP BY wkCycleNo) AS ad2
    		ON ad1.anniversaryDate = ad2.firstAnnivDate AND ad1.wkCycleNo = ad2.wkCycleNo
    
    	
    	SET @counter = @counter + 1
    END

    Thanks

    Tuesday, June 17, 2014 8:40 AM

Answers

  • You should avoid loops whenever you can in T-SQL. If you want to insert five or more almost identical rows into the @tmpFinalTable, you could use the ROW_NUMBER function:

    INSERT INTO @tmpFinalTable
     SELECT ad1.observationNo, @firstObservYear + (CAST((ad1.observationNo-1) * 3 AS FLOAT) / CAST(12 AS FLOAT)) AS NumberOfYears,
       (@firstCoupon + (@snowballCoupon * (ad1.observationNo-1))) + 1 As KickOutValue, ad1.wkCycleNo, ROW_NUMBER() OVER (ORDER BY ad1.observationNo)
     FROM @anniversaryDates AS ad1
      JOIN 
      (SELECT MIN(anniversaryDate) AS firstAnnivDate, wkCycleNo FROM @anniversaryDates 
      WHERE anniversaryUnd1 >= strikeValue * @kickOutLevel1 
      GROUP BY wkCycleNo) AS ad2
      ON ad1.anniversaryDate = ad2.firstAnnivDate AND ad1.wkCycleNo = ad2.wkCycleNo
    

    • Marked as answer by Alex Teslin Wednesday, June 18, 2014 8:05 PM
    Tuesday, June 17, 2014 9:01 AM

All replies

  • You should avoid loops whenever you can in T-SQL. If you want to insert five or more almost identical rows into the @tmpFinalTable, you could use the ROW_NUMBER function:

    INSERT INTO @tmpFinalTable
     SELECT ad1.observationNo, @firstObservYear + (CAST((ad1.observationNo-1) * 3 AS FLOAT) / CAST(12 AS FLOAT)) AS NumberOfYears,
       (@firstCoupon + (@snowballCoupon * (ad1.observationNo-1))) + 1 As KickOutValue, ad1.wkCycleNo, ROW_NUMBER() OVER (ORDER BY ad1.observationNo)
     FROM @anniversaryDates AS ad1
      JOIN 
      (SELECT MIN(anniversaryDate) AS firstAnnivDate, wkCycleNo FROM @anniversaryDates 
      WHERE anniversaryUnd1 >= strikeValue * @kickOutLevel1 
      GROUP BY wkCycleNo) AS ad2
      ON ad1.anniversaryDate = ad2.firstAnnivDate AND ad1.wkCycleNo = ad2.wkCycleNo
    

    • Marked as answer by Alex Teslin Wednesday, June 18, 2014 8:05 PM
    Tuesday, June 17, 2014 9:01 AM
  • Thanks Magnus, I re-designing the chain of CTEs and queries.  The above example is the simplification and I will post the exact code to make it clearer what I am doing.
    Wednesday, June 18, 2014 8:05 PM