-2

I am trying to find out how MS CTEs actually access data. Trying to find out if CTEs will actually run slower when pulling data and what is the order that a CTE will pull data. Does a CTE pull the data when a view is called or does it happen in the last select statement when creating the result set? How often does the CTE have to make a database call for additional data?

2 Answers 2

6

Main Difference

The main difference between Common Table Expressions and temporary objects is that CTEs do not materialize a result set, and every time you reference them the inner query needs to be re-executed. I'm not bringing @table variables into the picture because it introduces additional performance complexities.

Common Table Expression Problems

I'm using this demo as a portable example. There are no performance issues here because it's a one row table, but it's sufficient to get the point across.

CREATE TABLE
    #t
(
    id int NOT NULL PRIMARY KEY
);

INSERT
    #t
(
    id
)
SELECT
    id = 1;

We have a one-row table, and now let's hit it with a CTE:

WITH 
    t AS
(
    SELECT
        t.id
    FROM #t AS t
)
SELECT
    t.*
FROM t
JOIN t AS t2
    ON t2.id = t.id
JOIN t AS t3
    ON t3.id = t.id;

The resulting query plan looks like this:

NUTS

For each join to the Common Table Expression, we end up hitting the base table. If you have CTEs that are referenced multiple times, you're usually better off using a #temp table to materialize the result. In Common Table Expressions with more complex queries in them, this can be especially painful.

Row Goals

While row goals do not materialize Common Table Expression results, they do (at least up to the current version of SQL Server) offer an optimization fence around the query inside of them.

As an example of how this can work, take a look at these two queries:

SELECT
    c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON p.OwnerUserId = u.AccountId
JOIN dbo.Comments AS c
    ON c.PostId = p.ParentId
JOIN dbo.Votes AS v
    ON v.PostId = c.PostId
WHERE c.Score > 1000;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
    ON p.OwnerUserId = u.AccountId
JOIN 
(
    SELECT TOP (2147483647)
        c.*
    FROM dbo.Comments AS c
    JOIN dbo.Votes AS v
        ON v.PostId = c.PostId
    WHERE c.Score > 1000
) AS c ON c.PostId = p.ParentId;

Here are the query plans for them:

First query: NUTS

Second query: NUTS

The join order is altered because SQL Server has to honor the row goal set by the TOP operator. There's no real performance difference here, but you get the idea. If there's a particular thing you want to happen together, this can be one way to achieve that without a temporary object, but sans the materialization.

Managing Complexity

For an example of tuning a query that misused Common Table expressions by using #temp tables instead, check out this post of mine:

There's a video over there that I can't embed here that walks through identifying things like poor cardinality estimates coming out of Common Table Expressions, and using each one as a stopping point to materialize results.

2

CTEs, Views, Subqueries, and Inline Table-Valued Functions don't specify the order of processing. The query optimizer operates on the complete query as submitted by the client, and optimizes it as a whole. The optimization may result in a query plan that performs the logic in a different order than the logical order specified by any CTEs.

If you want to force the processing order you can break up the CTE pipeline by using some initial CTEs to load a temp table, possibly with indexes, and reference that in the subsequent CTEs.

4
  • Thank you for that information. I have a developer that over uses CTEs , uses 5-8 CTEs in a single query and has CTEs that reference CTEs and then a final select. I was trying to find documentation on when you should use a CTE and when not to.
    – Dave
    May 26, 2022 at 12:23
  • 5-8 CTEs is usually better than 5-8 temp tables. May 26, 2022 at 13:44
  • Is there a limit or guide to how many CTEs you can use before the Query starts to have response issues?
    – Dave
    May 26, 2022 at 16:31
  • Not a fixed limit, but you should evaluate query performance and consider using temp tables, indexed views, or pre-loading regular tables to prevent a query from becoming too complex. At some point the query optimizer has too many possible plans to search. May 26, 2022 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.