我有一个复杂的查询需要在随后的查询中使用(实际上是更新语句)。我尝试过使用公用表表达式(CTE)和临时表(Temp table)。但是，使用CTE的性能非常差，而使用临时表的方法则非常快，大约需要15秒以及毫秒级别的时间。为了简化测试，我不是在随后的查询中连接CTE/Temp表，而是从中选择*。 在这种情况下，它们的执行效果相同。
我查看了两种方法的执行计划，一种是在随后的查询中包含连接，另一种是仅选择*。在仅选择*时，查询计划大致相同，但在随后查询中包含连接时，查询计划不同。具体来说，创建和填充临时表的查询计划部分保持不变，而将CTE用于带有连接的查询时，创建和填充CTE的查询计划部分发生了巨大变化。
我的问题是：为什么CTE的创建和填充查询计划会因其随后的使用方式而改变，而临时表则不会？此外，在哪些场景下，CTE会比临时表产生更好的性能？
*注意：我还使用过表变量，它与临时表的方法相比较。
谢谢
CTE是查询的别名。
每次使用时，它可能会（也可能不会）重新运行。
在SQL Server中，没有清洁的方法可以强制要求CTE材料化（例如Oracle的/*+ MATERIALIZE */），因此您必须像这样使用不正当手段:
如果在只需要一次评估的计划中使用（如HASH JOIN、MERGE JOIN等），CTE可以提高性能。
在这些场景中，哈希表将直接从CTE构建，而使用临时表将需要评估CTE，将结果拉入临时表，再次读取临时表。
我发现通常情况下，重复使用公用表表达式(CTE)不会提高性能。
例如，如果您使用CTE来填充一个表，然后在稍后的查询中使用相同的CTE进行连接操作，那么没有任何好处。不幸的是，CTE不是快照，必须重复使用才能在两个不同的语句中使用，因此它们 tend to被评估两次。
与之不同的是，我经常使用内联TVF(可能包含CTE)，这允许适当的重用，并且在我的SPs中与CTE一样好或不好。
此外，我还发现，如果第一步改变了统计信息以至于第二步的执行计划总是不准确，那么执行计划可能会出现问题，因为它在运行任何步骤之前就被计算了。
在这种情况下，我会手动存储中间结果，确保它们被正确索引，并将过程分成多个SP，并添加WITH RECOMPILE以确保后面的SP有适合它们实际要操作的数据的计划。
我尝试使用从大表筛选的简单选择创建CTE，然后对其进行了3次子查询。
然后再使用临时表重复相同的操作。
结果是，CTE比临时表耗时70％，临时表耗时30％。因此，临时表更适合这些解决方案。
我认为CTE不仅仅使用选定的查询创建临时表，而是对大表进行三次查询。
