November 1, 2011 at 8:02 pm
Folks,
When are the CTEs used best? Is it true that procs that are called thousands of times a minute and store less than 100K of records are best candidates to use CTEs?
Thanks
November 1, 2011 at 10:32 pm
Lexa (11/1/2011)
Folks,When are the CTEs used best? Is it true that procs that are called thousands of times a minute and store less than 100K of records are best candidates to use CTEs?
Thanks
To answer your second question first... No. CTE's are the same thing as a sub-query in the FROM clause known as a "Derived Table". Both are sometimes referred to as "inline views". They both have similar performance and characteristics. (From the sounds of this question, do you really mean "Table Valued Variables"???)
For the first question, one supposed advantage of CTE's is if you need to do a self join, you simply refer to the CTE twice instead of writing the code twice. It may be a coding advantage but the code also executes twice so provides a performance problem rather than being an elegant solution. If you need to do such a thing, write a query to dump a result set into a Temp table and refer to the Temp table twice, instead.
Another supposed advantage to CTE's is that you can write recursive CTE's to supposedly "avoid a loop". Although you avoid the word "WHILE", it's still a loop and it'll usually use more resources than a loop. See the following article for more on why you shouldn't use recursive CTE's.
http://qa.sqlservercentral.com/articles/T-SQL/74118/
The only real advantage to CTE's is that they allow "top down" programming which is easier for most folks to think about. Other than that, there's no real advantage to CTE's over the use of sub-queries in the FROM clause.
--Jeff Moden
November 2, 2011 at 12:14 am
CTE is an abbreviation Common Table Expression. A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
Advantages of using CTE
Using CTE improves the readability and makes maintenance of complex queries easy.
The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
CTE can be defined in functions, stored procedures, triggers or even views.
After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
November 2, 2011 at 5:38 am
Jeff Moden (11/1/2011)
Lexa (11/1/2011)
Folks,When are the CTEs used best? Is it true that procs that are called thousands of times a minute and store less than 100K of records are best candidates to use CTEs?
Thanks
To answer your second question first... No. CTE's are the same thing as a sub-query in the FROM clause known as a "Derived Table". Both are sometimes referred to as "inline views". They both have similar performance and characteristics. (From the sounds of this question, do you really mean "Table Valued Variables"???)
For the first question, one supposed advantage of CTE's is if you need to do a self join, you simply refer to the CTE twice instead of writing the code twice. It may be a coding advantage but the code also executes twice so provides a performance problem rather than being an elegant solution. If you need to do such a thing, write a query to dump a result set into a Temp table and refer to the Temp table twice, instead.
Another supposed advantage to CTE's is that you can write recursive CTE's to supposedly "avoid a loop". Although you avoid the word "WHILE", it's still a loop and it'll usually use more resources than a loop. See the following article for more on why you shouldn't use recursive CTE's.
http://qa.sqlservercentral.com/articles/T-SQL/74118/
The only real advantage to CTE's is that they allow "top down" programming which is easier for most folks to think about. Other than that, there's no real advantage to CTE's over the use of sub-queries in the FROM clause.
So basically there isn't a situation where CTE will outperform temp table or temp table variable, is that correct?
November 2, 2011 at 7:12 am
Lexa (11/2/2011)
So basically there isn't a situation where CTE will outperform temp table or temp table variable, is that correct?
No. Didn't say that. As with anything else, "It Depends" on what you're doing. CTE's can blow the doors off of Temp Table and Table Variable usage and vise versa depending on what you're doing.
There's not much in SQL Server that is totally black and white... most of it is gray and it's because there are different requirements on different data. My recommendation would be to stop looking for black and white rules in SQL Server and learn 3 or 4 different ways of doing the same thing.
--Jeff Moden
November 2, 2011 at 7:21 am
Jeff Moden (11/2/2011)
Lexa (11/2/2011)
So basically there isn't a situation where CTE will outperform temp table or temp table variable, is that correct?No. Didn't say that. As with anything else, "It Depends" on what you're doing. CTE's can blow the doors off of Temp Table and Table Variable usage and vise versa depending on what you're doing.
There's not much in SQL Server that is totally black and white... most of it is gray and it's because there are different requirements on different data. My recommendation would be to stop looking for black and white rules in SQL Server and learn 3 or 4 different ways of doing the same thing.
Ok, thanks.
November 2, 2011 at 8:40 am
Lexa (11/2/2011)
Jeff Moden (11/2/2011)
Lexa (11/2/2011)
So basically there isn't a situation where CTE will outperform temp table or temp table variable, is that correct?No. Didn't say that. As with anything else, "It Depends" on what you're doing. CTE's can blow the doors off of Temp Table and Table Variable usage and vise versa depending on what you're doing.
There's not much in SQL Server that is totally black and white... most of it is gray and it's because there are different requirements on different data. My recommendation would be to stop looking for black and white rules in SQL Server and learn 3 or 4 different ways of doing the same thing.
Ok, thanks.
Going along with that, if you are looking at performance you should always test for your specific scenario; i.e. write 1 query using CTE, another with a table variable, and another with a temp table. However, don't just execute these ad-hoc if you are going to eventually put them into a stored procedure. Create 3 separate SPs and test each several times. If the query is too resource intensive to run on production data or if it changes data, then make sure that the test data is as close to the same including the amount of data. That can change the results quickly. Then use what works best.
Thanks,
Jared
Jared
CE - Microsoft
November 2, 2011 at 11:46 am
A lot of people make the mistake of thinking of CTE's in terms of performance. In the vast majority of cases (the recursive CTE being one example of an exception to this), CTE's are completely performance neutral and execute with the exact same plan as they would if derived tables had been used instead. Each time the CTE is used, the expression within that CTE is unpacked, so if you have a self-joining CTE you will see the query within that CTE performed twice and then joined. Their advantage is mainly in the clarity of code.
November 2, 2011 at 11:54 am
Just to clarify something for my knowledge, i thought CTE can be replacement of temp tables. I've started using CTEs and it is useful in terms of code readability, finding problems etc. as it splits your code top to bottom but once CTE has been used it cant be used second time.
;with CTE_1 as
(select * from table1)
select column1, column2 from CTE_1
,with CTE_2 as
(select * from table2)
select column1, column2 from CTE_2
Now at this point if i need the data from CTE_1, it cant be used as it is already used. I am not sure if this is true but if i have temp table at the top then i can use it after CTE_2 as it exists in the stored procedure execution.
November 2, 2011 at 12:56 pm
dva2007 (11/2/2011)
Just to clarify something for my knowledge, i thought CTE can be replacement of temp tables. I've started using CTEs and it is useful in terms of code readability, finding problems etc. as it splits your code top to bottom but once CTE has been used it cant be used second time.;with CTE_1 as
(select * from table1)
select column1, column2 from CTE_1
,with CTE_2 as
(select * from table2)
select column1, column2 from CTE_2
Now at this point if i need the data from CTE_1, it cant be used as it is already used. I am not sure if this is true but if i have temp table at the top then i can use it after CTE_2 as it exists in the stored procedure execution.
The technical answer is that a CTE is defined within the scope of a single statement. Once you move beyond that statement, the CTE is no longer defined. (You could, however, reference a CTE more than once within a single statement.) The code above is two separate statements within a single batch. If you need to use the data from the CTE in the first statement within the second statement, then you have to load that data into a temp table or a table variable.
November 2, 2011 at 1:14 pm
Jeff Moden (11/1/2011)
For the first question, one supposed advantage of CTE's is if you need to do a self join, you simply refer to the CTE twice instead of writing the code twice. It may be a coding advantage but the code also executes twice so provides a performance problem rather than being an elegant solution. If you need to do such a thing, write a query to dump a result set into a Temp table and refer to the Temp table twice, instead.
I think this needs to be clarified a bit...
Yes, if you have a very complex CTE that with a lot of reads and you have to use that CTE multiple times in your end result it might be better to create a temp table and use that.
With that said, what you are doing is moving the reads from standard tables to temp tables. SQL Server still has to read that data twice, and with temp tables - it also has to write it out to tempdb. So, one process to read the data from the standard tables, a write operation to write it out to tempdb, then 2 reads...
The savings may not be worth the additional overhead of using tempdb. And, if you are going to use that procedure in SSIS/SSRS the temp table is going to cause you problems.
So, as in all things...it depends 😉
I would not arbitrarily state that referencing a CTE twice (or more) will cause a performance issue. Rather, if you are referencing a CTE more than once and having performance issues - it might be worth moving to a temp table to see if that improves performance.
And finally, if I am building a report procedure - I would probably sacrifice a few seconds of performance for ease of maintenance and readability, not to mention being able to use SSRS.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
November 24, 2011 at 2:44 pm
Appoligies for the late reply but I guess I'll have to agree to disagree with you, Jeff. In most cases, the number of rows directed to a temp table are greatly reduced compared to the reads necessary from the original tables.
--Jeff Moden
November 24, 2011 at 3:02 pm
dva2007 (11/2/2011)
Just to clarify something for my knowledge, i thought CTE can be replacement of temp tables. I've started using CTEs and it is useful in terms of code readability, finding problems etc. as it splits your code top to bottom but once CTE has been used it cant be used second time.;with CTE_1 as
(select * from table1)
select column1, column2 from CTE_1
,with CTE_2 as
(select * from table2)
select column1, column2 from CTE_2
Now at this point if i need the data from CTE_1, it cant be used as it is already used. I am not sure if this is true but if i have temp table at the top then i can use it after CTE_2 as it exists in the stored procedure execution.
You can't refer to a CTE outside of the statement in which it's declared then used. You can do this, however, which sidesteps your quandary:
;with CTE_1 as
(select * from table1),
CTE_2 as
(select * from table2)
select column1, column2
from CTE_1 inner join CTE2
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply