Microsoft Book online (BOL) says that “Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.” What it means is that once a CTE is defined it can be used multiple times in the same query.
A CTE is transient in nature and once its execution is completed it vanishes on its own. And this is where the performance issue occurs while referencing multiple times for large tables which we often fail to notice.
Is CTE always preferable over Temporary Table? If a CTE is being called multiple times in a same query then using temporary table/Table variable is much preferable over CTE. However, do not forget that the "Table Variable" has cardinality estimate issues which might incur poor performance.
Pre-requisite to perform the test:
Let’s consider the following SQL Code against “AdventureWork2012” database. This code is for demonstration purposes and simulating the behavior of using three different techniques to understand IO and cardinality estimate.
1. We can download the latest “AdventureWork2012”database from the following site.
2. We have created a covering index as follows:
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_OrderQty
ON [Sales].[SalesOrderDetail] ([OrderQty])
INCLUDE ([ProductID],[UnitPrice],[LineTotal])
3. Before performing each test we will clean up the data buffers:
DBCC DROPCLEANBUFFERS
4. In each query we use “SET STATISTICS IO ON”
5. We will collect “Actual Execution Plan”, use CTRL + M in SSMS
Seeing the IO:
In our sample code (given below), we will be using a CTE, a Temporary Table, a Table Variable and a Derived Table (In-line table) to perform the same task and then combined each SELECT statement block with “UNION ALL”. We will be focusing on the table “SalesOrderDetail” for an IO that is occurring.
Findings:
CTE and In-line Table:
1. “SalesOrderDetail” was accessed three times (Scan count 3, logical reads 15, physical reads 3).
2. Cardinality estimate was accurate.
Temporary Table and Table variable:
1. “SalesOrderDetail” was accessed only once (Scan count 1, logical reads 5, physical reads 3
) while populating data.
2. In case of Table variable cardinality estimate was wrong, but for Temporary Table cardinality is correct. However, “OPTION (RECOMPILE)” can be used to correct the cardinality estimate error.
Conclusion: If we need to access a same set of records multiple times, then it is good idea to use a Temporary Table which will eventually reduce lock requirements on a heavily used table and save some unnecessary IO. But on the other hand we should have TempDB “sliced and diced” based on workload.
Sample Code Using CTE:
/*****************************************************
* Using a CTE (common table expresssion)
******************************************************/
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
;
WITH myCTE
AS ( SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
)
-- use the cte to join other tables
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( c.LineTotal - c.TotalDiscount ) AS EstimatedLineCost
FROM myCTE c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.ProductNumber = 'GL-F110-L'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - 5.0 ) AS EstimatedLineCost
FROM myCTE c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Color = 'Black'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - c.LineTotal * 0.01 ) AS EstimatedLineCost
FROM myCTE c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Class = 'L'
Sample Code Using Derived Table:
/*****************************************************
* Using a CTE (common table expresssion)
******************************************************/
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
-- use the derived table to join other tables
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( c.LineTotal - c.TotalDiscount ) AS EstimatedLineCost
FROM ( SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
) c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.ProductNumber = 'GL-F110-L'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - 5.0 ) AS EstimatedLineCost
FROM ( SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
) c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Color = 'Black'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - c.LineTotal * 0.01 ) AS EstimatedLineCost
FROM ( SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
) c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Class = 'L'
Sample Code Using Temporary Table:
/*****************************************************
* Using a temporary table
******************************************************/
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
;
IF OBJECT_ID('tempdb..#Temptbl') IS NOT NULL
DROP TABLE #Temptbl
GO
CREATE TABLE [#Temptbl]
(
[ProductID] [int] NOT NULL ,
[OrderQty] [smallint] NOT NULL ,
[LineTotal] [numeric](38, 6) NOT NULL ,
[TotalDiscount] [numeric](22, 6) NULL
)
INSERT INTO #Temptbl
( ProductID ,
OrderQty ,
LineTotal ,
TotalDiscount
)
SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
-- Use the temporary table to join other tables
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( c.LineTotal - c.TotalDiscount ) AS EstimatedLineCost
FROM #TempTbl c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.ProductNumber = 'GL-F110-L'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - 5.0 ) AS EstimatedLineCost
FROM #TempTbl c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Color = 'Black'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - c.LineTotal * 0.01 ) AS EstimatedLineCost
FROM #TempTbl c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Class = 'L'
Sample Code Using Table Varable:
/*****************************************************
* Using a table varable
******************************************************/
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
DECLARE @Tempvar TABLE
(
[ProductID] [int] NOT NULL ,
[OrderQty] [smallint] NOT NULL ,
[LineTotal] [numeric](38, 6) NOT NULL ,
[TotalDiscount] [numeric](22, 6) NULL
)
INSERT INTO @Tempvar
( ProductID ,
OrderQty ,
LineTotal ,
TotalDiscount
)
SELECT ProductID ,
OrderQty ,
LineTotal ,
( OrderQty * UnitPrice * 0.05 ) AS TotalDiscount
FROM sales.SalesOrderDetail
WHERE OrderQty >= 30
-- Use the table variable to join other tables
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( c.LineTotal - c.TotalDiscount ) AS EstimatedLineCost
FROM @Tempvar c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.ProductNumber = 'GL-F110-L'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - 5.0 ) AS EstimatedLineCost
FROM @Tempvar c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Color = 'Black'
UNION ALL
SELECT b.ProductID ,
b.Name ,
b.ProductNumber ,
b.Color ,
b.Class ,
c.OrderQty ,
c.LineTotal ,
c.TotalDiscount ,
( ( c.LineTotal - c.TotalDiscount ) - c.LineTotal * 0.01 ) AS EstimatedLineCost
FROM @Tempvar c
JOIN Production.Product b ON c.ProductID = b.ProductID
WHERE b.Class = 'L'
-- OPTION (RECOMPILE)
No comments:
Post a Comment