CTE benchmark

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement. SQL Server supports recursive and non-recursive CTE.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE can be used to: Create a recursive query.

CTE simplified query, and make query more readable. The compensation is a slightly slower then normal query in some cases.

I did the following benchmark on aggregated query on CTE.

If there's aggregation within a query and the aggregation is depending on the other aggregation fields, CTE execution is slightly slower than direct aggregation query.

The following query with CTE repeated 10000 times and execution spend 4:27.

DECLARE @cnt INT=0 

WHILE ( @cnt < 10000 ) 
  BEGIN 
      IF Object_id('tempdb..#e') IS NOT NULL 
        DROP TABLE #e; 

      WITH cte ([year], [month], brand_categ, numerator, total_app_hits) 
      AS (
               SELECT Year([aplctn_start_dt])  [year], 
                      Month([aplctn_start_dt]) [month], 
                      brand_categ, 
                      Sum(CASE [aplctn_self_serve_cd] WHEN 1 THEN csid END) Numerator, 
                      Sum(csid) Total_app_hits 
               FROM   [IVR].[tbl_app_level_scorecard_v5] 
               WHERE  Month([aplctn_start_dt]) = 10 AND trsfr_ind = 0 
               GROUP  BY rollup( Year([aplctn_start_dt]), Month([aplctn_start_dt]),brand_categ )
      ) 
      SELECT [year], 
             [month], 
             brand_categ, 
             numerator, 
             total_app_hits, 
             numerator / total_app_hits * 100 [% self-serve] 
      INTO   #e 
      FROM   cte 

      SET @cnt+=1 
  END

The following query without CTE repeated 10000 times and execution spend 4:22.

DECLARE @cnt INT=0 

WHILE ( @cnt < 10000 ) 
  BEGIN 
      IF Object_id('tempdb..#e') IS NOT NULL DROP TABLE #e; 

      SELECT Year([aplctn_start_dt])    year, 
             Month([aplctn_start_dt])   month, 
             brand_categ, 
             Sum(CASE [aplctn_self_serve_cd] WHEN 1 THEN csid END) Numerator, 
             Sum(csid) [total app hits], 
             Sum(CASE [aplctn_self_serve_cd] WHEN 1 THEN csid END) / Sum(csid) * 100 [% self-serve] 
      INTO   #e 
      FROM   [IVR].[tbl_app_level_scorecard_v5] 
      WHERE  Month([aplctn_start_dt]) = 10 AND trsfr_ind = 0 
      GROUP  BY rollup( Year([aplctn_start_dt]), Month([aplctn_start_dt]),brand_categ ) 

      SET @cnt+=1 
  END

In the above case, with CTE query is 5 seconds slower then normal query.

2,179 total views, 4 views today

Author: IT Team