July 8, 2010 at 7:14 am
Once again I turn to these forums for an opinion.
CTEs are the being used by our bi developers. I look at the code and wonder why they don't turn the thing into a view and join it that way. I think in a view the code would always be the same and using a CTE the code must be repeated from query to query and that seems prone to error. Is there a performance gain using CTE?
Any thoughts?
Example
WITH Maxlocked AS
(
SELECT MAX(LastBillReleaseDateKey) AS Period
FROM Invoice WITH(NOLOCK)
WHERE LockStatusKey = 1
)
SELECT
CAST(CONVERT(varchar(10),getdate(),112) AS Int) AS CurrentDate
,MAX(ISNULL(BillReleaseDateKey, LastBillReleasedateKey)) AS ReleaseDate
FROM Invoice WITH(NOLOCK)
INNER JOIN Maxlocked ON Invoice.LastBillReleaseDateKey = Maxlocked.Period
Vs
SELECT
CAST(CONVERT(varchar(10),getdate(),112) AS Int) AS CurrentDate
,MAX(ISNULL(BillReleaseDateKey, LastBillReleasedateKey)) AS ReleaseDate
FROM Invoice WITH(NOLOCK)
INNER JOIN VW_Maxlocked as Maxlocked ON Invoice.LastBillReleaseDateKey = Maxlocked.Period
Steve
July 8, 2010 at 7:28 am
There will be no performance impact.
With both SQLServer will evaluate the 'best' query plan at execution time.
For this purpose CTE's are Views and vice-versa.
July 8, 2010 at 7:55 am
We use CTE's in our environment for 'on the run' queries where code is most likely not going to get repeated. For one time use, we do not want to create another object in our databases.
July 8, 2010 at 8:04 am
we used to have a developer who loved views. he created them for everything even if they only returned a few rows. when i had to troubleshoot performance issues with a scheduled process it was a nightmare pulling up all the views and looking back and forth between them.
i use them and they have their place, but i think it's something where you can create a few of them for use by multiple queries.
July 8, 2010 at 8:46 am
From the perspective of the optimiser, they will have the same performance, however as the above poster said, views tend to be re-used, so different developers will tend to find views that have most of what they need, but not quite all, so they'll join them back to the underlying tables again to get other columns and join two views together that actually query the same underlying tables, ending up with a mess that is going to have pretty poor performance.
At least with CTE's, people are more inclined to change the CTE to fit what the query needs which should end up with better performance.
July 8, 2010 at 2:57 pm
CTEs are also a technique to improve readability because they read from top to bottom, rather than from inside out like subqueries. I often use them to break down complicated CASE statements into logical steps. But I wouldn't want to make every CTE into a view because (as has already been pointed out) over-proliferation of views can create problems.
A general rule of thumb is that if you have certain joins and/or filters which are used over and over again, a view comes in handy. This of course assumes that your developers are aware of the existence of such views. A little code review might make them aware that they could save a little time and trouble by using existing views. But be careful, they might also start joining those views to tables already contained in the views themselves. When that happens, performance suffers.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 9, 2010 at 6:35 am
I agree with your second paragraph. Code reviews are helpful and knowledge of the environment is important. I even think the idea of one-off coding is correct.
Where I disagree is in your first paragraph. I am working with a 640 line CTE and it seems like complicated SQL code. It has UNIONs and inside out subqueries and finally a pivot table construct (not that any of those are bad, they just don't lend themselves to readablity). Ok so when you get to the main query it is just referenced but a view does that. In this other example, I'm not sure its simpler.
July 9, 2010 at 6:39 am
If readability / re usability and maintainability are issues then I would be looking at using Inline table valued functions.
July 9, 2010 at 6:46 am
Early versions of tables caused such odd performance problem, I developed an aversion to them. Please tell me more about the table returning functions, they seem not to have the performace problems that some scalar function can have.
Any examples?
July 9, 2010 at 6:55 am
Multi line table valued functions are to be avoided, inline table valued functions are to be embraced.
See Simon Sabin's session in the recent 24Hours of Pass , here http://www.sqlpass.org/LearningCenter/24Hours.aspx
July 9, 2010 at 4:23 pm
It has UNIONs and inside out subqueries and finally a pivot table construct (not that any of those are bad, they just don't lend themselves to readablity).
Agreed, but these are to be distinguished from the CTE itself.
CTEs aren't a panacea, and they can be written poorly. A complex query is a complex query. You can break it down into steps and stop to store values in working tables, but sometimes that can really slow down the processing time. (Other times it can speed it up, but that's for a different thread.)
If a solution requires one or more UNIONS, multiple subqueries and a PIVOT, imagine how hellish it would be to decipher a single query containing all those elements prior to CTEs. What CTEs buy you is the ability to break a complex query down into logical steps, that read from top to bottom, so you don't have to focus on all 600+ lines at once. Each step can be isolated in its own CTE. One cte creates a result set, a second creates a simlar result set, a third does a union of the those two sets, a forth applies an inline table valued function to the result of the union, a fifth does a PIVOT, etc. Yes it will take more lines on a page, but it produces a single query plan.
You used to have to do the same thing with subqueries/derived tables only, which read from inside out. Top to bottom is more intuitive.
P.S. No argument that where the same functionality is required over and over again, views and/or inline table valued functions are appropriate.
__________________________________________________
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply