December 1, 2009 at 11:22 am
You cant exclude them if for instance it is based on time and the threshold that will make the particular constraint true will only hold true while the actual loop is looping. For example when u loaded the cursor the 5 foot rule would not hold true. Perhaps it wasnt until the nth iteration that the 5 ft rule because a factor. That's what i meant by dynamic.
Your right though the fast forward is really fast. It depends on the amount of iterative loops though. For example if the difference was to either loop 7 time as opposed to 7 million?
Regardless of the example which was bad i admit the point still hold true. A cursor does not allow u to manipulate the set the loop is running off of at runtime. Period.
I've been burnt by cursors w transaction embedded in them in the past. So i am totally biased i admit. i loath them.
December 1, 2009 at 12:11 pm
Ok, I see what you mean. that is definitely something that you cannot do with a cursor. Not that I have needed to change the cursor contents in 11 years of DB application development, but you never know, it could happen...
I will admit to being biased against the table and while loop construction, because I have seen so many places where the cargo cult coders have taken hold of the "cursor are teh evil" nugget and replaced perfectly acceptable fast_forward cursors with eldritch looping constructs, which are both slower and uglier
December 1, 2009 at 12:37 pm
jdurandt (12/1/2009)
Ok, I see what you mean. that is definitely something that you cannot do with a cursor. Not that I have needed to change the cursor contents in 11 years of DB application development, but you never know, it could happen...I will admit to being biased against the table and while loop construction, because I have seen so many places where the cargo cult coders have taken hold of the "cursor are teh evil" nugget and replaced perfectly acceptable fast_forward cursors with eldritch looping constructs, which are both slower and uglier
I believe that cursors have there place, but I also believe that in 99.9% of the cases where they are used that there exists a set-based solution that would be faster and more scalable.
December 1, 2009 at 12:37 pm
you would hate me then. one of the first things i do is redesign all proc away from cursors. Perhaps i am you arch nemesis and u mine.
The question is who is the good guy and who the bad. I'm bald so i think i should default to the good guy. My hair has already been taken from me.
December 1, 2009 at 12:49 pm
john scott miller (12/1/2009)
you would hate me then. one of the first things i do is redesign all proc away from cursors. Perhaps i am you arch nemesis and u mine.The question is who is the good guy and who the bad. I'm bald so i think i should default to the good guy. My hair has already been taken from me.
If you were writing a proc that was supposed to run a log backup command on each active database in Full recovery model, which is a situation where you have to step through one at a time one way or another, what would you use in place of a fast_forward cursor?
(By the way, I'm losing my hair but not quite bald yet. I guess that makes me ambiguously good/evil, probably depending on how recently I've buzzed.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 1:18 pm
Oh the humanity.... I had a good chuckle at the idea of Cursor Boy and his arch nemesis Loop Man
I have a full head of hair, but it is graying :ermm:
@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
December 1, 2009 at 1:22 pm
jdurandt (12/1/2009)
Oh the humanity.... I had a good chuckle at the idea of Cursor Boy and his arch nemesis Loop ManI have a full head of hair, but it is graying :ermm:
@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
Lynn and I are agreeing with you. john scott miller is the one making odd statements about hair, good guys, and disagreeing with Lynn that cursors have any use at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 1:36 pm
jdurandt (12/1/2009)
Oh the humanity....@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
Okay, jdurandt, totally off subject and you aren't the first to do it, but where do people keep getting the 'e' at the end of my first name? 😛
December 1, 2009 at 1:43 pm
Lynn Pettis (12/1/2009)
jdurandt (12/1/2009)
Oh the humanity....@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
Okay, jdurandt, totally off subject and you aren't the first to do it, but where do people keep getting the 'e' at the end of my first name? 😛
Same place they turn mine into "Guss".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 2:23 pm
GSquared (12/1/2009)
Lynn Pettis (12/1/2009)
jdurandt (12/1/2009)
Oh the humanity....@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
Okay, jdurandt, totally off subject and you aren't the first to do it, but where do people keep getting the 'e' at the end of my first name? 😛
Same place they turn mine into "Guss".
Okay, I can actually see the extra "s" if someone has a twitchy finger or flaky keyboard, but the "n" and "e" actually use different fingers on different hands to type. 😛
December 1, 2009 at 2:51 pm
-----------------------------------------------------------------------------------------------
If you were writing a proc that was supposed to run a log backup command on each active database in Full recovery model, which is a situation where you have to step through one at a time one way or another, what would you use in place of a fast_forward cursor?
-----------------------------------------------------------------------------------------------
The most basic example of the non cursor method that iterate through one rec at a time is as follows.
declare @custId int
--load looping mechanism
select distinct custid
, 0 processed
into #loop
from customers
while exists ( select * from #loop where processed = 0 )
begin
select top 1 @custId = custId from #loop where processed = 0
--do whatever...update the cust records....update some other table whatever
--As i was saying before the difference here is if it is warented i can adjust as many records in the #loop table to processed if i know for what ever reason that at this instance in time they dont need to be processed. By doing so the loops iterations can be greatly reduced. That's really the only point i was making which is you cannot manipulate the original set the cursor is running on so that perhaps it will only do a third of the loop iteration. instead u have to do a fast forward after checking if some value is equal to something over and over and over again.
update #loop
set processed = 1
where custId = @custid
end
drop table #loop
Also the perfered term is Loop Man. If i was loop Boy i'd still have hair
December 1, 2009 at 3:02 pm
Yes, you can manipulate the data in a temp table in ways you can't in a cursor.
However, in 9 years of SQL, I've never yet run into a situation where I'd build such a loop. The very rare loops that I do use are handled quite nicely by a cursor.
Do you have a concrete example where that would be advantageous? And where you wouldn't just use set-based-logic instead of any sort of loop (cursor or otherwise)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 3:02 pm
----------------------------------------------------------------------------
--Lynn and I are agreeing with you. john scott miller is the one making odd statements about hair, good guys, and disagreeing with Lynn that cursors have any use at all.
------------------------------------------------------------------------------
For the record i didnt say they have no use. I just personally dont use them anymore. There will never be a situation where u have to use a cursor. You can always design around them w/ what i call the faux cursor which is the looping mechanism. Cursors burt me a very long time ago. I was showed another way and have been using it of a variation of it ever since thats all.
December 1, 2009 at 3:08 pm
BaldingLoopMan (12/1/2009)
------------------------------------------------------------------------------Lynn and I are agreeing with you. john scott miller is the one making odd statements about hair, good guys, and disagreeing with Lynn that cursors have any use at all.
------------------------------------------------------------------------------
For the record i didnt say they have no use. I just personally dont use them anymore. There will never be a situation where u have to use a cursor. You can always design around them w/ what i call the faux cursor which is the looping mechanism. Cursors burt me a very long time ago. I was showed another way and have been using it of a variation of it ever since thats all.
I've done extensive performance-testing on a variety of looping mechanisms. In the very rare circumstances that one is needed, a fast_forward cursor generally outperforms other options by a measurable margin.
Problems you can run into with While loops are just as numerous as ones you can run into with cursors. They're not better, they're just different, and in some ways worse. They do have uses, but not for stepping through records.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 1, 2009 at 3:18 pm
GSquared (12/1/2009)
Lynn Pettis (12/1/2009)
jdurandt (12/1/2009)
Oh the humanity....@Lynne - I agree 100% : Set based processing is the answer in 99% of cases. What I was saying though is that, in the event that I have to loop things, I would use a cursor, as that is the built-in SQL Server tool for processing a bunch of records one at a time
Okay, jdurandt, totally off subject and you aren't the first to do it, but where do people keep getting the 'e' at the end of my first name? 😛
Same place they turn mine into "Guss".
Heh, yeah, it's the "G" that's squared, not the "s"! 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply