CTE vs Derived Table

Question

CTE vs Derived Table

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Jeff Moden (11/12/2008)
ggraber (11/12/2008)
I was really looking for some basic guidelines.
I really have come to love the readability of CTEs.
I just wanted to make sure I wasn't compromising performance.
I guess if I had to write some basic guidelines, I'd say that CTE's generally have the same performance as Derived Tables and are sometimes faster. Because of that, their extreme readability, and their logical top down logical flow, I can't see using anything else in 2k5. I imagine there are exceptions, but I just haven't found one yet. Of course, I try to avoid recursive CTE's as much as I do any form of RBAR.
So, I'd say, rest easy... you're not compromising performance by using CTE's over derived tables. However, if you need the same result set from a CTE in more than one Select in the same proc, a nicely indexed Temp table can pretty much blow away CTE performance over the long haul.

And, as always, it depends. So test, test, and test again. Make sure what you are doing is the best.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden (11/12/2008)
Well I'll be damned... didn't even notice until just now... 12,000+ posts... Steve owes me a shirt or two! 😛

Actually, according to the scores and standings page (2008-11-12 21:49 MST) you have 11868 forum posts. Your TOTAL points has exceed 12,000.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

... thanks for the leg up, Lynn. 😛

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (11/12/2008)
... thanks for the leg up, Lynn. 😛

Just keeping you honest! :w00t:

I still haven't even hit 3,000 forum posts, but I'm getting close!

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 5

Damn, I learn a lot by lurking on this site. Guess I'll start using more temp tables. Thanks to all.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Jim Russell (11/13/2008)
Damn, I learn a lot by lurking on this site. Guess I'll start using more temp tables. Thanks to all.

Oh, be careful, Jim... while I'll admit to using Temp tables alot (I'm mostly a "large batch" programmer), like anything else, they're not a panacea. There's a lot of code, like Adam's for example, that is many times faster than the use of a temp table especially when the correct indexes are in play for the code. If you don't need the result that is stored in a temp table more than once, then a well formed CTE or Derived table is probably the better way to go.

--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)

Mauve SSChampion Points: 11316 More actions · Answer 7

I'll offer my thoughts on a CTE vs. derived table:

1. I'll continue to code CTEs as they are simplier and easier to maintain, along with being a bit "self documenting".

2. I know that performance is the same as a derived query. But that that may change in the future (are you listening Microsoft?) when the query optimizer is improved. In Oracle, CTEs are properly utilized by the query optimizer (the sub-query is run only once) and not just utilized as a shortcut for the query authoring.

3. Should performance be an issue it is relatively easy to take the CTE query, as it is stand-alone, and use it to populate a temporary table. Only a minor tweak (changing the table name) to the remainder of the query is necessary.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 8

You are right as usual, Jeff. I was mainly thinking of the cases where a CTE is referenced multiple times, and had always assumed that the results were cached somehow. Now that I know that it is simply re-executed, I will use a temp table to capture the results for multiple references.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 9

Jim Russell (11/13/2008)
You are right as usual, Jeff. I was mainly thinking of the cases where a CTE is referenced multiple times, and had always assumed that the results were cached somehow. Now that I know that it is simply re-executed, I will use a temp table to capture the results for multiple references.

Thanks for the feedback, Jim. I think you'll find that the CTE will always win if there are only 1 or 2 references in the same query. I don't believe the Temp table will break even until 3 especially if you have to add an index to the Temp table.

Of course, if you mean that the CTE has to be copied and pasted in multiple places in the same batch, then you're spot on... the Temp table will likely break even or get ahead at 2 and certainly outshine on 3.

--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)

Virendra Singh Valued Member Points: 53 More actions · Answer 10

Goldie Graber (11/9/2008)
Are there any benefits in using a CTE over a derived table? Or vice versa?
Dear Team,
CTE,TempTable,DerivedTable in these which is best according to performance.
Please help

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

In the two or three cases where I have modified a query using CTEs to #temp tables, I have seen significant performance improvements. (Not careful study or timings, just ad-hoc observations.)

I would expect best performance from views, but, for those that take noticeable time to execute, I typically materialize a table with the view results, and reference that in subsequent queries. (We only update biweekly.)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 12

Virendra Singh (4/14/2009)
Goldie Graber (11/9/2008)
Are there any benefits in using a CTE over a derived table? Or vice versa?
Dear Team,
CTE,TempTable,DerivedTable in these which is best according to performance.
Please help

Virenda, why don't you take the time to read through all the posts in this thread?

I think you will find that your question has already been answered.