April 23, 2008 at 11:54 am
Jeff Moden (4/22/2008)
GSquared (4/22/2008)
I would definitely avoid that "speed at all cost" methodology.Heh... then why have you posted so much on this thread about speed? 😉 The real answer is that speed does matter, always, and if you always consider writing for "speed" and scalability, you will never run into problems. Yes, yes... the code must always be correct and stabile... that's a given...
But, let me ask you this... have you ever used a Tally or Numbers table? A While loop also does the job and produces the correct and stabile answer so why do YOU use a Tally table? Speed. Have you ever intentionally avoided writing a UDF and written inline code instead? Why? Speed. Have you ever written an indexed view? Why? Speed. Have you ever looked at an execution plan? Why? Speed.
You can avoid the "speed at all cost" methodology if you want... but I'd suggest that you haven't and you won't. Considering all that you've written about speed testing on this very thread and the fact that you said "The only time to compromise on performance is when integrity would be threatened by it", I'd have to say your comment about my writing for speed is a bit contrary to what you practice and a bit out of line, as well.
Jeff: I think I may have written more into what I wrote than I intended. Or something like that.
I was agreeing with you, but just adding that caveat, not for you, but for the cases where I've run into developers who sacrifice ACIDity for speed, and don't understand that they've done so.
I definitely know that you, specifically, don't need that warning. Nor do most of the people on this page. But someone might, so I thought I'd say it.
As for what I've been writing on this exact thread about speed, yeah. Definitely. I believe in the need for performance and scalability. No hypocrisy there. If there seems to be, then I miswrote something.
I'm definitely not challenging you on this. You know a lot more about SQL than I do. If I came across as accusing you of something, I very definitely didn't mean to.
- 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
April 23, 2008 at 12:09 pm
My point is that, done correctly[/b], high performance code will always have the correct integrity.
(Emphasis added.)
Totally agree with you, so long as that statement is included. But that statement isn't "speed at any cost". I was specifically refering to that exact statement. "Speed at any cost."
I already know you don't do the kind of stupid things that will shave a tiny bit off the run-time at the cost of ACID transactions. Fortunately or unfortunately, you're not the only one who reads this web page. I'm not accusing you of anything at all, but I'm certainly accusing a few people I work with of violations on this point.
(Then, of course, I also run into stuff that seems to "use as many cursors as possible, at any cost", and the speed also gets thrown out the window. 🙂 )
I've seen inserts into table variables, using "with (nolock)", resulting in data corruption issues in concurrent transactions. I've seen "begin transaction... commit", just to speed the proc up, regardless of the fact that, if a later transaction in the same proc failed, the first transaction needed to be rolled back too. I've seen local fast_forward cursors used to do line-by-line updates, so that users didn't have to wait for each other (speed), ending in completely junk data being left in the tables.
Those are what I mean by "speed at any cost". No DBA worthy of the title would ever do any of them. But that doesn't mean they don't happen. That is what I'm talking about. And it is all that I'm talking about.
Outside of those situations, I want a Saturn V in my database! Heck with 0-60 in 4 seconds, or a top speed that's measured in miles per hour! I want 7 gs and escape velocity! I just don't want it to explode while I'm in it. 🙂
- 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
April 24, 2008 at 7:16 am
Heh... sorry Gus, I took it the wrong way... thought for sure it was directed at me and didn't understand why especially since you know me pretty well... thank you clearing that up. 🙂
--Jeff Moden
April 25, 2008 at 6:50 am
There are times (not too often) where speed at any cost is unwise
I may be a newbie at SQL Server but in computer programming for over 3 decades
my current situation is a fine example
a tiny group of competent developers are working on software used as much for demos as actual implementations
we code up a lot of features to add razzle dazzle to the package, carefully demo them and then go back and rigorously test what features excited the potential customer
the speed which is paramount is speed of development
I try to make code so simple as to be unbreakable but aren't given the time to test all paths before it gets shown
and yes, I've used CTE's (the original topic of this thread) and cursors
April 25, 2008 at 8:00 pm
Seggerman (4/25/2008)
There are times (not too often) where speed at any cost is unwiseI may be a newbie at SQL Server but in computer programming for over 3 decades
my current situation is a fine example
a tiny group of competent developers are working on software used as much for demos as actual implementations
we code up a lot of features to add razzle dazzle to the package, carefully demo them and then go back and rigorously test what features excited the potential customer
the speed which is paramount is speed of development
I try to make code so simple as to be unbreakable but aren't given the time to test all paths before it gets shown
and yes, I've used CTE's (the original topic of this thread) and cursors
I've found that the speed of development is very often the reason for slow, unscalable code. To coin a phrase... "If they want it real bad, that's usually the way they get it." 😉
--Jeff Moden
September 25, 2009 at 9:31 am
This is an old thread, but my results indicate CTE's may have some use.
I do not claim to be the sql expert most of you are but I converted a recursive cursor I created with a CTE. The CTE is faster for me. This is about 500 records, so not a definitive answer. The CTE also makes retruning the level in the tree easier to boot.
Here are the Results:
CTE
with CTE (Lvl, ID) as
(SELECT 1, party_id FROM dbo.t_party
where party_id = 1
union all
SELECT Lvl + 1, child_party_id FROM dbo.t_party_relationship P2
inner join CTE on P2.parent_party_id = CTE.id AND party_relationship_type_id = 1)
select Lvl, ID
from CTE
Client Execution Time
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements0
Rows affected by INSERT, DELETE, or UPDATE statements0
Number of SELECT statements 1
Rows returned by SELECT statements495
Number of transactions 0
Network Statistics
Number of server roundtrips1
TDS packets sent from client1
TDS packets received from server2
Bytes sent from client576
Bytes received from server5505
Time Statistics
Client processing time15.4
Total execution time46.2
Wait time on server replies30.8
Old way
--RETURNS @AssignedOrgs TABLE(organization_id int primary key)
DECLARE @AssignedOrgs TABLE(organization_id int primary key)
DECLARE @ParentOrgID int
DECLARE @OrgID int
DECLARE @OrgTree TABLE (
organization_id int primary key,
processed char(1)
)
-- prime the organizational tree with the organization to retrieve
INSERT INTO @OrgTree
SELECT 1, 'N'
-- now start scanning the org tree for all descendants
WHILE (SELECT COUNT(1) FROM @OrgTree WHERE processed = 'N') > 0 BEGIN
-- grab the first unprocessed organization
SELECT TOP 1 @ParentOrgID = organization_id FROM @OrgTree WHERE processed = 'N'
-- flag that organization as processed
UPDATE @OrgTree SET processed = 'Y' WHERE organization_id = @ParentOrgID
-- use a cursor to find all the children of this organization
DECLARE FindChildOrg CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT child_party_id FROM dbo.t_party_relationship WHERE parent_party_id = @ParentOrgID AND party_relationship_type_id = 1
OPEN FindChildOrg
FETCH NEXT FROM FindChildOrg INTO @OrgID
WHILE @@FETCH_STATUS = 0 BEGIN
-- only add a child organization once
IF NOT EXISTS(SELECT 1 FROM @OrgTree WHERE organization_id = @OrgID) BEGIN
--NOTE: Code goes here to exclude an organization that the user has been denied access to
INSERT INTO @OrgTree (organization_id, processed) VALUES (@OrgID, 'N')
END
-- process the next child organization
FETCH NEXT FROM FindChildOrg INTO @OrgID
END
CLOSE FindChildOrg
DEALLOCATE FindChildOrg
END
SELECT organization_id FROM @OrgTree
Client Execution Time
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements990
Rows affected by INSERT, DELETE, or UPDATE statements990
Number of SELECT statements 1980
Rows returned by SELECT statements990
Number of transactions 990
Network Statistics
Number of server roundtrips1
TDS packets sent from client1
TDS packets received from server22
Bytes sent from client3648
Bytes received from server86334
Time Statistics
Client processing time300
Total execution time315.2
Wait time on server replies15.2
September 25, 2009 at 12:27 pm
just incidentally while we're on this topic, is there any actual difference between the following two statements?
with cte as
(
select * from table1
)
select * from cte
where field1 = value1
and
select * from
(
select * from table1
) rs
where field1 = value1
October 1, 2009 at 8:03 am
Can anyone tell me CTE uses temp db or memory to hold the data for next statement?
October 1, 2009 at 9:18 am
kramaswamy (9/25/2009)
just incidentally while we're on this topic, is there any actual difference between the following two statements?
with cte as
(
select * from table1
)
select * from cte
where field1 = value1
and
select * from
(
select * from table1
) rs
where field1 = value1
Nope. No real difference. Not to the SQL engine anyway.
- 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
October 1, 2009 at 9:20 am
dipti9patel (10/1/2009)
Can anyone tell me CTE uses temp db or memory to hold the data for next statement?
Yes, they do.
They're just like table variables and temp tables and derived tables. They'll stay in RAM if there's room for them, and move to disk if there isn't.
- 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 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply