CTE's are useless

  • 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

  • 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

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • Seggerman (4/25/2008)


    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

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • 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

  • Can anyone tell me CTE uses temp db or memory to hold the data for next statement?

  • 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

  • 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