August 5, 2008 at 5:46 am
i am in the situation whete i must use curosr
is there any lack of performance if i use while loop insted of using cursor
select @id=min(id) from table
while@id is not null
begin
--do some thing
select @id = min(@id) from table where id >@id
end
please help me
thank you
August 5, 2008 at 9:40 am
A local and either fast_forward or static cursor will out-perform that while loop. There are ways to build while loops that can be pretty fast, but that's not one of them.
If you're doing row-by-row, and actually have to, a cursor is the best way to go, even if only to maintain standards.
If you want, we can take a look at what you're doing with the cursor, and maybe help you change it to a set-based operation.
- 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
August 5, 2008 at 9:46 am
HI GSquared,
Don't get me wrong, I'm not a 100% but I have always been taugh both at work and here that cursors are a no-no where ever possible.
Perhaps because of this I always assumed that a loop was better as a result.
I mean abviously set-based code is the best 🙂
I thought that cursors caused problems with memory and locking etc...
Please could you clear this up for me?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 11:24 am
The universal problem with cursors in modern versions of SQL Server is that they are row-by-row (or row-by-agonizing-row, RBAR).
Depending on how you create them, they can also have problems with locks (and blocks).
The thing is, the While loop proposed here would create the same sort of locks, and would be slower because of having to find the next row in each iteration.
- 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
August 6, 2008 at 2:14 am
Thanks for the reply 🙂
I guess it comes down to test test and test again 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 6, 2008 at 8:13 am
Yep. And always remember the first rule of database coding and design, "It depends".
- 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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply