Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Introduction to Common Table Expressions Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 4:43 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 8:32 AM
Points: 769, Visits: 256
Comments posted to this topic are about the item Introduction to Common Table Expressions

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #822788
Posted Tuesday, December 8, 2009 7:01 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #830631
Posted Tuesday, December 8, 2009 7:26 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 1:07 PM
Points: 535, Visits: 881
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.



Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
Post #830664
Posted Tuesday, December 8, 2009 7:28 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 28, 2013 10:28 AM
Points: 461, Visits: 230
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;
Post #830667
Posted Tuesday, December 8, 2009 7:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
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
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #830686
Posted Tuesday, December 8, 2009 12:47 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #830932
Posted Tuesday, December 8, 2009 1:08 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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

Post #830958
Posted Tuesday, December 8, 2009 3:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 3:19 PM
Points: 188, Visits: 1,564
Lovely concise, easy to understand introduction. Thank you!

Nicole Bowman

Nothing is forever.
Post #831078
Posted Wednesday, December 9, 2009 2:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 4, Visits: 581
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?
Post #831230
Posted Wednesday, December 9, 2009 4:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198
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....>>

Post #831290
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse