Replacement for Cursors

Question

Replacement for Cursors

Viewing 15 posts - 31 through 45 (of 59 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

vinu512 (4/25/2012)
OK...my bad...lets forget about printing the names.
I just want the names to be stored in 5 temp variables so that I can use them further.
Can that be done without cursor or loop?

I'd have to say that you're still not thinking "set based". If you were doing this in procedural code, you'd think "array". In T-SQL, why can't the names be stored in a single table with an enumeration column such as an IDENTITY instead of 5 separate variables?

--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)

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

Chandrachurh Ghosh (4/26/2012)
There are always ways to avoid CURSOR.....but you might end up in a costlier plan.
If you have decided to use tally table.....try it out....
Unless someone uses a feature or command he cannot comment on it.....and without knowing the prospects of using a tally table commenting will be ignorance....:-D

Since you brought it up, show us the way to populate and use 5 variables as requested using a Tally Table. And remember who you're talking to before you make any claims of ignorance. 😉

--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)

vinu512 SSCoach Points: 15729 More actions · Answer 3

Jeff Moden (4/26/2012)
vinu512 (4/25/2012)
OK...my bad...lets forget about printing the names.
I just want the names to be stored in 5 temp variables so that I can use them further.
Can that be done without cursor or loop?
I'd have to say that you're still not thinking "set based". If you were doing this in procedural code, you'd think "array". In T-SQL, why can't the names be stored in a single table with an enumeration column such as an IDENTITY instead of 5 separate variables?

This is about an example I gave a few pages back where the names are actually stored in a table with an enumeration column.

But the requirement is that I do not need the Names as a "Set". I need them separately(one by one), so that I can use them further.

I know that SQL Server is supposed to be used in a SET based manner...but you never know what requirements com up in front of you.

I have got a lot of examples and opinions. Thanks to everyone.

I'll go along with everyone else here and just reiterate that there are places where a loop of one sort or another is necessary, and in those cases a cursor is often the best bet. Outside of those cases, don't use a loop at all, no matter how disguised it may be.
For example, I have a dataset of 5.8-million rows that I need to check each row if name, email, phone, and address data in it can be matched to any prior row. Could do the whole thing as a triangular join, but even a 1000-row subset of the data overloads the server if I do it that way. Partially because the join math is insanely complex. (It has to identify that "Joe" and "Joseph" are the same name, for example. And that "123 N 1st Ave" = "123 N. 1st Ave." = "123 North First Avenue". And that "bob.smith@gmail.com" = "bobsmith@gmail.com", but "bob.smith@hotmail.com" != "bobsmith@hotmail.com", because of special rules for GMail names.) Partially because of complex rules on how to handle sequences of data.
So, I have a cursor spending a month going through the data one row at a time, and even have to sub-manage that so that the cursor dataset is only a few thousand rows at a time and resets itself in a "meta-loop". Processes about 2 to 3 records per second, and getting it up to that speed took a lot of tuning.
That's a case where looping and cursors are necessary. Not because of what's being done with the data (it could be done through a single query), but because modern server hardware can't finish the job in finite time except through breaking the job up this way.
So, there are places where you should use them, and places where you must use them because of current limits on the hardware or the SQL Server engine, and then there's the vast majority of work, where you should avoid them and their disguised versions like the plague.

Thanks a lot GSquared for the example. Its really helpful.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

vinu512 (4/26/2012)
But the requirement is that I do not need the Names as a "Set". I need them separately(one by one), so that I can use them further.

By definition, that's not set based. It also sounds like an artificial requirement. It's similiar to saying "Here, paint the wall blue using this red paint." 😉

--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)

vinu512 SSCoach Points: 15729 More actions · Answer 5

Jeff Moden (4/27/2012)
vinu512 (4/26/2012)
But the requirement is that I do not need the Names as a "Set". I need them separately(one by one), so that I can use them further.
By definition, that's not set based. It also sounds like an artificial requirement. It's similiar to saying "Here, paint the wall blue using this red paint." 😉

Mr. Jeff Moden, I know you might know better than me on any given day. My requirement may seem artificial.

When I started this post I wanted to know if Cursors are completely avoidable.

I don't think anyone can do more justice to explaining it to me than the author of the best article on Tally Table[/url] I have read.

Please tell me Sir if Cursors can be completely avoided whatsoever the requirement may be??....or do you think it depends on the requirement??

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

I would say there are certainly cases where loops are unavoidable.

I just ran into a case on this thread, where a CURSOR may be needed to solve some of the possible scenarios: http://www.sqlservercentral.com/Forums/Topic1290424-392-1.aspx (this is a case of a non-deterministic solution set).

So I would say, yes there are probably cases out there where CURSORs are unavoidable and I believe Jeff has shown that there are cases where a CURSOR may perform better.

All I've ever been trying to convey is that I think they're avoidable like 99.99% of the time.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

vinu512 (4/27/2012)
Jeff Moden (4/27/2012)
vinu512 (4/26/2012)
But the requirement is that I do not need the Names as a "Set". I need them separately(one by one), so that I can use them further.
By definition, that's not set based. It also sounds like an artificial requirement. It's similiar to saying "Here, paint the wall blue using this red paint." 😉
Mr. Jeff Moden, I know you might know better than me on any given day. My requirement may seem artificial.
When I started this post I wanted to know if Cursors are completely avoidable.
I don't think anyone can do more justice to explaining it to me than the author of the best article on Tally Table[/url] I have read.
Please tell me Sir if Cursors can be completely avoided whatsoever the requirement may be??....or do you think it depends on the requirement??

I don't think that Jeff will try to argue that Cursors are completely avoidable in disrespect of any requirements. There are cases where you need to use them and that will be completely fine.

What everyone tries to tell you is that in most cases of getting data in/out database or processing/transforming data, you don't need to use loop or cursor.

Actually, based on the given details in this thread, it's impossible to justify use of cursor.

May be there are some folk here who will be able to establish an invisible connection to your mind and get the whole picture out of it, but probability of this happening is quite low :hehe:

If you could specify requirements in more details, then you will get much more relevant judgement and may be much better solution.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

I would like to postulate a theory on this topic and I'd be interested to have someone prove me wrong.

Dwain's Theory:

Given sufficiency of data and time, any deterministic results set can be generated by a finite number of set-based operations that is not bounded by the rows of one of the input sets.

Corollary: Insufficient data will result in a non-deterministic results set.

Constraint: Since CURSORs can nearly always be used to generate either deterministic or non-deterministic result sets, cases where choosing to use a CURSOR because of performance considerations are excluded from Dwain's Theory.

CURSORs of course also represent a finite number of set-based operations but they are only bounded by the rows of one of the input sets.

A deterministic results set is defined as a single possible outcome based on a given set of inputs.

Dwain's Theory does not state that a non-deterministic results set cannot be generated with a set-based operation. What it is saying is that there are potentially many set operations that could generate some or all of the non-deterministic results sets given the inputs, but possibly some of the non-deterministic results sets must be generated by using a CURSOR.

I shall provide a proof for my theory shortly after I catch a bigger fish.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

vinu512 (4/27/2012)
Jeff Moden (4/27/2012)
vinu512 (4/26/2012)
But the requirement is that I do not need the Names as a "Set". I need them separately(one by one), so that I can use them further.
By definition, that's not set based. It also sounds like an artificial requirement. It's similiar to saying "Here, paint the wall blue using this red paint." 😉
Mr. Jeff Moden, I know you might know better than me on any given day. My requirement may seem artificial.
When I started this post I wanted to know if Cursors are completely avoidable.
I don't think anyone can do more justice to explaining it to me than the author of the best article on Tally Table[/url] I have read.
Please tell me Sir if Cursors can be completely avoided whatsoever the requirement may be??....or do you think it depends on the requirement??

Heh... such absolutes and such total lack of absolutes all in the same thread. And, no... I'm not talking about your stuff. I'm talking about my stuff below because the correct answer is and always will be... "It Depends". 😛

Yes, you can 100% completely avoid the use of "CURSORs" (as in DECLARE CURSOR) but you cannot avoid the use of "cursors" (things that loop) when nothing but a loop will do. "Command and control" loops are valuable assets in the tool box of every DBA ("batched" deletes of millions of rows are a good example). There are even some data manipulation "scripts" that use explicit loops that will beat even the mighty Tally Table, so I'll go out on a limb and say that completely avoiding the use of properly written CURSORs/cursors is a rather moot point.

For example, the original problem you posted can be easily solved without a "loop" in at least 3 different ways and without a Tally Table, as well. BUT, since the loop probably wouldn't be the slow thing there, the real question might be "should you bother to solve it without a loop?". You could end up with RBAR code either way even though you supposedly "avoided" the loop. The reason why I'd do such a thing without a "cursor" is simply because the alternative code is physically shorter to write. Then again, you have to be careful. If the alternative code is in the form of concatenation to a variable that you'll execute as a "whole", you may have just limited the ultimate in scalability.

Still, it's a very rare thing for loops and other forms of "cursors" to beat the "Pseudo Cursors" (the machine language loops behind the scenes) that power properly written set-based code.

So, I'd have to say that it really does depend on the requirement not so much because you can avoid all "CURSORs" and most "cursors", but because you really need to select the right tool to get the job done and that might mean that you really do need to use a well written "CURSOR" and a "cursor"... or not. 😉

For the record, a well written "Firehose" CURSOR is just as and sometimes more effective than a Temp Table/While Loop combination and is actually a bit easier to use. People that make the change from a Cursor to a Temp Table/While loop have wasted a fair bit of time doing so. Just change the CURSOR type and call it a day.

Also for the record, I think that way too many people use "cursors" (loops of any kind) "depending on the requirements" because they simply don't know that most of those requirements can be solved without the use of "cursors".

And thank you for the thoughtful comment about the Tally Table article. I'm truly humbled. :blush:

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Eugene Elutin (4/27/2012)
May be there are some folk here who will be able to establish an invisible connection to your mind and get the whole picture out of it, but probability of this happening is quite low

Heh... I actually understood where he was going with that. Consider sending an email with a different subject and body to each person in a table. Yes, the commands to do such a thing can be created in a set based fashion, but should you really because, either way, the commands will be executed in a RBAR fashion.

--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)

vinu512 SSCoach Points: 15729 More actions · Answer 11

Thank you Mr. Moden.

I've just started as a DBA(a while back) and have no idea where I will end up with it. But I am definitely loving what I am doing.

And the best part is that I haven't been so excited about learning new stuff before in my life.

Thanks to all the Pros for their time and support.:-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

vinu512 (4/27/2012)
Thank you Mr. Moden.
I've just started as a DBA(a while back) and have no idea where I will end up with it. But I am definitely loving what I am doing.
And the best part is that I haven't been so excited about learning new stuff before in my life.
Thanks to all the Pros for their time and support.:-)

That's outstanding. I'm actually excited for you. I love being both a DBA and a Database Developer.

--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)

vinu512 SSCoach Points: 15729 More actions · Answer 13

Jeff Moden (4/28/2012)
vinu512 (4/27/2012)
Thank you Mr. Moden.
I've just started as a DBA(a while back) and have no idea where I will end up with it. But I am definitely loving what I am doing.
And the best part is that I haven't been so excited about learning new stuff before in my life.
Thanks to all the Pros for their time and support.:-)
That's outstanding. I'm actually excited for you. I love being both a DBA and a Database Developer.

I love it too. Lets hope that maybe someday I'll be the one writing the articles and you'll be the one reading them.....ROFLMAO!!!!:w00t::hehe::w00t::hehe::w00t:

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Dwain Camps SSC Guru Points: 86948 More actions · Answer 14

Writing articles ain't that difficult. Look at me. Just over 400 points and I have one published and another accepted/awaiting publication.

The trick is to find something that you've never seen anyone do before. And enjoy solving it. If it's actually useful, there's a good chance Steve will accept it.

If it isn't accepted, just blog on it.

I think Jeff even read my article. 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vinu512 SSCoach Points: 15729 More actions · Answer 15

dwain.c (4/30/2012)
Writing articles ain't that difficult. Look at me. Just over 400 points and I have one published and another accepted/awaiting publication.
The trick is to find something that you've never seen anyone do before. And enjoy solving it. If it's actually useful, there's a good chance Steve will accept it.
If it isn't accepted, just blog on it.
I think Jeff even read my article. 🙂

I guess I'm still new to this and still Learning. Not as experienced as you all. But I sure am enthusiastic to pounce on anything new that comes my way. Would definitely keep your suggestion in mind Dwain.:-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉