February 17, 2011 at 12:34 pm
Oh, okay... this is almost starting to sound like an episode of Intervention... "You gotta kick the cursor habit, man!! That stuff will kill you!"
I'm a recovering Access guy. I only ever use recordsets [cursors] if I have to. There are a few instances where it's very hard to get away from them... hence the question.
Thanks for the input!
Pieter
February 17, 2011 at 1:56 pm
pietlinden (2/17/2011)
Oh, okay... this is almost starting to sound like an episode of Intervention... "You gotta kick the cursor habit, man!! That stuff will kill you!"I'm a recovering Access guy. I only ever use recordsets [cursors] if I have to. There are a few instances where it's very hard to get away from them... hence the question.
Thanks for the input!
Pieter
They have their uses and their abuses, just like anything else.
If you run into particular situations where you think you can't escape from them, ask on these forums, and we'll see what we can do to help, or to confirm that they fit the bill there.
- 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
February 17, 2011 at 2:04 pm
But then what's the point, because I can just as easily declare a table variable, populate it with an insert query, and loop through it with a while loop. And this will likely be 10 times faster than the cursor.
Cursor performance can be poor and there are certainly cases where they are a bad decision..but this not a good example. I'd be hard pressed to believe that this method would perform as fast as a simple cursor, let alone 10x as fast.
February 17, 2011 at 6:34 pm
Derrick,
Yes, you are correct. I did some testing comparing a read_only static forward_only cursor to the loop method. At first the cursor was executing in 1/4 the time as the loop, but with some tweeking I was able to get the loop method to run nearly as fast as the cursor, processing 93000 rows. When I bumped it up to 1M rows, the loop method outperformed the cursor by 10.0 seconds vs 10.8 seconds. So I bumped it up to 5M rows, and the loop executed in 51.1 seconds vs the cursor in 54.9 seconds. The loop method is definately not 10 times faster. But they're on par, although I might expect the loop method to degrade at some point using the table variable.
I abandoned cursors some time ago because my testing showed a loop was considerably faster, but there may have been other factors at play. I will add cursors back into my toolbox.
However, I still believe that any query you can do with a cursor, you can also do with a loop.
Here's the two methods I was comparing:
-- cursor
declare curC cursor read_only static forward_only for
select top 5000000 recid from mytable
declare @recid as bigint
open curC
fetch next from curC into
@recid
while (@@FETCH_STATUS <> -1)
begin
--print @recid
fetch next from curC into
@recid
end
close curC
deallocate curC
-- loop
DECLARE @tblIDs TABLE
(
rowid bigint primary key,
recid bigint
)
declare @rowid as bigint
declare @recid as bigint
insert into @tblIDs
(rowid, recid)
select top 5000000 ROW_NUMBER () OVER (order by recid), recid from mytable
select @recid = null
select @rowid = 1
select @recid = recid
from @tblIDs
where rowid = @rowid
while @recid is not null
begin
--print @rowid
select @recid = null
select @rowid = @rowid + 1
select @recid = recid
from @tblIDs
where rowid = @rowid
end
February 17, 2011 at 8:19 pm
tfifield (2/17/2011)
Quirky updates ...[snip]... are just too messy in some cases.
Considering that a Quirky update and a cursor written to do the necessary previous row calculations are nearly identical, I have to ask why you think so?
--Jeff Moden
February 17, 2011 at 8:25 pm
GSquared (2/16/2011)[hr"Quirky update" is definitely a cursor. It's just not a declared one. It's all about counting on the engine to process row-by-row in some hoped-for sequence. Same as any explicit vs implicit coding.
Absolutely CORRECT! I used to call them "set based loops" but R.Barry Young coined a much better phrase for it that I've adopted... "Pseudo Cursors". 🙂
--Jeff Moden
February 17, 2011 at 8:31 pm
Gromey (2/17/2011)
However, I still believe that any query you can do with a cursor, you can also do with a loop.
With only very rare exceptions, I believe that any query you can do with a cursor or loop can be done in a high performance set based fashion. 😉
--Jeff Moden
February 18, 2011 at 6:54 am
Jeff Moden (2/17/2011)
Gromey (2/17/2011)
However, I still believe that any query you can do with a cursor, you can also do with a loop.With only very rare exceptions, I believe that any query you can do with a cursor or loop can be done in a high performance set based fashion. 😉
Yep.
- 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
February 18, 2011 at 8:01 am
Oh yea, absolutely. For the most part, I only use loops when I'm doing mass updates or for copying/moving large sets of data from one database table to another for integration. And then it's only so I can throttle back the transaction size to about 10,000 rows. Otherwise I'd constantly have 150k to 2M row update or insert transactions and those raise havoc with temp and log files.
February 18, 2011 at 9:04 am
Gromey (2/18/2011)
Oh yea, absolutely. For the most part, I only use loops when I'm doing mass updates or for copying/moving large sets of data from one database table to another for integration. And then it's only so I can throttle back the transaction size to about 10,000 rows. Otherwise I'd constantly have 150k to 2M row update or insert transactions and those raise havoc with temp and log files.
Yes, that's one of the "legit" uses of loops. Definitely.
- 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
February 21, 2011 at 11:47 am
Jeff Moden (2/17/2011)
tfifield (2/17/2011)
Quirky updates ...[snip]... are just too messy in some cases.Considering that a Quirky update and a cursor written to do the necessary previous row calculations are nearly identical, I have to ask why you think so?
Jeff,
Consider the situation where you have to do something else when a threshold is reached along the way. By something else I mean entirely new logic. I admit I gave up after several passes at the mix/match code logic and reverted to a cursor, which worked fine the the client considering it was a sales ticket being entered at a cash register, which doesn't require blinding speed. I do regularly use your quirky update method for reports that accumulate on several levels.
Todd Fifield
February 21, 2011 at 12:36 pm
tfifield (2/21/2011)
Jeff Moden (2/17/2011)
tfifield (2/17/2011)
Quirky updates ...[snip]... are just too messy in some cases.Considering that a Quirky update and a cursor written to do the necessary previous row calculations are nearly identical, I have to ask why you think so?
Jeff,
Consider the situation where you have to do something else when a threshold is reached along the way. By something else I mean entirely new logic. I admit I gave up after several passes at the mix/match code logic and reverted to a cursor, which worked fine the the client considering it was a sales ticket being entered at a cash register, which doesn't require blinding speed. I do regularly use your quirky update method for reports that accumulate on several levels.
Todd Fifield
I use it all the time for such "threshold" situations (mostly total file size limitations, in this case) and there's nothing messy about it. It's all in how you handle what happens when you reach such a threshold. If you try to handle what needs to be done within the threshold code, then, yeah, it's going to get messy. I don't do it that way... I peel one potato at a time first determining what meets a threshold and only then processing based on that threshold.
Your choice, though. I just wanted people to know that it's not necessarily the Quirky update that makes things "messy". 😉
--Jeff Moden
February 21, 2011 at 1:18 pm
Jeff Moden (2/21/2011)
I use it all the time for such "threshold" situations (mostly total file size limitations, in this case) and there's nothing messy about it. It's all in how you handle what happens when you reach such a threshold. If you try to handle what needs to be done within the threshold code, then, yeah, it's going to get messy. I don't do it that way... I peel one potato at a time first determining what meets a threshold and only then processing based on that threshold.Your choice, though. I just wanted people to know that it's not necessarily the Quirky update that makes things "messy". 😉
I did try the 'peel it off' method into temp tables. Possibly it was too late and I was too tired to make it work properly and still be somewhat understandable code. That was a few years ago and I'm a better programmer these days. Maybe I'll give it another try if the client is willing to pay for it. I'll let you know.
Todd fifield
February 22, 2011 at 2:35 am
Hi all,
I will explain a case when I coded a cursor as a good solution to a problem.
Some months ago I coded a Stored Procedure to purge a table based on time; 60 days old rows are purged. Nothing special but there is a condition; any row candidate to purge need a paired row, you can consider the first row is like an "open" and the last row like a "close" (actually it is), and both rows must be old enough to purge together.
I coded all conditions in one SQL instruction, but it ran very slowly, the execution plan didn't find adequate indexes. Maybe a table design problem, but my task was to code a Stored Procedure, not to change a table. So, facing a poor performance, I coded a process trough a cursor, and it worked well; not fantastic but well.
One important thing about performance; my firsts tests were slow until I added a COMMIT based on a row counter, in this case I commited changes every 1000 rows, it made a very big difference.
Regards,
Francesc
February 22, 2011 at 11:21 am
frfernan (2/22/2011)
Hi all,I will explain a case when I coded a cursor as a good solution to a problem.
Some months ago I coded a Stored Procedure to purge a table based on time; 60 days old rows are purged. Nothing special but there is a condition; any row candidate to purge need a paired row, you can consider the first row is like an "open" and the last row like a "close" (actually it is), and both rows must be old enough to purge together.
I coded all conditions in one SQL instruction, but it ran very slowly, the execution plan didn't find adequate indexes. Maybe a table design problem, but my task was to code a Stored Procedure, not to change a table. So, facing a poor performance, I coded a process trough a cursor, and it worked well; not fantastic but well.
One important thing about performance; my firsts tests were slow until I added a COMMIT based on a row counter, in this case I commited changes every 1000 rows, it made a very big difference.
Regards,
Francesc
Agreed on this one also - running a delete on a heavily indexed table with many rows will cause a table lock unless you break it up into batches with either a loop or cursor. I use this to delete about 10 million rows weekly from a 100+ million row table with a cursor (a while loop might be slightly faster but most likely negligible).
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply