Cursor and while loop

Dear all,

I know we should use set based T-sQL programming instead of cursor and while loop, but cursor and while loop can give us finer control on the data the query return, so if we have no choice, we should use cursor or while loop to fetch out value ?

which overhead is lower ?
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:
Vikas GargAssociate Principal EngineerCommented:

Cursor is somewhat slower down the performance.

So while loop should be first preference.

However there is good article on this

Hope this would help
Vitor MontalvãoMSSQL Senior EngineerCommented:
It really depends on what you have inside your LOOP. Querying a large table inside a loop isn't good for sure. I would never choose that option for sure.
Cursors are to avoid but if you can't better use them instead use another performance killer solution.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

marrowyungSenior Technical architecture (Data)Author Commented:
Vitor Montalvão,

so you prefer cursor over loop, right ? I am reading this :

this is funny to study !

someone will ask if there are no choice what should they do ? is this article true ?
marrowyungSenior Technical architecture (Data)Author Commented:
Vikas Garg,

I see from your article:

"I see a lot of people suggest while loops instead of cursors in situations where row-based processing is required (or, at least, where folks think that row-based processing is required). Sometimes the justification is that constructing a while loop is simpler and more straightforward than constructing a cursor. Others suggest that a while loop is faster than a cursor because, well, it isn't a cursor. Of course the underlying mechanics still represent a cursor, it's just not explicitly stated that way using DECLARE CURSOR."

I can understand that concept that the effect is exactly like cursor but I don't agree that cursor user more code than loop, specially when some one need to use @variable to find the max and they usually create a temporary table to store all data and then add a sequence id column, in this way, the while loop is much longer then cursor !

cursor always 5 -6 lines, agree ?
Vikas GargAssociate Principal EngineerCommented:

WHILE @Counter <= (SELECT COUNT(*) FROM InventoryTable)
Do something

    SET @Counter = @Counter + 1


This is the sytax for While Loop

and for Cursor

DECLARE my_cursor CURSOR FOR SELECT NameID,Name FROM FamousCharacter
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @id,@name
        PRINT  (CAST(@id AS VARCHAR(5)) + '.)' + @name)      
        FETCH NEXT FROM my_cursor
CLOSE my_cursor  
DEALLOCATE my_cursor

Hope this is self explanatory
marrowyungSenior Technical architecture (Data)Author Commented:
yeah, the most simple case it is, but if we need to create a temporary table to do a much complex solution, seems the while loop code expands a lot and cursor stay the same amount of line ?
marrowyungSenior Technical architecture (Data)Author Commented:
by the link you show me:


As I mentioned in a previous article, the cursor usually gets a bad rap for two reasons: (1) folks think a cursor is bad simply because it says DECLARE CURSOR, and (2) people use a cursor (or any row-by-row processing methodology) when it isn't necessary. In cases where it is the best option (e.g. running totals) or it is necessary (e.g. maintenance tasks, or cases where a stored procedure call must be made for every row or conditionally depend on processing that occurred for the previous row), people just say DECLARE c CURSOR FOR ... when they should usually be using the most efficient cursor declaration possible (LOCAL STATIC FORWARD_ONLY READ_ONLY). And yes, some will argue that FORWARD_ONLY READ_ONLY could be replaced by FAST_FORWARD, but I prefer the former, because the latter can't be combined with STATIC:"

"The XML and simple concatenation approaches aside, the cursor clearly outperformed all of the while loops. This may not necessarily be true in simpler scenarios, so I'm not suggesting that a cursor will always be as good as or better than a while loop. But by the same token, I think I've demonstrated that a while loop isn't always faster or easier to write and understand than a cursor."

so cursor is better?
Vitor MontalvãoMSSQL Senior EngineerCommented:
In general the maximum that a LOOP can be is have the same performance of a CURSOR.
Can't see why a LOOP will be better. If so, CURSOR won't exist.
marrowyungSenior Technical architecture (Data)Author Commented:
"Can't see why a LOOP will be better. If so, CURSOR won't exist."

cursor is simpler to let cut and paste but while loop can be highly customized ?

" maximum that a LOOP can be is have the same performance of a CURSOR."

yeah, should be the same
marrowyungSenior Technical architecture (Data)Author Commented:
"Can't see why a LOOP will be better. If so, CURSOR won't exist. "

I think just because loop is an older stuff and cursor just perform better, or loop just for the ease of use by web developer so they create a better loop, cursor for DB developer ?

procedural call always use WHILE loop  ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Loops are always benefic in any language but the way you show it here and comparing to a cursor it will be less performant for sure. I will avoid to use queries inside a loop since you can obtain mostly the same from a cursor and with better performance.
marrowyungSenior Technical architecture (Data)Author Commented:
" I will avoid to use queries inside a loop since you can obtain mostly the same from a cursor and with better performance. "

AHAHAH, you always say cursor perform better than loop, I think I will search more on this .
Vitor MontalvãoMSSQL Senior EngineerCommented:
you always say cursor perform better than loop
In those cases that you have queries inside a loop, yes. Otherwise you should verify what's better.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.