|
|
|
Right there with Babe
 
Group: General Forum Members
Last Login: Monday, March 15, 2010 8:38 AM
Points: 761,
Visits: 140
|
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Monday, March 01, 2010 9:15 AM
Points: 397,
Visits: 262
|
|
CTE's have their place as long as you are not dealing with a large volume of data. One of the problems with CTEs is performance tuning. Unless I'm missing something there is no way to set primary keys or index the CTE. I've used CTE's in applications where I'm dealing with 1k-5k rows of data. Beyond that I'll use a temp table.
Thanks for the information.
Kurt
Kurt W. Zimmerman
SR DBA
Sponsor Direct
Tarrytown, NY
http://www.linkedin.com/in/kurtwzimmerman
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 11:26 AM
Points: 273,
Visits: 324
|
|
Actually the CTE uses the table indexes quite nicely, just review the query plan and you will see it pulls the indexes from the table. (Most of the time) :D
I do agree however sometimes it is hard to figure out where in a large CTE the performance is slow. Usually the best bet, also the longest time waster is to break apart the query and run each building on each until you find the slow query. I just did that same process for a weighted search and found I was missing a few indexes on some key look up columns that should have been found earlier when I was using the tuning wizard.
Senior Consultant
8yrs IT, 6yrs SQL
(select * from users where clue > 0)
0 results found
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 9:40 AM
Points: 351,
Visits: 138
|
|
Good article. Well articulated.
I would just like to state that perhaps the following point about CTE usage should have been mentioned as well since that is something which, if a person is not aware of, can lead to unnecessary headaches!!
** The query using the CTE must be the first query appearing after the CTE.
For ex, based on your query in Listing 1, we couldn't do the following:
WITH emp AS (
SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID
FROM HumanResources.Employee AS E
INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID
)
-- First query after CTE not using the CTE
SELECT * FROM HumanResources.Employee
-- Second query after CTE using the CTE will return the "Invalid column name" error message
SELECT A.EmployeeID, A.FirstName, A.LastName, A.Title,
A.ManagerID, B.FirstName AS MgrFirstName,
B.LastName AS MgrLastName, B.Title AS MgrTitle
FROM emp AS A INNER JOIN emp AS B ON A.ManagerID = B.EmployeeID;
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Monday, March 01, 2010 9:15 AM
Points: 397,
Visits: 262
|
|
Adam Seniuk (12/8/2009)
Actually the CTE uses the table indexes quite nicely, just review the query plan and you will see it pulls the indexes from the table. (Most of the time) :D
I do agree however sometimes it is hard to figure out where in a large CTE the performance is slow. Usually the best bet, also the longest time waster is to break apart the query and run each building on each until you find the slow query. I just did that same process for a weighted search and found I was missing a few indexes on some key look up columns that should have been found earlier when I was using the tuning wizard.
I had a stored procedure that performed "global search" that queried 3 tables which were rather large in size. I had gone ahead and tuned each separate query. The stored procedure really stressed the system. I simply removed the CTEs and utilized indexed temp tables. This made the performance almost 100% better.
Just my observations.
Kurt
Kurt W. Zimmerman
SR DBA
Sponsor Direct
Tarrytown, NY
http://www.linkedin.com/in/kurtwzimmerman
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 7:12 AM
Points: 695,
Visits: 746
|
|
Kurt W. Zimmerman (12/8/2009)
[quote]
I had a stored procedure that performed "global search" that queried 3 tables which were rather large in size. I had gone ahead and tuned each separate query. The stored procedure really stressed the system. I simply removed the CTEs and utilized indexed temp tables. This made the performance almost 100% better.
Just my observations.
Kurt
When not used recursively, a CTE is essentially a different syntax for a subquery. They will perform precisely the same as a subquery and generate the same execution plan.
Now of course a recursive CTE is a different animal performance wise and you can often get better performance with a non-CTE, Non-recursive answer. I touched on this briefly at http://www.sqlservercentral.com/articles/Common+Table+Expression+(CTE)/62404/ and Peter He discussed this in depth at http://www.sqlservercentral.com/articles/T-SQL/2926/
Now, there are certainly times that an indexed temp table in a stored procedure can perform better than using subqueries, especially if they are complicated and nested. But in that case it would perform better than any formulation of the subquery, not better than a CTE in particular. As Adam pointed out, the CTE is capable of using the available indexes as well as any other query or subquery.
|
|
|
|
|
SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 5:05 PM
Points: 668,
Visits: 1,338
|
|
Great article. You might want to include some things though. I use a CTE to get me out of awkward JOIN situations. If I want to count the number of Customers assigned to a Worker and print the name from the table [Worker] I have to add every stinking column to the GROUP BY. But with a CTE I can take this preaggregate: SELECT WorkerId, COUNT(CustomerId)
FROM WorkerCustomer
GROUP BY UserId
and the JOIN to it. Since the GROUP BY only applies to the sub-query.
Also the syntax allows you to specify the column names coming out so that I don't have to put the AS after the COUNT(). It looks like this: WITH UC (UserId, CustomerCount) AS ( ... )
But then I needed two CTEs in the same query. That syntax is a bit on the awkward side (IMHO). You specify the first CTE then a comma and the second CTE without the WITH. The whole thing is below.
WITH WC (WorkerId, CustomerCount)
AS (
SELECT WorkerId, COUNT(CustomerId)
FROM WorkerCustomer
GROUP BY WorkerId
)
, WO (WorkerId, OrderCount)
AS (
SELECT WorkerId, COUNT(OrderNumber)
FROM [SalesOrder]
GROUP BY WorkerId
)
SELECT
[Worker].Name
,[Worker],Supervisor
,COALESCE(WC.CustomerCount,0) AS [Customer Count]
,COALESCE(WO.OrderCount,0) AS [Order Count]
FROM [Worker]
LEFT OUTER JOIN WC ON WC.WorkerId = [Worker].ID
LEFT OUTER JOIN WO ON WO.WorkerId = [Worker].ID
ORDER BY WC.CustomerCount DESC, WO.OrderCount DESC
ATB
Charles Kincaid
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, March 14, 2010 6:35 PM
Points: 141,
Visits: 682
|
|
Lovely concise, easy to understand introduction. Thank you!
Nicole Bowman
Nothing is forever.
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 3:06 AM
Points: 3,
Visits: 87
|
|
Aren't most of your examples just syntactic sugar that could be done with a subquery?
I would prefer to use simpler constructs if possible for ease of future maintenance.
From what I can see, there are only two situations where a Common Table Expression is really useful:
1) Where you need to join a subquery to itself, so you would otherwise need to either duplicate the subquery or use a temp table.
2) Where you want to use recursion.
Can anyone thing of any others?
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, January 28, 2010 3:52 AM
Points: 308,
Visits: 143
|
|
Nice article.
I know that this is not a place to ask questions, but some how it is related to CTE execution. So..
I have a CTE, which contains around 4-5 tables joined within it and then outside CTE, I joined 4 tables to that CTE. (in general, I am joining around 9 tables in total).
So what is the intermediate steps of execution, in the light of CTE.
As, we all know FROM will get executed very first and then WHERE, then GROUP BY ...., in the normal SELECT.
Can someone help me on this.
Thanks in advance.
e.g.
;WITH CTE
AS
(
SELECT Table1.ID AS ID, Table1.Code AS Code, Table1.Name AS Name, Table5.Description AS Description
FROM Table1
INNER JOIN Table2 ON Table1.Sub_ID = Table2.ID
INNER JOIN Table3 ON Table2.Sub_ID = Table3.ID
INNER JOIN Table4 ON Table3.Sub_ID = Table4.ID
INNER JOIN Table5 ON Table4.Sub_ID = Table5.ID
)
SELECT .....
FROM CTE
INNER JOIN Table6 ON CTE.ID = Table6.ID
INNER JOIN Table7 ON Table6.Sub_ID = Table7.ID
INNER JOIN Table8 ON Table7.Sub_ID = Table8.ID
WHERE <<some condition....>>
|
|
|
|
