January 29, 2009 at 1:16 pm
In 2005, the exciting feature is Common table expression. Is use of CTE is cost effective?
January 29, 2009 at 1:18 pm
Consultant's answer (while rubbing one's chin) "That depends."
I have found that they make writing some of the more complex queries easier, as well as more readable especially where you may have been using derived tables in a from clause in SQL Server 2000 or earlier.
January 29, 2009 at 1:21 pm
Well, they're very useful for certain things, and not very good at certain other things.
They are good at resolving recursive hierarchy data.
They are good at taking the place of derived tables in the From clause (because they're the same thing).
They're good at allowing you to assign row numbers to data, if that matters.
They are not good at certain other things, like when you need to work on the same data in more than one query in the same process.
Of all the new features in SQL 2005, I would have to say they are far from the most exciting. I'd have to go with the Include feature for covering indexes on that measure.
- 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 29, 2009 at 1:23 pm
I agree .."It depends" .
Recently when I chekced the execution plan for a slow running query ..the CTE's were the one which were with more percentages.
The I/O cost was more as compared to any other operation in that query.
January 29, 2009 at 1:25 pm
That doesn't mean the CTE is the problem (if it is a problem). It could be the query written in the CTE that needs to be reviewed.
January 29, 2009 at 1:28 pm
Or it could be that the CTE is doing heavy lifting because the load is a big one.
There's nothing wrong with a piece of a query having more than its fair share of the percentage of work, if what it's doing is handling a lot of data so the rest of the query can handle small pieces of the data.
It's only a problem if it could be better and 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
January 30, 2009 at 4:00 am
Except for the recursion, a CTE is really just a more sophisticated way to create sub-selects. So they're just as effective as the sub-selects were with the added value that you only have to define them once for a given query, so you can reuse them, alias them, within a single statement. So, yeah, I'd say they're effective.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply