T-SQL CTE 的性能特征
我有一些 SQL，看起来大致如下：
with InterestingObjects(ObjectID, OtherInformation, Whatever) as (
select X.ObjectID, Y.OtherInformation, Z.Whatever
from X join Y join Z -- abbreviated for brevity
)
-- ...long query follows, which uses InterestingObjects in several more CTEs,
-- and then uses those CTEs in a select statement at the end.
当我运行它时，我可以在执行计划中看到，基本上每次引用 CTE 时，它似乎都在 CTE 中运行查询。如果我创建一个临时表 #InterestingObjects 并使用它，当然，它会运行一次查询，将结果放入临时表中，然后再进行查询。在我的特定实例中，这使得整个过程运行得更快。
我的问题是：这是否总是我对 CTE 的期望（不以任何方式记忆结果，就像它在各处内联查询一样？）是否有 SQL Server 无法更好地优化这一点的原因？通常我对优化器的智能感到敬畏，但令我惊讶的是它无法解决这个问题。
（编辑：顺便说一句，我在 SQL Server '08 R2 上运行它。）
I've got some SQL that looks roughly like this:
with InterestingObjects(ObjectID, OtherInformation, Whatever) as (
select X.ObjectID, Y.OtherInformation, Z.Whatever
from X join Y join Z -- abbreviated for brevity
)
-- ...long query follows, which uses InterestingObjects in several more CTEs,
-- and then uses those CTEs in a select statement at the end.
When I run it, I can see in the execution plan that it appears to be running the query in the CTE basically every single time the CTE is referenced. If I instead create a temp table #InterestingObjects and use it, of course, it runs the query once, puts the result in the temp table, and queries that from then on. In my particular instance, that makes the whole thing run much faster.
My question is: Is this always what I can expect from CTEs (not memoizing the results in any way, just as if it were inlining the query everywhere?) Is there a reason that SQL Server could not optimize this better? Usually I am in awe at how smart the optimizer is, but I'm surprised that it couldn't figure this out.
(edit: BTW, I'm running this on SQL Server '08 R2.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
CTE 可能更好也可能更差，仅取决于它们的使用方式（涉及递归、索引等概念）。您可能会发现这篇文章很有趣： http://www.sqlservercentral.com/articles/ T-SQL/2926/
CTE's can be better or worse, just depending on how they're used (involving concepts of recursion, indexing, etc.). You might find this article interesting: http://www.sqlservercentral.com/articles/T-SQL/2926/