SQL Server Performance

WHILE loop!!

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by anbeshivam, Feb 27, 2007.

  1. anbeshivam New Member

    Hi every buddy!!

    can anybody tell me, the stored procedure performance is getting down when we using WHILE loop in the same..

    is it true?

    thanks in advance

  2. madhuottapalam New Member

    it again depends... you really need to use WHILE Loop or not... if it can be avoided then it
    should be. post your procedure, while loop can reduce the performance.

  3. Adriaan New Member

    With a WHILE loop, you have to be absolutely sure that the exit criteria are met at one point. Otherwise you get in an infinite loop.

    You don't have to avoid WHILE loops, at least not as much as you should avoid cursors.
  4. Roji. P. Thomas New Member

  5. Adriaan New Member

    Not all WHILE loops are cursors in disguise.
  6. Roji. P. Thomas New Member

    Ofcourse, those that does not loop through the rows in a table. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Roji. P. Thomas<br />SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  7. Adriaan New Member

    Be careful, next time you'll say that an IF construct is a cursor in disguise.[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]
  8. anbeshivam New Member

    Some buddy told me, if u use while loop in sp.. the sp's performance getting low..-<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />thnk u buddies..<br /><br />thx 4 ur kind rply..
  9. Adriaan New Member


    There is no performance problem with the WHILE syntax, the problem is usually that people create procedures that loop through cursors (using a WHILE loop) instead of working with set-based processing - like using UPDATE or INSERT action queries.

    However, as much as you should favour set-based solutions over cursor operations - and they will cover 99% of all situations - there will always be stuff that you can only do using a cursor (that final 1%).

Share This Page