SQL Server Performance Forum – Threads Archive

WHILE loop!!

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
Regards,
Shiva
(+91-98948-45445)

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. Madhu

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.

But a while loop is a cursor in disguise. 99% of the time, there is a set based alternative. Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

Not all WHILE loops are cursors in disguise.

Ofcourse, those that does not loop through the rows in a table. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Roji. P. Thomas SQL Server MVP http://toponewithties.blogspot.com</a>

Be careful, next time you’ll say that an IF construct is a cursor in disguise.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

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=’

‘ /> thnk u buddies.. thx 4 ur kind rply..

Shiva, 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%).

]]>