Total Pageviews

Monday, February 18, 2013

CTE – What happens when referencing it multiple times?

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