January 26, 2009 at 9:49 am
GSquared (1/26/2009)
Jeff, something I have to ask:You keep mentioning that recursive CTEs are worse than cursors. I can see that in some cases, but in cases like a hierarchy crawl, I've seen cursor solutions to that, and they were much, much worse than CTEs.
Do you have something specific in mind when you say they're worse, or do you have some data that shows they're better on actually recursive solutions?
The places where I've seen recursive CTEs perform poorly have been places where recursion wasn't actually needed at all.
Like anything else, it depends... Do a simple test... Recursive CTE to count from 1 to 100,000 and Cursor to do the same count. That should let you know which is worse as a "base".
For the rest of the stuff, you say " I've seen cursor solutions to that..." To play back what you just asked, "Do you have something specific in mind when you say they're worse, or do you have some data that shows they're better on actually recursive solutions?" 😉
--Jeff Moden
January 26, 2009 at 9:54 am
spelling and grammar mistakes I can tolerate. As Gail mentioned, we're not all native English speakers.
I do get annoyed with the texting shorthand some people employ, but I might not get anything done about that.
There is advice that can damage the db, but not much. Lots of times I see people claiming this is "right" and that is wrong, and I don't agree. There are better and worse solutions FOR reasons and in situations, not as all-encompassing edicts.
Cursors are not bad. They can cause poor performance and don't make sense in many places for various reasons. There are better solutions, but there is no edict that cursors are just bad.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 26, 2009 at 11:20 am
GilaMonster (1/26/2009)
P Jones (1/26/2009)
I also get hot under the collar about bad spelling and grammar - I had a traditional English grammar school educationJust bear in mind that not everyone posting here has English as a first language, or even maybe as a second language. How good's your Spanish spelling and grammar? (as an example)
hehe, English is my third language (Croatian, German and than English) :alien:
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
January 26, 2009 at 12:18 pm
Jeff Moden (1/26/2009)
GSquared (1/26/2009)
Jeff, something I have to ask:You keep mentioning that recursive CTEs are worse than cursors. I can see that in some cases, but in cases like a hierarchy crawl, I've seen cursor solutions to that, and they were much, much worse than CTEs.
Do you have something specific in mind when you say they're worse, or do you have some data that shows they're better on actually recursive solutions?
The places where I've seen recursive CTEs perform poorly have been places where recursion wasn't actually needed at all.
Like anything else, it depends... Do a simple test... Recursive CTE to count from 1 to 100,000 and Cursor to do the same count. That should let you know which is worse as a "base".
For the rest of the stuff, you say " I've seen cursor solutions to that..." To play back what you just asked, "Do you have something specific in mind when you say they're worse, or do you have some data that shows they're better on actually recursive solutions?" 😉
I must be missing something here. I was able to build and test this:
set nocount on;
;with Counting (Number) as
(select 1
union all
select number + 1
from counting
where number <= 99999)
select *
from counting
option (maxrecursion 0);
Took 8 seconds to run on my machine. It's almost the most inefficient means I know of to build a list of numbers, but I think it's what you're writing about.
Then I thought about how to get a cursor to do the same thing. I can't come up with a workable query for the cursor to start with, unless I already have 100k rows of something or other. Which, if you have that, you don't need a recursive CTE, you just query row_number.
Ignoring that piece of idiocy, here's what I came up with for a cursor:
set nocount on;
declare Counting cursor local fast_forward for
select top 100000 row_number() over (order by t1.object_id) as Number
from sys.all_objects t1
cross join sys.all_objects t2;
open Counting;
declare @Number int;
fetch next from Counting
into @Number;
while @@fetch_status = 0
begin
print @Number;
fetch next from Counting
into @Number;
end
close Counting;
deallocate Counting;
Took 22 seconds to run on the same machine.
What am I doing differently than what you tested?
Of course, the "right" solution to this is either have a Numbers table in the first place, or do something like this:
;with Counting (Number) as
(select top 100000 row_number() over (order by t1.object_id) as Number
from sys.all_objects t1
cross join sys.all_objects t2)
select Number
from Counting;
Which, on that same system, ran in such a small fraction of a second that it's not worth recording. MUCH faster than either of the other solutions above.
(continued below)
- 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
January 26, 2009 at 12:18 pm
And, if you're asking me about hierarchy resolution with a cursor as opposed to a recursive CTE (asking me to put my code where my mouth is, essentially), here you go:
set nocount on;
go
create table dbo.Hierarchy (
ID int identity primary key,
ParentID int null references dbo.Hierarchy(ID),
constraint CK_IDParentID check (ID != ParentID or ParentID is null));
go
insert into dbo.Hierarchy (ParentID)
select top 10000 null
from sys.all_objects t1
cross join sys.all_objects t2;
go
update dbo.Hierarchy
set ParentID = nullif(nullif(abs(checksum(newid()))%10000, id), 0)
where parentid is null;
go
create index IDX_HierarchyParents on dbo.Hierarchy (ParentID);
That creates a simple adjacency hierarchy. (Set based hierarchies require neither cursors nor recursive code of any sort to query, so it has to be adjacency for either one to make any sense at all.)
Here's the simplest solution I can come up with for having a cursor crawl that hierarchy. May not be the best solution, but it's what I can come up with.
alter function dbo.HierarchyCursor
(@ID_in int)
returns @h table (
ID int,
ParentID int)
as
begin
insert into @h (ID)
select @ID_in
declare HierarchyCur cursor local fast_forward for
select ID, ParentID
from dbo.Hierarchy
where ParentID = @ID_in;
open HierarchyCur;
declare @ID int, @ParentID int;
fetch next from HierarchyCur
into @ID, @ParentID;
while @@fetch_status = 0
begin
if exists
(select *
from @h
where ID = @ID)
begin
fetch next from HierarchyCur
into @ID, @ParentID;
continue
end
insert into @h(ID, ParentID)
select ID, ParentID
from dbo.HierarchyCursor(@ID)
fetch next from HierarchyCur
into @ID, @ParentID;
end
close HierarchyCur;
deallocate HierarchyCur;
return;
end;
Ran it for a few input values and it took an average of 8 milliseconds to complete for very simple hierarchies.
Same table, same inputs, ran this:
;with HierarchyCTE (ID, ParentID) as
(select ID, null
from dbo.Hierarchy
where ID = @ID_in
union all
select Hierarchy.ID, Hierarchy.ParentID
from dbo.Hierarchy
inner join HierarchyCTE
on Hierarchy.ParentID = HierarchyCTE.ID)
select *
from HierarchyCTE;
Ran with a max runtime of 1 millisecond, and some too fast even for "show statistics time" to record anything except 0.
Maybe you can build a better cursor for this, but I can't think of one.
- 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
January 26, 2009 at 12:33 pm
Man, sorry, Gus... :blush: I meant a "While Loop" for the count comparison, lost my mind, and said "Cursor" instead. A WHILE LOOP has about the same speed as a correctly constructed static cursor.
So far as the hierarchy thing goes, I'll have to take a look at your good examples before I try chewing on another tennis shoe in public... 😛
--Jeff Moden
January 26, 2009 at 12:38 pm
While loop counter:
declare @Counter int;
select @Counter = 1;
while @Counter <= 100000
begin
print @Counter;
set @Counter = @Counter + 1;
end;
Same machine. 16 seconds first run, 14 seconds second run, 13 third run. Still longer than the CTE, but I can't say I have any idea why. That code should be blinding fast, per everything I know of how procedural code works. Somehow, the CTE (at 8 seconds), is beating a simple While loop. I have to admit, when I wrote the test, I was planning on seeing how much faster it would be than the CTE. Did NOT expect it to lose the race!
- 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
January 26, 2009 at 10:14 pm
my absolute favorite posts to read are the ones where they say "urgent" in the title.
they are invariably from a poster who just joined, has less than 5 posts, and never heard of google.
I have real trouble , i need now where is the...
these kind of posts captivate me like a car accident or train wreck...I can't keep my eyes off of it.
A lot of them are homework related where they skipped class, didn't do the homework and are not about to research it themselves, but ones like this...where the guy got an upgrade job and now is in way over his head and his butt is on the line....man i wish there was a reality show with these guys in it...
I can just imagine this guys swearing he's awesome at DTS at his interview,then gets the job and realizes he screwed the pooch on this one.
Lowell
January 27, 2009 at 2:44 am
Just bear in mind that not everyone posting here has English as a first language, or even maybe as a second language. How good's your Spanish spelling and grammar? (as an example)
My spanish or anything other language may not be good but I CAN press a button for a dictionary/spell checker in that language. Why can't posters?
January 27, 2009 at 7:19 am
Grammar lessons, yes... maybe help them Lynn can.....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 27, 2009 at 9:18 am
Those fonts are the reasons most people shouldn't have too many choices.
Spell checking is easy, but underutilized. I love the Firefox automatic spell checking in edit boxes. Wish more people used that.
And as much as it's fun to see this thread growing, can you drop the performance debates into that forum or the T-SQL one so that others might see it and follow it as an on-topic debate?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2009 at 9:22 am
We just need another 60 posts to break 1000. Come on people.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 27, 2009 at 9:33 am
Bob Hovious (1/27/2009)
Grammar lessons, yes... maybe help them Lynn can.....
Hey, occasionally, yes in character I must be.
For the most part, when actually getting into a discussion with others, I try very hard to write coherently and correctly.
Also regarding Steve's comment about performace, I had to go back and read a few of the posts to figure out what he was talking about. Having done that, I have to agree that there are a few posts in this thread that should be moved to where others are more likely to read them.
January 27, 2009 at 9:39 am
Grant, that was a cheap post, course, I probably have a few as well.
Lynn has a good point. Writing coherently, even giving someone a hint on what would make more sense (please spell out you), can help calm someone down, show them that you're not picking on them, but trying to help, can go a long way towards improving the posts.
I need to keep that in mind to specifically answer people in a calmer manner when they're writing a mile a minute and help them in more ways than just the technical.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 27, 2009 at 9:39 am
One more post towards 1000.... and close to the original topic here. How many people get annoyed by people who cut and paste data rather than giving a script to create the temp table(s)? It's better than nothing at all, but....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 931 through 945 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply