为什么CTE比游标/派生表/子查询/临时表等更好？

Question

如何以及为什么使用CTE相比派生表/子查询/临时表等方法能提供更好的性能？

任何临时计算都会发生在临时数据库中。因此，如果我们采用游标方法，它也会在临时数据库中创建临时表/工作表，并且一旦操作完成，该工作表就会被销毁。我对CTE的理解是它也是这样做的（或者它是在内存中创建临时结果的，因此可以提高性能）。那么，为什么它比游标/派生表/子查询/临时表等方法要好呢？

- mcUser

---

特定的例子？那是一个不准确的笼统陈述。 - Sam Saffron

总的来说，我正在寻找答案......没有具体的要求...... - mcUser

使用CTES并不能让查询比使用子查询更快或更慢，这并非神奇的事情。虽然CTES能够高效地完成递归等一些特殊应用场景，但这些场景是极为罕见的边缘情况。 - Sam Saffron

主要原因是：游标是一个过程性构造 - 你创建它，逐个步进。这对于通用编程来说很好用，但完全违背了 SQL Server 的集合思维和工作方式。游标在大多数情况下会消耗内存和性能，所以在使用时一定要非常小心。90%或更多的时间，你实际上不需要游标 - 你可以用基于集合的方法做得同样好（或更好！）。 - marc_s

@marc_s: 当然，在底层，基于集合的方法使用循环来处理结果集，但最大的区别在于这些是 SQL Server 的内部快速循环（请不要让任何人误解我在提倡使用游标。不要！） - Mitch Wheat

Answer 1

一个（非递归的）公用表达式（CTE）不使用游标。它是一种基于集合的方法，这是与使用游标相比的重要区别。但这也适用于一般不使用游标的情况。

游标应该尽可能避免使用（我相信我们都知道这一点）。

一个CTE不一定比使用派生表更好，但确实会导致更易理解的TSQL代码。CTE实际上只是查询或子查询的简写方式; 类似于临时视图。

当查询计划优化器对CTE的行估计得出不准确结果的情况下，CTE可能不是最佳选择。

相关问题：使用CTE有什么优缺点？

- Mitch Wheat

---

+1 光标就像魔鬼的作品一样，尽可能远离它们！特别是在触发器中……（我现在正在处理一个非常有问题的数据库……） - marc_s

是的，在触发器内部 - 并且在该游标内，另一个表被更新并触发了另一个触发器.....里面还有另一个游标.....这个数据库应该真的被复制到DEV:NULL中。 - marc_s

如果ACM或任何人需要一个真正可怕的数据库的例子，它做了所有被禁止和不被赞同的事情 - 我这里有一个很好的例子 :-) - marc_s

然后人们问“为什么这么慢？” :p。我从游标方法转换为8层互相嵌套的CTE（最终的UPDATE语句是第9层），将一个需要6小时才能完成的批处理任务优化到了4分钟！！！ - Vincent Vancalbergh

@Vincent - 天哪 - 9层CTE的可读性/可维护性有多高？除非你记录了每个层的职责，否则尝试进行更改的新人面临高风险失败的风险 - 就像试图理解第五维度一样...我赞扬你的速度提升，但是哇... - barrypicker

@barrypicker，可读性还可以（它总是会很复杂）。大多数层级是必要的，这样您就可以将值x定义为column1 * column2（实际上是一种时髦的case语句），然后在第二层中您可以在另一个计算中使用x而不重复（并破坏DRY原则）。 - Vincent Vancalbergh