question

GHaider avatar image
GHaider asked

Is WHILE loop better than CURSOR?

Hi, I wanted to know that is WHILE loop better than CURSOR? Thanks.
sql-servertsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
Not really. In terms of what it is doing, a while loop and a cursor both do the same thing, they operate on one row at a time. A lot of people when trying to remove cursor-based code, simply replace it with a while loop, in the hope that it will run faster, because it's not a *nasty* cursor. In fact I think I've seen examples where a cursor is faster than a while loop, as a cursor is a data structure designed for that very purpose. What they should be doing is re-writing so that the code operates in a set-based fashion. Write the query declaratively, not procedurally. I've recently taken a chunk of code that, amongst other things, had nested cursors - execution times have fallen from 40 mins to 5 seconds. I'm sure there's bound to be some circumstances where a while loop is preferred over a cursor, just as there are ones where a cursor beats set-based code hands down, but they will be edge cases.
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

In that case you've got the perfect opportunity to see the real difference between a cursor based solution and a 'replacement' while loop.
4 Likes 4 ·
Replacing a nasty cursor with a nasty while loop, LOL! Or in other words: always use set-based logic unless you really can't... +1 to Kev
1 Like 1 ·
Can you plz give any example where CURSOR was better than WHILE loop. Thanks.
0 Likes 0 ·
I don't have one - but that doesn't mean there isn't one. What are you trying to achieve?
0 Likes 0 ·
I just got the instruction from my senior that try to replace the cursor with WHILE loop and see the difference. At this time, I cannot change the whole design becuse it can take very long time
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
In addition to @Kev Riley 's answer, you should give a try to FAST_FORWARD CURSOR approach (If not used already). It could do wonders if you cannot do the design changes. And for the example where WHILE LOOP is slower, go to the following hyperlink http://www.techrepublic.com/blog/datacenter/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/1741
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for that. Cursors have their use (sometimes). I have them in index maintenance scripts for example. They could perhaps be rewritten to not use cursors, but for tasks that run scheduled outside bussiness hours I won't bother, I just make sure to use the fastest, least resource consuming cursor type I can find.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
+1 to @Kev Riley When I started at my former position, about 10 years ago, there were tons of cursor-based stored procedures. Some of them were created for batch-loading data and I didn't really bother about them, because their performance was OK (they ran outside of bussiness hours). But there were also cursors in online operations, like a statistical module, where the users were complaining over response times. Knowing what I know now, I would replace them with set-based logic. In most cases it would be rather straight forward, it would have been possible to replace the whole row-by-row-insert-to-temporary-tables-and-query-them with one single select. But some would have required for example a Tally table. What I did at that time was to replace cursors with WHILE-loops. Not the prettiest solution, but - as it turned out - a MUCH faster solution. The reason for it being faster was that the cursors were only used to extract a date period, which was already known and could be replaced with a loop from 1 to 12, or from 1 to 7 or from 1 to 365/366, all depending on the operation at hand. What was saved with a WHILE-loop instead of a cursor at that time was the totally unnecessary initial operation to query table(s) to extract the period data, and maintaining it. But I want to stress that using a set based approach would have been a much, much, much better solution. If I saved time from 20 to 10 seconds per query with the WHILE-loop, I would have saved time from 10 to 0 seconds using a set based approach. Perhaps you're right - it might take more development time to replace a cursor with a set-based approach than replacing it with a WHILE-loop. But that won't have to be the case. I would either try to convince my manager to allow me to test also the set based approach on a few procedures, or simply do it without even bothering the manager, and then, when proven that they perform much better and also that they won't demand much more working hours build my case from there. Except the fact that set-based almost always beat loop-based logic in SQL Server, another advantage is that there are almost always fewer lines of code to maintain in a set-based procedure than a loop-based procedure.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
I'm going to go into the uncomfortable position of respectfully disagreeing with @Kev Riley at least partially. I want to reiterate what he said about trying to do things in a set based fashion. That will virtually always be much faster and more effecient than either a cursor or while based loop. I fully agree on that part. Where I disagree is that when you really must operate on the rows one by one, then I think a while loop is better than cursor. I have yet to see a time when a cursor is faster than a while loop (though that doesn't mean they don't exist!) but I have seen times where a while loop is faster than a cursor. More significantly, I think that most programmers will find a while loop much easier to read. Remember that most of us learned to program outside of a database before learning to deal with databases. A while loop is a very natural entity that occurs in some form in virtually every language out there, cursors do not have that property. But I want to once again emphasize that while I would definitely choose a while loop over a cursor, I would only use either if it could not be done in set based operations. I normally encounter this as part of a data cleaning process where I may need to execute DDL statements (sometimes with variations depending on the structure of the table) against numerous tables in sequence, or in the (pre-Powershell) days when I sometimes found it made sense to do limited file operations from inside of T-SQL so I was looping through lists of file names.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. I don't think we necessarily disagree. I'm sure for the majority of cases, set-based beats while-loops beats cursor, but I've been burnt too many times to say that there can never be a situation where either one of them wins hands down! Never say never. You're right as well, if I needed to do any kind of row-by-row, I'd choose a while-loop over a cursor any day, but that's probably down to the fact that I can remember the syntax a lot easier! Good to hear your take on this too.
1 Like 1 ·
Ch.Rashid avatar image
Ch.Rashid answered
while loop,do while, for loops advantage on one and other ??????????????? plz ans me
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.