March 18, 2013 at 2:38 pm
Extra typing always makes anything more complex.
By definition.
Actually by definition that makes it more verbose. Verbosity is not the same thing as complex.
But if to ingnore our personal preferences - the query written using DT is simpler than the same query written using CTE.
😎
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 2:42 pm
Most developers used to wrire "CSS style" code.
CTE fits there patterns and allows not to learn SQL-specific best practices.
That's why it's so popular.
What does that mean? What is "CSS style" code and how does whatever that mean prevent somebody from using "SQL-specific best practices"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 2:54 pm
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.
Exactly my point.
Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.
But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.
_____________
Code for TallyGenerator
March 18, 2013 at 3:01 pm
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.Exactly my point.
Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.
But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.
So a derived table in a FROM clause would have the same problem, wouldn't it.
It would seem that a view would also have the same issue as well.
March 18, 2013 at 3:02 pm
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.Exactly my point.
Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.
But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 3:18 pm
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.
Hmm...
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.
But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.
Do you agree with me on this?
_____________
Code for TallyGenerator
March 18, 2013 at 3:21 pm
Lynn Pettis (3/18/2013)
So a derived table in a FROM clause would have the same problem, wouldn't it.It would seem that a view would also have the same issue as well.
Definitely not.
You may write a horrible query causing a huge hash join and see tempdb filled with data with no temporary objects created in it.
_____________
Code for TallyGenerator
March 18, 2013 at 3:23 pm
Sean Lange (3/18/2013)
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.Exactly my point.
Unlike Lynn said, using CTE has no preference comparing to using derived tables.
+1.
But CTE imposes a danger.
CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.
So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.
Especially it's dangerous in multi-user environment.
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.
I think CTEs make complex queries easier to read and understand instead of using derived tables in multiple from clauses in a query.
But, to each their own.
March 18, 2013 at 3:29 pm
Sergiy (3/18/2013)
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.Hmm...
I saw a server stopped operating because it's run out of memory, while its tempdb was basically empty.
At the moment I fixed the problem by changing CTE's to DT's.
But probably I did something else to improve the queries.
Will need to check later.
But anyway - even if you're right and CTE's can use tempdb - it makes them totally equal to DT's.
No advantage to any method, except for that extra line of code required by CTE syntax.
Do you agree with me on this?
Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference. My preference is the age old "it depends". I find that sometimes I prefer one over the other. I do often find that using a CTE can make an otherwise complicated query a bit easier to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 18, 2013 at 4:06 pm
Sean Lange (3/18/2013)
Of course a CTE can use tempdb. Just like a temp table, if the memory pressure is too much it will absolutely start writing data to tempdb.
What would you say about that?
http://www.sql-server-performance.com/2012/common-table-expressions-cte-developers/
f you are using larger data sets in table variables it will use tempdb to store data physically. However, a CTE always uses memory. This can be an issue in electing for CTEs as you need to ensure that you are not using larger data sets that will drain memory. The following graph shows tempdb write transactions/sec for temp tables, table variables and CTE scenarios.
_____________
Code for TallyGenerator
March 18, 2013 at 4:15 pm
Another one:
I have some questions about it, though.
Will do some excersises around it, when have some spare time.
_____________
Code for TallyGenerator
March 18, 2013 at 4:16 pm
Hmmmmm... Based on what I saw of the article, I'd have to say that the conclusions drawn were unwarranted. I didn't see anywhere where the 3 different queries were proven to be equivalent, nor did I see the data volumes being worked by the queries. I don't think you can draw the conclusion from the evidence given that CTEs will never write to tempdb. Could be true, though counter-intuitive, but I don't think the article proved it.
March 18, 2013 at 4:22 pm
How can I remove my post?
😀
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 18, 2013 at 4:25 pm
Sean Lange (3/18/2013)
Yes I don't think there is a clear cut advantage for either approach as far as performance is concerned. I think it boils down to preference.
If you come back to where it's started you'll see it's exactly what I said.
The only difference is as minor as an extra line of code required for syntax sake only.
I'm not so sure about that "in-memory" thing, therefore I did not bring it up, until you guys forced me. 🙂
But I know there is controversy around it.
That's why I prefer (and suggest) not to use CTE where it's not necessary.
Again, it's my preference.
_____________
Code for TallyGenerator
March 18, 2013 at 4:44 pm
From the MS site:
A common table expression can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
When the query plan for a common table expression query uses a spool operator to save intermediate query results, the Database Engine creates a work table in tempdb to support this operation.
http://msdn.microsoft.com/en-us/library/ms345368(v=SQL.105).aspx
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply