自连接中引用的SQL Server CTE速度慢

我编写了一个表值的UDF,它以CTE开头,返回一个大表中的行的子集。 CTE中有几个连接。两个内部表和一个左表联接到其他表,这些表不包含太多行。 CTE有一个WHERE子句,它返回日期范围内的行,以便只返回所需的行。

然后,我在4个自左联接中引用这个CTE,以便使用不同的标准构建分类汇总。

查询非常复杂,但这里有一个简化的伪版本

WITH DataCTE as
(
     SELECT [columns] FROM table
                      INNER JOIN table2
                      ON [...]

                      INNER JOIN table3
                      ON [...]

                      LEFT JOIN table3
                      ON [...]
)
SELECT [aggregates_columns of each subset] FROM DataCTE Main
LEFT JOIN DataCTE BananasSubset
               ON [...] 
             AND Product = 'Bananas'
             AND Quality = 100
LEFT JOIN DataCTE DamagedBananasSubset
               ON [...]
             AND Product = 'Bananas'
             AND Quality < 20
LEFT JOIN DataCTE MangosSubset
               ON [...]
GROUP BY [

我有种感觉,SQL Server会感到困惑,并为每个自连接调用CTE,这似乎通过查看执行计划得到了证实,尽管我承认自己不是阅读这些内容的专家。

我假设SQL Server足够智能,只从CTE执行一次数据检索,而不是多次执行。

我尝试过同样的方法,但我没有使用CTE来获取数据的子集,而是使用了与CTE中相同的选择查询,而是将其输出到临时表。

引用CTE版本的版本需要40秒。引用临时表的版本需要1到2秒。

为什么SQL Server不够智能,无法将CTE结果保存在内存中?

我喜欢CTE,尤其是在这种情况下,因为我的UDF是表值的,所以它允许我将所有内容保存在一个语句中。

要使用临时表,我需要编写一个多语句表值的UDF,这是一个稍微不那么优雅的解决方案。

你们中的一些人对CTE有这种性能问题吗?如果有,你是如何将它们分类的?

谢谢,

哈洛斯