Removing cursor from TSQL - 1

Question

Removing cursor from TSQL - 1

Viewing 4 posts - 16 through 18 (of 18 total)

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

Dugi SSCoach Points: 17998 More actions · Answer 1

Jeff Moden (3/3/2009)
[font="Arial Black"]Dugi... [/font]if I may suggest, it's better to give someone no answer than to give them what might be one of the worst answers for replacing cursors. Temp Tables and While loops are not the answer to replacing cursors. the reason why people want/need to replace cursors is mostly because of performance problems... Temp Table/While loop combinations do not solve that problem...

Jeff,

When you reply after my post, suddenly I remember that you have the Tally Table explanation also with performance, and I red again just to remember all things that you can do with Tally Table! I just reply again with suggestion about your Tally Table. And finally as I can see your explain that the While Loop doesn't replace cursor when we discuss about performance!!!

I think that now everything is ok!

Have nice day My Friend!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

But.... just to be clear, Dugi... this one doesn't have anything to with a Tally table... just good programming.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

joao.rolo.sa Valued Member Points: 59 More actions · Answer 3

Basically using this technique,

of Using an extra column with ID from our Data from 1 to NROWS and processing the row with ID equal to

current row number, at each while iteraction... could work for almost any kind of sp that uses N-Cursors , its a matter of changing the logic using FETCHS to a While's based LOGIC.

Thanks a lot :).

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

joao.rolo.sa (3/4/2009)
Basically using this technique,
of Using an extra column with ID from our Data from 1 to NROWS and processing the row with ID equal to
current row number, at each while iteraction... could work for almost any kind of sp that uses N-Cursors , its a matter of changing the logic using FETCHS to a While's based LOGIC.
Thanks a lot :).
Cheers

Joao, if you look at the avatar of the post above yours, you will see the acronym RBAR, which is a 'Modenism' for "row by agonizing row".

Changing one RBAR mechanism for another RBAR mechanism is a waste of developer time, expensive time which could instead be spent converting the code to a fast and efficient set-based method.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden