sql-server - 执行计划上的 CTE 性能。是显示两次，还是处理了两次？

Question

这是带有 CommonTableExpression 的 SQL。请注意， USERS_PROJECTS_CTE 使用了两次。

 WITH USERS_PROJECTS_CTE (PRO_ID, SHOW_IAS, USERNAME)
   AS
   (
      SELECT up.PRO_ID, up.SHOW_IAS, ISNULL(u.FIRST_NAME, '') + ' ' + ISNULL(u.SECOND_NAME, '')
      FROM SFMIS07_PRO.USERS_PROJECTS up
      INNER JOIN SFMIS07_ADM.USERS  AS u 
                ON  up.USER_ID = u.ID
      WHERE up.IS_RESP_PERSON = 1 AND up.valid_to is null
   )
   SELECT up.PRO_ID, 
          up1.USERNAME as RESP_USER1,
          up2.USERNAME as RESP_USER2,
          up.COUNT_
   FROM SFMIS07_PRO.PRO_RESP_USERS_KERNEL_MV  AS up 
        LEFT JOIN USERS_PROJECTS_CTE  AS up1 ON up.PRO_ID = up1.PRO_ID AND up1.SHOW_IAS=1  
        LEFT JOIN USERS_PROJECTS_CTE  AS up2 ON up.PRO_ID = up2.PRO_ID AND up2.SHOW_IAS=0  

执行计划。请注意，CTE 显示了两次:


问题:

我对 CTE 不仅显示两次而且处理两次是否正确？

是否可以通知 QO 重用 CTE？

QO 原则上是否有可能检测“相同的 SQL 片段”并重用结果(我想象这个实现 - 通过处理已经准备好的数据)？

如何优化查询(不使用时态表:)？

Answer 1

Am I right that CTE is not only displayed twice but processed twice?

是的

Is it possible to inform QO to reuse CTE ?

不是直接的，但有一些技巧可以鼓励这样做。

is it possible for QO in principle to detect "the same SQL fragment" and reuse results (I imagine the realization of this - by coping already prepared data)?

原则上是的。参见微软研究论文 Efficient Exploitation of Similar Subexpressions for QueryProcessing举些例子。

how to optimize the query (without using temporal tables :) ?

最可靠的方法是使用临时(非临时)表。见 Provide a hint to force intermediate materialization of CTEs or derived tables一个更hacky的解决方法。