CTE performance on execution plan. Is it displayed two times, or two times processed?
common-table-expression, query-optimization, sql-server, sql-server-2012
Solution
Am I right that CTE is not only displayed twice but processed twice?
Yes
Is it possible to inform QO to reuse CTE ?
Not directly but there are some hacks to encourage this.
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)?
In principle yes. See Microsoft Research Paper Efficient Exploitation of Similar Subexpressions for Query Processing for examples.
how to optimize the query (without using temporal tables :) ?
The most reliable way would be to use a temporary (not temporal) table. See Provide a hint to force intermediate materialization of CTEs or derived tables for a more hacky workaround.
Problem
This is the SQL with CommonTableExpression. Note, that USERS_PROJECTS_CTE used twice. ``` 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 ``` The Execution Plan. Note that CTE displayed twice: Questions: - 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)? - how to optimize the query (without using temporal tables :) ?