4

I'm working on this huge query that I chose to use CTEs. Due to the complexity of this query, I ended up having to reuse intermediary query results more than once in subsequent queries. The problem I'm facing can be summarized by the following toy code:

WITH cte1 AS (
    SELECT id, name
    FROM Manager)
, cte2 AS (
    SELECT id, name
    FROM Employee 
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte1

I have notice an unusual amount of indexes seeks in a fairly small table that was being queried in one the first queries which, according to the execution plan, were taking about 30% of the whole query. This led to me believe the base table was being queried multiple times as it was being referenced more than once in subsequent queries.

I was under the impression that intermediary results in a CTE were cached until the final statement. Reading Microsofts docs on CTEs, I couldn't find any reason to think otherwise.

Are intermediary CTEs cached for muliple uses within an WITH statement? If not, is there a way to cache it without rewriting the query?

Thanks!

  • 5
    No there is no way to cache a CTE, it may be executed multiple times if it is referenced multiple times. Use a #temp table instead. – Aaron Bertrand Nov 5 '18 at 10:51
  • @AaronBertrand, oh, bummer. I was hoping to keep everything within one single WITH statement. But it seems it wouldn't be best choice. Anyway, thanks for the answer and the suggestion. – Luís Gabriel de Andrade Nov 5 '18 at 11:08
  • @jean, I'm not using recursive CTEs here. Some CTEs are being referenced more than once but none of them are recursive. – Luís Gabriel de Andrade Nov 5 '18 at 11:11
  • @AaronBertrand, as a follow-up, when you say "it may be executed multiple times if it is referenced multiple times" do you mean that there are some instances where SQL Server will cache the queried results? If so, could you give me an example of one of these instances? Thanks! – Luís Gabriel de Andrade Nov 5 '18 at 11:36
  • I don't think I suggested it would ever be cached. What I meant was that in some cases the execution plan may be simple enough that the CTE is only materialized once. I don't have any examples at hand and, even if I did, you couldn't rely on that to always be the case. – Aaron Bertrand Nov 5 '18 at 12:01
8

No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead.

In some cases, the execution plan may be simple enough that the CTE is only materialized once (this doesn't technically mean it was "cached"), or for the results to be reused with a spool. I don't have any examples at hand and, even if I did, you couldn't rely on that to always be the case. Even something as simple as this:

CREATE TABLE dbo.OneTuple(id int PRIMARY KEY);
INSERT dbo.OneTuple(id) VALUES(1);
GO

;WITH cte AS 
(
  SELECT id FROM dbo.OneTuple
)
SELECT id FROM cte
UNION ALL
SELECT id FROM cte;

Yields two identical index scans:

enter image description here

Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.

3

CTEs are just inline views, which, aside from the functionality provided by recursive CTEs, are nothing more than a syntactic convenience.

If you need to cache the results of a CTE for multiple subsequent uses, then the best option is to break up the query and load the CTE query's results into a temp table, index it as needed, and then use the temp table in the later queries.

2

is there a way to cache it without rewriting the query?

To cache (or spool) intermediate results, minimally you would change:

WITH cte1 AS (
    SELECT id, name
    FROM Manager)
, cte2 AS (
    SELECT id, name
    FROM Employee 
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte2

to

SELECT id, name
into #cte1
FROM Manager;

WITH cte1 AS (
  select * from #cte1
)
, cte2 AS (
    SELECT id, name
    FROM Employee 
    LEFT OUTER JOIN cte1 ON Employee.id = cte1.id)
SELECT *
FROM cte2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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