Overuse of Common Table Expressions
Let’s explore common table expressions. Like we have shown with scalar-valued functions, the CTE may be useful, but can quickly hinder performance if not used responsibly.
What is a CTE?
Microsoft’s definition of a CTE: Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.
What does that mean?
A CTE is essentially a defined derived table. Derived tables are the tables which are created on the fly with the help of the Select statement. When we defined the derived table as a CTE, it makes the final query certainly more readable.
Defining this as a CTE has one strong advantage. The CTE has the ability to reference itself, also known as recursion. This is certainly a powerful tool when attempting to find the results of a parent-child relationship.
For the purposes of this post, I will recognize that recursion is an advantage but the performance issues that we will address the common practice of using a CTE without the need for recursion.
Comparing CTE to Derived Table
Using the AdventureWorks2012 database, let’s take a look at the execution plan for a simple CTE querying the SalesOrderHeaderEnlarged table.
USE AdventureWorks2012 go WITH cteTotalSales (SalesPersonID, NetSales) AS ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2) FROM Sales.SalesOrderHeaderEnlarged e WHERE e.SalesPersonID IS NOT NULL GROUP BY e.SalesPersonID ) select * from cteTotalSales;
Now, let’s take a look at the execution plan for the same query written as a derived table.
Use AdventureWorks2012 go select * from ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2) as NetSales FROM Sales.SalesOrderHeaderEnlarged e WHERE e.SalesPersonID IS NOT NULL GROUP BY e.SalesPersonID ) derivedTable_TotalSales
Notice how both queries give you the same execution plan. A CTE really is a definable derived table that may be used multiple times throughout the query. This is the double edged sword. Although the query may be more readable, and you can reference the CTE repeatedly, the end result is referencing the same derived table multiple times.
CTE Example
Above I have shown a simple CTE. The SalesOrderHeaderEnlarged table is called once. Defined like that, with smaller recordsets, a CTE is harmless. In a situation like this, if necessary, the CTE can be used to query itself. What happens if the CTE is referenced multiple times? Let’s take a look at a more complex CTE. In this query, we are looking for the net sales, order quantity, sales quota, quota differential and average quantity per sale. We are using three CTEs. The second and third reference the previous two. The final query looks like a relatively simply join with four objects in the from clause.
WITH cteTotalSales (SalesPersonID, NetSales, OrderQty) AS ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2), sum(s.OrderQty) as OrderQty FROM Sales.SalesOrderHeaderEnlarged e inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID WHERE e.SalesPersonID IS NOT NULL and s.ModifiedDate >= '2007-01-01' GROUP BY e.SalesPersonID ), cteTargetDiff (SalesPersonID, SalesQuota, QuotaDiff, OrderQty) AS ( SELECT ts.SalesPersonID, CASE WHEN sp.SalesQuota IS NULL THEN 0 ELSE sp.SalesQuota END, CASE WHEN sp.SalesQuota IS NULL THEN ts.NetSales ELSE ts.NetSales - sp.SalesQuota END, ts.OrderQty FROM cteTotalSales AS ts INNER JOIN Sales.SalesPerson AS sp ON ts.SalesPersonID = sp.BusinessEntityID ), ctePerUnit(avg_qty_per_sale, SalesPersonID) As ( select NetSales/OrderQty, SalesPersonID from cteTotalSales ) SELECT sp.FirstName + ' ' + sp.LastName AS FullName , sp.City , ts.NetSales , ts.OrderQty , pu.avg_qty_per_sale , td.SalesQuota , td.QuotaDiff FROM Sales.vSalesPerson AS sp INNER JOIN cteTotalSales AS ts ON sp.BusinessEntityID = ts.SalesPersonID INNER JOIN cteTargetDiff AS td ON sp.BusinessEntityID = td.SalesPersonID INNER JOIN ctePerUnit as pu on pu.SalesPersonID = ts.SalesPersonID ORDER BY ts.NetSales DESC
See the statistics below. To get 17 records in results, the duration was nearly 4 seconds with 72194 logical reads.
| Text Data | Duration | CPU | Logical Reads |
|---|---|---|---|
| CTE | 3956.8800000 | 8880 | 72194 |
Let’s take a look at the statistics IO breakdown. Notice how the majority of the 72194 reads come from the SalesOrderDetailEnlarged and SalesOrderHeaderEnlarged tables.
------------------- BEGIN CTE ------------------- Table 'SalesOrderHeaderEnlarged'. Scan count 8, logical reads 2712, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderDetailEnlarged'. Scan count 8, logical reads 68888, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 9, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Address'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BusinessEntityAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PersonPhone'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'EmailAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. ------------------- END CTE -------------------
Below we are showing a cross section of the execution plan, along with other offenders, I wanted to show the biggest. Notice how SalesOrderDetailEnlarged and SalesOrderHeaderEnlarged are being queried more than once in the same manor for the same data. This is showing you the cause for the high reads. Our goal as database programmers is to get the data with the database doing as little work as possible. In this case, that goal is not being met.
Derived Table in place of the CTE
In this case, I have altered the CTE query to use the derived table directly rather than the CTE. Let’s take a look at the code. Certainly more lines of code. We have gone from 43 to 52 lines of code. I’m aware I use a lot of spacing, but it is consistent. Even given that, are we really doing more?
SELECT sp.FirstName + ' ' + sp.LastName AS FullName , sp.City , ts.NetSales , ts.OrderQty , pu.Qty_Sales , td.SalesQuota , td.QuotaDiff FROM Sales.vSalesPerson AS sp INNER JOIN ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2) as NetSales, sum(s.OrderQty) as OrderQty FROM Sales.SalesOrderHeaderEnlarged e inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID WHERE e.SalesPersonID IS NOT NULL and s.ModifiedDate >= '2007-01-01' GROUP BY e.SalesPersonID ) AS ts ON sp.BusinessEntityID = ts.SalesPersonID INNER JOIN ( SELECT ts.SalesPersonID, CASE WHEN sp.SalesQuota IS NULL THEN 0 ELSE sp.SalesQuota END as SalesQuota, CASE WHEN sp.SalesQuota IS NULL THEN ts.NetSales ELSE ts.NetSales - sp.SalesQuota END as QuotaDiff, ts.OrderQty FROM ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2) as NetSales, sum(s.OrderQty) as OrderQty FROM Sales.SalesOrderHeaderEnlarged e inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID WHERE e.SalesPersonID IS NOT NULL and s.ModifiedDate >= '2007-01-01' GROUP BY e.SalesPersonID ) AS ts INNER JOIN Sales.SalesPerson AS sp ON ts.SalesPersonID = sp.BusinessEntityID ) AS td ON sp.BusinessEntityID = td.SalesPersonID INNER JOIN ( select NetSales/OrderQty as Qty_Sales, SalesPersonID from ( SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2) as NetSales, sum(s.OrderQty) as OrderQty FROM Sales.SalesOrderHeaderEnlarged e inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID WHERE e.SalesPersonID IS NOT NULL and s.ModifiedDate >= '2007-01-01' GROUP BY e.SalesPersonID ) as derTotalSales ) as pu on pu.SalesPersonID = ts.SalesPersonID ORDER BY ts.NetSales DESC
The answer is no. Let’s look at the basic statistics. The duration is roughly the same, the CPU is roughly the same. The logical reads are roughly the same and, as you see below, the execution plan is exactly the same.
| Text Data | Duration | CPU | Logical Reads |
|---|---|---|---|
| CTE | 3956.8800000 | 8880 | 72194 |
| Derived Table | 3953.0510000 | 8421 | 72198 |
Now, let’s look at the statistics IO output. Again, the majority of the 72198 reads come from the SalesOrderDetailEnlarged and SalesOrderHeaderEnlarged tables.
------------------- BEGIN Derived ------------------- Table 'SalesOrderHeaderEnlarged'. Scan count 8, logical reads 2712, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderDetailEnlarged'. Scan count 8, logical reads 68880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 9, logical reads 105, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Address'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BusinessEntityAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PersonPhone'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'EmailAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 68, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. ------------------- END Derived -------------------
Let’s take a look at the execution plan. No, I did not simply copy and reuse the same image. They are the same execution plan. Again, we are seeing the worst offenders executed multiple times. Notice the parallelism as well. SQL Server has determined that it needs to use more than one core to complete this task. For a well written query, parallelism is a welcomed advantage. CTEs do not always translate to a well written query.
By rewriting the code as derived tables, we can observe through the source code, the statistics and the execution plan that CTEs are simply derived tables put into a more readable code.
CTE Alternatives
Now that we see what CTE are doing behind the scene, what alternatives do we have? Each database developer may have different answers, but the most common would be table variables or temporary tables. Of course, this is debatable. We will explore that debate in the future.
Table Variable CTE Alternative
Let’s take a look at the code re-written to use table variables.
declare @totalSales table ( SalesPersonID int , NetSales money , OrderQty bigInt ) declare @TargetDiff table ( SalesPersonID int , SalesQuota money , QuotaDiff money ) declare @perUnit table ( Qty_Sales money , SalesPersonID int ) insert into @totalSales(SalesPersonID, NetSales, OrderQty) SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2), sum(s.OrderQty) as OrderQty FROM Sales.SalesOrderHeaderEnlarged e inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID WHERE e.SalesPersonID IS NOT NULL and s.ModifiedDate >= '2007-01-01' GROUP BY e.SalesPersonID insert into @targetDiff(SalesPersonID, SalesQuota, QuotaDiff) SELECT ts.SalesPersonID, CASE WHEN sp.SalesQuota IS NULL THEN 0 ELSE sp.SalesQuota END, CASE WHEN sp.SalesQuota IS NULL THEN ts.NetSales ELSE ts.NetSales - sp.SalesQuota END FROM @totalSales AS ts INNER JOIN Sales.SalesPerson AS sp ON ts.SalesPersonID = sp.BusinessEntityID insert into @perUnit(Qty_Sales, SalesPersonID) select NetSales/OrderQty, SalesPersonID from @totalSales SELECT sp.FirstName + ' ' + sp.LastName AS FullName , sp.City , ts.NetSales , ts.OrderQty , pu.Qty_Sales , td.SalesQuota , td.QuotaDiff FROM Sales.vSalesPerson AS sp INNER JOIN @totalSales AS ts ON sp.BusinessEntityID = ts.SalesPersonID INNER JOIN @targetDiff AS td ON sp.BusinessEntityID = td.SalesPersonID inner join @perUnit as pu on pu.SalesPersonID = ts.SalesPersonID ORDER BY ts.NetSales DESC
I’m all about pretty and readable code. We’re defining the table variables, the columns and data types explicitly. This allows the next person to see this code have a better understanding of what is going on.
Are there other benefits? Yes there are. Let’s take a look at the overall statistics. Our duration isn’t much less at 3.5 milliseconds, but both our CPU and Logical Reads are less than half of what the CTE/Derived table are.
| Text Data | Duration | CPU | Logical Reads |
|---|---|---|---|
| CTE | 3956.8800000 | 8880 | 72194 |
| Derived Table | 3953.0510000 | 8421 | 72198 |
| Table Variable | 3579.8140000 | 3573 | 35972 |
We can see that as well from the statistics IO output. You’ll notice two stark differences. The first is the amount of logical reads on SalesOrderDetailEnlarged and SalesOrderHeaderEnlarged. Both are roughly 50% of what they were in the CTE/Derived Table version of the query.
If you look closely, there is one more difference. The Scan Count. For our main tables, the scan count is one. For the CTE/Derived Table, the Scan Count is EIGHT. This scan count shows that parallel processing was determined to be necessary for SQL Server to complete its task. Table Variables to not allow parallel processing.
------------------- BEGIN Table Variable ------------------- Table '#A569A960'. Scan count 0, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderDetailEnlarged'. Scan count 1, logical reads 34300, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeaderEnlarged'. Scan count 1, logical reads 1237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A65DCD99'. Scan count 0, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A569A960'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A751F1D2'. Scan count 0, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A569A960'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PersonPhone'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'EmailAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Address'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BusinessEntityAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A65DCD99'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A751F1D2'. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#A569A960'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. ------------------- END Table Variable -------------------
Now, let’s see the improvements from the execution plan below in several screenshots.
The most costly is the insert into @totalSales, replacing the CTETotalSales. The rest also use the table variables to replace the CTE. The end result is at least a 50% improvement in performance.
Temporary Table Alternative
Temporary tables are my go to choice. The biggest advantages are the ability to create non-unique clustered/non-clustered indexes and the ability to use statistics in the execution plan. The biggest difference that you will see in the query and execution plan is that table variables to not use parallelism. I’ve mentioned that parallel execution and query quality are not mutually exclusive. A well written query can use parallel execution thus taking advantage of the extra power.
Let’s take a look at the query rewritten using temporary tables. The biggest difference, at least in the code, is the ability to add the non-unique clustered indexes to the table.
if object_id('tempdb..#totalSales') is not null
drop table #totalSales
create table #totalSales
(
SalesPersonID int ,
NetSales money ,
OrderQty bigint
)
create clustered index #ix#totalSales on #totalSales(SalesPersonID)
if object_id('tempdb..#targetDiff') is not null
drop table #targetDiff
create table #targetDiff
(
SalesPersonID int ,
SalesQuota money ,
QuotaDiff money
)
create clustered index #ix#targetDiff on #targetDiff(SalesPersonID)
if object_id('tempdb..#perUnit') is not null
drop table #perUnit
create table #perUnit
(
Qty_Sales money ,
SalesPersonID int
)
create clustered index #ix#perUnit on #perUnit(SalesPersonID)
insert into #totalSales(SalesPersonID, NetSales, OrderQty)
SELECT e.SalesPersonID, ROUND(SUM(e.SubTotal), 2), sum(s.OrderQty) as OrderQty
FROM Sales.SalesOrderHeaderEnlarged e
inner join sales.SalesOrderDetailEnlarged s on s.SalesOrderID = e.salesOrderID
WHERE e.SalesPersonID IS NOT NULL
and s.ModifiedDate >= '2007-01-01'
GROUP BY e.SalesPersonID
insert into #targetDiff(SalesPersonID, SalesQuota, QuotaDiff)
SELECT ts.SalesPersonID,
CASE
WHEN sp.SalesQuota IS NULL THEN 0
ELSE sp.SalesQuota
END,
CASE
WHEN sp.SalesQuota IS NULL THEN ts.NetSales
ELSE ts.NetSales - sp.SalesQuota
END
FROM #totalSales AS ts
INNER JOIN Sales.SalesPerson AS sp ON ts.SalesPersonID = sp.BusinessEntityID
insert into #perUnit(Qty_Sales, SalesPersonID)
select NetSales/OrderQty, SalesPersonID
from #totalSales
SELECT sp.FirstName + ' ' + sp.LastName AS FullName ,
sp.City ,
ts.NetSales ,
ts.OrderQty ,
pu.Qty_Sales ,
td.SalesQuota ,
td.QuotaDiff
FROM Sales.vSalesPerson AS sp
INNER JOIN #totalSales AS ts ON sp.BusinessEntityID = ts.SalesPersonID
INNER JOIN #targetDiff AS td ON sp.BusinessEntityID = td.SalesPersonID
inner join #perUnit as pu on pu.SalesPersonID = ts.SalesPersonID
ORDER BY ts.NetSales DESC
You will see that with the temporary table, the CPU and Logical Reads are little higher than Table Variables, but the duration is 50% of the CTE/Derived Table and 57% of the Table Variable. The reason is in the parallel execution.
| Text Data | Duration | CPU | Logical Reads |
|---|---|---|---|
| CTE | 3956.8800000 | 8880 | 72194 |
| Derived Table | 3953.0510000 | 8421 | 72198 |
| Table Variable | 3579.8140000 | 3573 | 35972 |
| Temp Table | 2042.6220000 | 4993 | 37112 |
Examining the statistics IO below, you will see the scan counts are higher for the temp tables than the table variables, but still less than the CTE/Derived Table. The reads for our main tables are roughly the same, but slightly high. The additional reads come from populating our temporary tables with the indexes.
------------------- BEGIN temp table ------------------- Table 'SalesOrderHeaderEnlarged'. Scan count 4, logical reads 1356, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderDetailEnlarged'. Scan count 4, logical reads 34438, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#totalSales'. Scan count 0, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#targetDiff'. Scan count 0, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#totalSales'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#perUnit'. Scan count 0, logical reads 35, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#totalSales'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'PersonPhone'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'EmailAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Person'. Scan count 0, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Address'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'BusinessEntityAddress'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#perUnit'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesPerson'. Scan count 0, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#totalSales'. Scan count 17, logical reads 34, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#targetDiff'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. ------------------- end temp table -------------------
Review the execution plan below. The glaring difference is the use of parallelism couple with the ability to use the clustered indexes within the temporary tables.
Conclusion
As I have demonstrated through this series, reuse of code may make life a little easier during development, but may have adverse affects on the performance of the queries. In this post, we’ve seen that CTE are nothing more than a derived table(with the exception of the recursive ability). CTE may make coding a bit easier for that final join and the developer does not have to take the extra step to define the tables, but the ending result is not optimal.
Play around with your code to see what will work best for your situation. The end goal is the make the database do the least amount of work for the desired results. In my experience, temporary tables provide the best benefit over table variables and certainly over CTE.
More coming soon!
Nice article Bill!