0

I have a query that has something like this: 

;with Dataset1 as (SELECT columnA, columnB, SUM(columnC) as columnC  FROM Table1 GROUP BY columnA, columnB )

SELECT
    (SELECT columnC FROM Dataset1 WHERE columnA = '1' and columnB='2') as Result1,
    (SELECT columnC FROM Dataset1 WHERE columnA = '2' and columnB='3') as Result2,
    (SELECT columnC FROM Dataset1 WHERE columnA = '3' and columnB='4') as Result3,
...

What I'm wondering is does Dataset1 get selected in-memory and reused with every one of those select clauses, or does SQL fetch it each time? If that's the case, that seems really wasteful, and what should I do to optimize this ?

Patrick Schomburg
  • 2,494
  • 1
  • 18
  • 46
  • Get the execution plan https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan my guess only need calculate it once. but you can test if the plan with one Result is the same as the plan with two Results. – Juan Carlos Oropeza Mar 15 '18 at 15:48
  • 1
    IIRC (hence just a comment), the answer when they first introduced the feature was, "No"... results are re-computed for each reference. This was partly to ensure recursive CTEs will work properly, but also because it's not always clear that re-using the CTE result set really is a win. In many cases, the extra memory use of keeping the separate result set materialized can be worse for performance then re-calculating, in addition to adding query engine complexity. But (again: IIRC) adding re-use in known-safe cases was on the roadmap. – Joel Coehoorn Mar 15 '18 at 15:48
  • 1
    Also: that CTE example is not legal for at least two reason: **_1)_** No `FROM` clause for those columns. **_2)_** `columnC` would not be legal in that context, because it's not grouped and not aggregated. – Joel Coehoorn Mar 15 '18 at 15:53
  • @JuanCarlosOropeza I'm looking at the execution plan but I'm not entirely sure how to read it. There's a nested loop that's repeated for each select clause that leads me to believe it is recalculating – Patrick Schomburg Mar 15 '18 at 15:54
  • @JoelCoehoorn good call, I was trying to make my example as simple as possible and left some things out. – Patrick Schomburg Mar 15 '18 at 15:55
  • @JoelCoehoorn Assume it is re-calculating, I would like to at least try an alternative and see if it performs better - my actual query has about 25 select statements and it drags quite a bit. What are my options? – Patrick Schomburg Mar 15 '18 at 15:56
  • Possibly conditional aggregation (`SUM(CASE ... END)`) repeated for each final result column instead of nested SELECTs would be faster here – Joel Coehoorn Mar 15 '18 at 15:58
  • You can add your plan [using paste the plan](https://www.brentozar.com/pastetheplan/). – S3S Mar 15 '18 at 18:50

0 Answers0