Mixing
What Rules Determine When SQL Server Use a CTE as an “Optimization Fence�
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
7
down vote
favorite
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:
Two Important Differences Between SQL Server and PostgreSQL
The first point (“CTEs are optimization fencesâ€Â) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).
However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc. For example:
A Better Way To Select Star
So, it seems like SQL Server “honors†the CTE as an optimization fence SOMETIMES. Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?
sql-server query-performance optimization cte
edited 1 hour ago
sp_BlitzErik
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
add a comment |Â
up vote
7
down vote
favorite
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:
Two Important Differences Between SQL Server and PostgreSQL
The first point (“CTEs are optimization fencesâ€Â) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).
However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc. For example:
A Better Way To Select Star
So, it seems like SQL Server “honors†the CTE as an optimization fence SOMETIMES. Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?
sql-server query-performance optimization cte
edited 1 hour ago
sp_BlitzErik
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago
add a comment |Â
up vote
7
down vote
favorite
up vote
7
down vote
favorite
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:
Two Important Differences Between SQL Server and PostgreSQL
The first point (“CTEs are optimization fencesâ€Â) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).
However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc. For example:
A Better Way To Select Star
So, it seems like SQL Server “honors†the CTE as an optimization fence SOMETIMES. Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?
sql-server query-performance optimization cte
edited 1 hour ago
sp_BlitzErik
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL:
Two Important Differences Between SQL Server and PostgreSQL
The first point (“CTEs are optimization fencesâ€Â) caught my eye, because it is obvious that in the example provided, SQL Server combines the CTE and the main query together and optimizes it as a single query (as opposed to the opposite behavior in PostgreSQL).
However, this behavior seems contrary to the examples that I have seen in other blogs and training classes, where SQL Server does treat the CTE as an optimization fence, which allows for better use of indexes, better performance, etc. For example:
A Better Way To Select Star
So, it seems like SQL Server “honors†the CTE as an optimization fence SOMETIMES. Are there any good resources available that document the specific list of known cases where SQL Server will reliably honor the CTE as an optimization fence (or the opposite behavior)?
sql-server query-performance optimization cte
sql-server query-performance optimization cte
edited 1 hour ago
sp_BlitzErik
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
edited 1 hour ago
sp_BlitzErik
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
edited 1 hour ago
sp_BlitzErik
20.4k1162103
edited 1 hour ago
sp_BlitzErik
20.4k1162103
edited 1 hour ago
sp_BlitzErik
20.4k1162103
20.4k1162103
asked 1 hour ago
Bryan Rebok
68929
asked 1 hour ago
Bryan Rebok
68929
asked 1 hour ago
Bryan Rebok
68929
68929
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago
add a comment |Â
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
6
down vote
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.
CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.
Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.
Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:
- The optimizer should considering materialising results of CTEs
- Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.
answered 1 hour ago
Paul White♦
48k14257407
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
6
down vote
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.
CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.
Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.
Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:
- The optimizer should considering materialising results of CTEs
- Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.
answered 1 hour ago
Paul White♦
48k14257407
add a comment |Â
up vote
6
down vote
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.
CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.
Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.
Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:
- The optimizer should considering materialising results of CTEs
- Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.
answered 1 hour ago
Paul White♦
48k14257407
add a comment |Â
up vote
6
down vote
up vote
6
down vote
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.
CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.
Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.
Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:
- The optimizer should considering materialising results of CTEs
- Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.
answered 1 hour ago
Paul White♦
48k14257407
...list of known cases where SQL Server will reliably honor the CTE as an optimization fence
Any such list would rely on observed behaviour, with no guarantee of reliability.
The SQL Server query optimizer never treats a common table expression as an optimization fence per se, though some constructions are clearly difficult to optimize across. Recursive CTEs are a good example of this.
CTEs are treated very similarly to views/inline functions/subqueries/derived tables and inlined into the query. Any observed 'fence' behaviour depends on the optimizer either not being able to, or deciding not to, optimize across that in-principle permeable border.
Generally speaking, the simpler and more 'relational' the CTE is, the more likely it is that the optimizer will be able to move bits around.
Features that would allow the optimizer to consider, or force it to materialize the 'result' of a CTE have been suggested, but not yet implemented:
- The optimizer should considering materialising results of CTEs
- Additional Query Hint: OPTION (MATERIALIZE (cte_name,...,n)
In the meantime, the most common workaround is to explicitly materialize the intermediate result set in a temporary table or table variable. This obviously requires a scenario not limited to a single statement.
answered 1 hour ago
Paul White♦
48k14257407
edited 20 mins ago
answered 1 hour ago
Paul White♦
48k14257407
answered 1 hour ago
Paul White♦
48k14257407
answered 1 hour ago
Paul White♦
48k14257407
48k14257407
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%2f221555%2fwhat-rules-determine-when-sql-server-use-a-cte-as-an-optimization-fence%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
"list of x" questions are generally off-topic, but I have done my best to answer within the scope of this site.
– Paul White♦
1 hour ago