Difference between cursors and while looping

Question

Difference between cursors and while looping

sasi.tripuraneni

Old Hand

Points: 384
More actions
October 18, 2010 at 11:11 am

#225111

hello,
what is the difference between cursors and looping(while looping) and which is better in optimizing wise.........etc
thanks in advance
thanks
Sasi.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

A while loop is an execution control mechanism where a cursor is set of data (a result set ) that you can iterate through. In fact, the most common way to process rows of a cursor is by using a while loop

WHILE @@FETCH_STATUS = 0

begin

fetch next

end

The probability of survival is inversely proportional to the angle of arrival.

upstart Ten Centuries Points: 1255 More actions · Answer 2

Because a cursor is an actual object inside the database engine, there is a little overhead involved in creating the cursor and destroying it. Also, a majority of cursor operations occur in tempdb, so a heavily used tempdb will be even more overloaded with the use of cursors.

WHILE loops are as easy to use as a cursor, but sometimes these constructs are a little more difficult to read and/or to understand, as a query must occur on the base table(s) that must fetch a single next row. This type of operation is very tricky to do in situations where your underlying base table does not have a primary key column (which it should have).

WHILE loops don’t provide some of the bells and whistles that come with cursors, such as the ability to easily go “backward” in the result set. Although, I’ve never been in a situation in which I did anything other than a straight loop through a result set with a cursor.

One advantage of the WHILE loop is that no objects must be created in memory to facilitate the looping through a set of records as is necessary with a cursor.

So, 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.

If the query is more complex, then the cursor could benefit from more advanced query processing techniques such as hashing and parallelism, which will not be used for the loop (and if they will, they will not result in significant performance gain).

However, if you do not define your cursor as FAST_FORWARD, then it can easily be slower, so make sure you use this keyword in its declaration! If you cannot use a FAST_FORWARD cursor, then you really can't compare it with a loop.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Better yet to use set based logic and do away with the looping/cursor logic all together. There of course is not a catch all way to convert all looping/cursor logic to set based. When you run into a scenario where you want to eliminate the RBAR (thanks Jeff Moden) dig through this site. Somebody has probably already done this. Otherwise post another question asking for help specific to your scenario.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

upstart (10/18/2010)
Because a cursor is an actual object inside the database engine, ...

Copy-paste from http://blogs.techrepublic.com.com/datacenter/?p=1741

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sasi.tripuraneni Old Hand Points: 384 More actions · Answer 5

thanks for ur help to each and every one............

thanks

Sasi.:-)