CTEs - Formatting Not Performance
Published: 2nd March 2014
I was reading a forum the other day in which someone was asking whether they would be better off changing their code to use CTEs for performance gains. I was surprised by the amount of responses by people thinking that CTEs are a performance tool.

In all honesty there are a few situations in which CTEs are very useful and can outperform traditional coding methods (such as recursion). However, in all other cases they are simply a tool for making code clearer and have no effect on performance.

Here’s a clear example to demonstrate this:

This is a quick query I knocked up in AdventureWorks which simply gives the difference in sales from one day to the next in July 2005 (there are better ways to write this, I just wanted to use this method to prove the point)…

use AdventureWorks2012
go

select b.OrderDate, b.totalSales - a.totalSales as comparativeSales
from
(
  
select row_number() over(order by orderDate) dateID, orderDate, sum(totalDue) totalSales
  
from sales.salesOrderHeader
  
where orderDate between '2005-07-01' and '2005-07-31'
  
group by orderDate
) a
join
(
  
select row_number() over(order by orderDate) dateID, orderDate, sum(totalDue) totalSales
  
from sales.salesOrderHeader
  
where orderDate between '2005-07-01' and '2005-07-31'
  
group by orderDate
) b
on a.dateID = b.dateID - 1


We can see from SQL Sentry that the plan looks like the following:

So now let’s use a CTE… this will replace our derived table and provide us with much more readable code…

use AdventureWorks2012;
go

with cte_totalByDate as
(
  
select row_number() over(order by orderDate) dateID, orderDate, sum(totalDue) totalSales
  
from sales.salesOrderHeader
  
where orderDate between '2005-07-01' and '2005-07-31'
  
group by orderDate
)
select b.OrderDate, b.totalSales - a.totalSales as comparativeSales
from cte_totalByDate a
join cte_totalByDate b
on a.dateID = b.dateID - 1


Now let’s look at the resulting execution plan…

As you can see… they’re identical.

Basically, as seen above, a CTE is there to make code much easier to read and to provide more power to your derived statements (by using multiple CTEs back to back to filter results)… but they actually don’t help performance. They are literally substituted back in as if you have written the unwieldy old style code.

As mentioned, there are gains in some specific areas such as hierarchical recursion, but when using them simply as a replacement to lengthy derived table queries there is no performance impact.
Comments:
NB: Comments will only appear once they have been moderated.