Mixing
Caching intermediary CTEs for multiple uses
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
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!
sql-server cte
asked 2 hours ago
LuÃs Gabriel de Andrade
706
add a comment |Â
up vote
1
down vote
favorite
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!
sql-server cte
asked 2 hours ago
LuÃs Gabriel de Andrade
706
4
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♦
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
1 hour ago
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♦
1 hour ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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!
sql-server cte
asked 2 hours ago
LuÃs Gabriel de Andrade
706
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!
sql-server cte
sql-server cte
asked 2 hours ago
LuÃs Gabriel de Andrade
706
asked 2 hours ago
LuÃs Gabriel de Andrade
706
asked 2 hours ago
LuÃs Gabriel de Andrade
706
asked 2 hours ago
LuÃs Gabriel de Andrade
706
asked 2 hours ago
LuÃs Gabriel de Andrade
706
706
4
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♦
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
1 hour ago
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♦
1 hour ago
add a comment |Â
4
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♦
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
1 hour ago
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♦
1 hour ago
4
4
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♦
2 hours ago
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♦
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
1 hour ago
@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
1 hour ago
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♦
1 hour ago
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♦
1 hour ago
add a comment |Â
2 Answers
2
active
oldest
votes
up vote
3
down vote
accepted
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:
Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.
answered 50 mins ago
Aaron Bertrand♦
147k18279475
add a comment |Â
up vote
1
down vote
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.
answered 13 mins ago
db2
7,85812347
add a comment |Â
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
3
down vote
accepted
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:
Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.
answered 50 mins ago
Aaron Bertrand♦
147k18279475
add a comment |Â
up vote
3
down vote
accepted
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:
Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.
answered 50 mins ago
Aaron Bertrand♦
147k18279475
add a comment |Â
up vote
3
down vote
accepted
up vote
3
down vote
accepted
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:
Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.
answered 50 mins ago
Aaron Bertrand♦
147k18279475
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:
Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.
answered 50 mins ago
Aaron Bertrand♦
147k18279475
edited 19 mins ago
answered 50 mins ago
Aaron Bertrand♦
147k18279475
answered 50 mins ago
Aaron Bertrand♦
147k18279475
answered 50 mins ago
Aaron Bertrand♦
147k18279475
147k18279475
add a comment |Â
add a comment |Â
up vote
1
down vote
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.
answered 13 mins ago
db2
7,85812347
add a comment |Â
up vote
1
down vote
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.
answered 13 mins ago
db2
7,85812347
add a comment |Â
up vote
1
down vote
up vote
1
down vote
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.
answered 13 mins ago
db2
7,85812347
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.
answered 13 mins ago
db2
7,85812347
answered 13 mins ago
db2
7,85812347
answered 13 mins ago
db2
7,85812347
answered 13 mins ago
db2
7,85812347
7,85812347
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221791%2fcaching-intermediary-ctes-for-multiple-uses%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
4
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♦
2 hours ago
@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
2 hours ago
@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
2 hours ago
@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
1 hour ago
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♦
1 hour ago