Menu
Is it a good idea to use while loop instead of a cursor?
What are the advantages/disadvantages of cursors?
557
Cursors can be faster than a while loop but they do have more overhead. It is we can do RowWise validation or in other way you can perform operation on each Row. It is a Data Type which is used to define multi-value variable. Cursors can be faster than a while loop but at the cost of more overhead.
While SQL While loop is quicker than a cursor, reason found that cursor is defined by DECLARE CURSOR. Every emphasis of the loop will be executed inside system memory and consuming required server assets.
Advantages of using Cursor:Cursors can provide the first few rows before the whole result set is assembled. Without using cursors, the entire result set must be delivered before any rows are displayed by the application. So using cursor, better response time is achieved.
I'm following this bit of advice:
[...] which is better: cursors or WHILE loops? Again, it really depends on your situation. I almost always use a cursor to loop through records when necessary. The cursor format is a little more intuitive for me and, since I just use the constructs to loop through the result set once, it makes sense to use the FAST_FORWARD cursor. Remember that the type of cursor you use will have a huge impact on the performance of your looping construct.
— Tim Chapman in Comparing cursor vs. WHILE loop performance in SQL Server 2008
The linked article contains simple examples of how to implement each approach.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
