Rewriting a Cursor as a While Loop

Question

Hi,

I have rewritten a cursor into WHILE loops to reduce the execution time. But I haven't found any major differences after converting the cursor in to a while loop.Can someone please suggest some ideas to improve the performance of the query.

My actual cursor.

Two  table variables are used for this cursor. I am inserting the values into the second  table variable by looping through the first  table  variable after doing some calculations. That's why cursor is used.

1 : DECLARE @NewCastLength TABLE (RowID INT IDENTITY(1,1),CastID INT, AvgLength FLOAT, SumLength FLOAT, WeightCast FLOAT, DeepBedFilterID INT ,CastStartDate DATETIME, furncaeID int)       

2: DECLARE @CummulCastLength TABLE (CastID INT, WeightCast FLOAT, DeepBedFilterID INT ,CastStartDate DATETIME, Cummul Float) 
DECLARE @CummulCastID INT          
DECLARE @CummulWeightCast FLOAT          
DECLARE @CummulDeepBedFilterID INT          
DECLARE @CummulCastStartDate DateTime          
DECLARE @newOldDate DATETIME = @newStartDate          
DECLARE @Cummul FLOAT          
declare @FurnaceID int          
DECLARE @ActualDeepBedFilterID INT          
declare @prevDBFID int= (select top 1 NCL.DeepBedFilterID from @NewCastLength NCL)          
set @PrevActualDeepBedFilterID = @prevDBFID
DECLARE @newCumm FLOAT  
DECLARE @NewDeepBedFilterID INT           
Declare @PrevCommul float   

                             
  SET PrevCommul  =0

DECLARE @getCummulDeepBedFilterID CURSOR   --Name of the Cursor       
SET @getCummulDeepBedFilterID = CURSOR FOR          
SELECT CastID , WeightCast , DeepBedFilterID  , CastStartDate FROM @NewCastLength ncl          
OPEN @getCummulDeepBedFilterID          
FETCH NEXT          
FROM @getCummulDeepBedFilterID INTO @CummulCastID , @CummulWeightCast, @CummulDeepBedFilterID ,@CummulCastStartDate          

WHILE @@FETCH_STATUS = 0           
BEGIN          
set @ActualDeepBedFilterID=@CummulDeepBedFilterID          
SET  @Cummul=@PrevCommul;          
if @ActualDeepBedFilterID  is not null          
      WHILE @@FETCH_STATUS = 0 and (@CummulDeepBedFilterID = @ActualDeepBedFilterID )          
      BEGIN          

            SET  @Cummul= isnull(@Cummul,0)+isnull(@CummulWeightCast,0)          
            INSERT INTO @CummulCastLength(CastID, WeightCast, DeepBedFilterID, CastStartDate, Cummul)          
            VALUES (@CummulCastID , @CummulWeightCast , @CummulDeepBedFilterID  ,@CummulCastStartDate , @Cummul )           
            FETCH NEXT          
            FROM @getCummulDeepBedFilterID INTO @CummulCastID, @CummulWeightCast, @CummulDeepBedFilterID,@CummulCastStartDate           
      END             
  if @CummulDeepBedFilterID <> @ActualDeepBedFilterID SET            
  @Cummul= isnull(@CummulWeightCast,0)          
else            
 SET  @Cummul= 0--isnull(@CummulWeightCast,0)           
 INSERT INTO @CummulCastLength(CastID, WeightCast, DeepBedFilterID, CastStartDate, Cummul)          
    VALUES (@CummulCastID , @CummulWeightCast , @CummulDeepBedFilterID  ,@CummulCastStartDate , @Cummul )           
    FETCH NEXT          
    FROM @getCummulDeepBedFilterID INTO @CummulCastID, @CummulWeightCast, @CummulDeepBedFilterID,@CummulCastStartDate             
END          
CLOSE @getCummulDeepBedFilterID          
DEALLOCATE @getCummulDeepBedFilterID   

I have rewritten the above cursor as :

 DECLARE  @rowCount INT,@currentRow  INT,@SomeID  INT   
 SELECT @rowCount = @@RowCount, @currentRow =  1   

           

WHILE @currentRow<=@rowCount


BEGIN

 SET  @Cummul=@PrevCommul;  

  SELECT @SomeID =  CastID,@CummulWeightCast=WeightCast,@NewDeepBedFilterID=DeepBedFilterID,@CummulCastStartDate=CastStartDate FROM @NewCastLength WHERE RowID =  @currentRow


  if @ActualDeepBedFilterID  is not null  
  BEGIN
  SET  @newCumm= isnull(@Cummul,0)+isnull(@CummulWeightCast,0) 
  INSERT INTO @CummulCastLength(CastID, WeightCast, DeepBedFilterID, CastStartDate, Cummul)          
  VALUES (@SomeID ,  @CummulWeightCast,@NewDeepBedFilterID,@CummulCastStartDate,@newCumm)
    SET  @currentRow = @currentRow + 1

  END
  ELSE
  SET  @newCumm= isnull(@Cummul,0)+isnull(@CummulWeightCast,0) 
   INSERT INTO @CummulCastLength(CastID, WeightCast, DeepBedFilterID, CastStartDate, Cummul)          
    VALUES (@SomeID ,  @CummulWeightCast,@NewDeepBedFilterID,@CummulCastStartDate,@newCumm )
      SET  @currentRow = @currentRow + 1

END

But when I have modified the cursor into while loops execution time has reduced from 19 seconds to 16 seconds which can not be considered as a major improvement. i have referred the article : http://adamhutson.com/2009/10/22/avoid-cursors-rewrite-into-while-loops/ for modifying the changes. Can someone please suggest me some better ideas to reduce the execution time further.

Thanks in advance.

Answer 1

>But when I have modified the cursor into while loops execution time has reduced from 19 seconds to 16 seconds

The cursor is usually (and in this case) used in WHILE loop also, so I am not surprised.

In SQL Server 2012 there are new ways to carry out cumulative calculations which would be likely faster:

http://blogs.adatis.co.uk/blogs/martynbullerwell/archive/2011/11/16/sql-server-2012-running-totals.aspx

http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

>>>>Can someone please suggest me some better ideas to reduce the execution time further.

The better way is to re-write the cursor\loop as set based solution, but in order to suggest you something accurate we need  a table structure + sample data + desired result

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Hello Kalman,

;WITH <NAME> AS expression suggested by you gave me exactly what I wanted. I have compared both results and I am getting exactly what I wanted. Thanks a lot for your help.

@Uri Dimant: Special thanks for you also for your prompt reply. I have found the solution in a very simple way. Only one table variable and a simple and most efficient ;WITH clause. :-)

Note : I have completely removed the cursor and while loops and execution time is now 0.00 seconds :-)

SELECT   CC.CastID,                 
         CC.ActualCastLength AS AvgLength,                   
        CC.ActualCastLength AS SumLength,          
        CC.ActualNumberOfStrands * CC.ActualCastLength * PP.LinearWeight/10 AS WeightCast,          
        DBF.DeepBedFilterID,          
        CC.CastStartDate,          
        B.BatchingProductionUnitID,
        0          
      --  ROW_NUMBER() OVER (PARTITION BY  B.BatchingProductionUnitID  ORDER BY CC.CastStartDate ) --to group the casts into furnaces          
FROM  Casting.Cast AS CC                      
           JOIN FurnaceCharge.Batch B ON B.BatchID = CC.BatchID          
           join Equipment.ProductionUnit P on P.ProductionUnitID = B.BatchingProductionUnitID                                             
           JOIN Product.Product AS PP                ON PP.ProductID = CC.ProductID          
         LEFT  JOIN Casting.DeepBedFilter DBF            ON DBF.DeepBedFilterID = CC.DeepBedFilterID           
         JOin Product.Dimension D on D.DimensionID = PP.DimensionID          
   join Product.ProductType PT on D.ProductTypeID = PT.ProductTypeID           
WHERE (CC.CastStartDate > @newStartDate AND CC.CastStartDate <  @EndDate)          

    AND PT.ProductTypeID IN (SELECT Item FROM dbo.Split(@ProductTypes,','))             
and CC.CastingProductionUnitID = @ProductionUnits           
ORDER BY CC.CastStartDate

  ;WITH Total AS
 (
   
   select * from @NewCastLength
 
 )
 
 
 UPDATE @NewCastLength
 SET Cummul=(isnull(@PrevCommul,0)+isnull(T.WeightCast,0))
 FROM Total T JOIN @NewCastLength N
 ON T.CastID=N.CastID  

Answer 4

If so, why CTE in that way ? I do not understand the logic behind the scene. Why not just

UPDATE @NewCastLength
 SET Cummul=(isnull(@PrevCommul,0)+isnull(WeightCast,0))

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 5

You say that your problem is resolved and execution time is now zero seconds, and the results are the same as before. I'm puzzled though. It appears that you are computing a running sum, and your cursor-less solution does not seem to do that what I can see, unless there are some pieces you have left out.

In any case, some comments on loops in general:

1) If your original solution produced correct results that was by chance. There is no ORDER BY clause in the SELECT statement for the cursor declaration, which means that SQL Server is free to serve you rows in any order.

2) Always declare your cursors like this:

    DECLARE cur CURSOR STATIC LOCAL FOR

The default is a dynamic cursor which can offer poor performance and unexpected behaviour.

3) When you need to loop, a cursor is often the best choice. A mistake people often do when implementing a poor man's cursor over a temp table is that they don't index the temp table, why retrieval of the next row is costly when the table is big.

4) WHILE loop or cursor, the big cost is the SQL statments in the loop, and they don't change, because you ditch the cursor.

5) As pointed out by others, a set-based solution is generally to prefer. However, there exists problems for which a solution with iterative elements performs better. One such example is the computation of a running sum in SQL 2008 and earlier versions, which do have direct any support for this in SQL and you need to use subquery where the performance is proportional to the square of the number of elements in a partition.

---

Erland Sommarskog, SQL Server MVP, [e-mail]