parse problem with unused "tables" in big CTE (common table expressions)
I have a rather big CTE with various entries ... (see attached file).
In short ... the distance between the definition of a CTE part and it's usage influences the 2performance" ...
In long ...
It seems that the SQL parser gets hiccups when either ...
- the query got too big
- "unusual" syntax is used ...
- the "distance" between usage and definition gets too big (which is my strongest assumption based on results so far - see also below)
In the attached SQLs I can query the results of the "MeasureBasicData" without problems (less than a second) up to before "PivotedCategoryValues".
But when I place the "select * from MeasureBasicData" after the closing paranthesis of "PivotedCategoryValues" (just before "CostSheetData"), the (very first) run of the query lasted over 30 seconds (subsequent runs had the expected runtime of less than a second).