I have about 30 queries across 50 tables in total which each pull or aggregate information to be later all joined together. The original code provided used entirely temp tables, but it is difficult to maintain. A common theme across the queries is the filter in the
WHERE clause which selects the records we want.
I tried to convert the temp tables into a series of chained CTE's and used the
WHERE clause as a filter CTE which is inner joined to all child CTE's so that when that filter is changed, it cascades to all other queries. Also, when new queries are added, they simply need to join to this CTE and the filter is applied. The idea is we start with the base filter table with only the IDs of the desired records, then left join to append the measures we want.
The problem is that this has a huge performance degradation compared to the temp tables. We've sacrificed performance for consistency, modularity, and ease of maintenance. Is there any way to tweak the performance in our favor, though? The biggest hits seem to happen on those that use windowing functions to concatenate strings across multiple rows into one row per record.
I've never attempted something of this nature before, and CTE's seemed like the logical approach. What was once a 4-5 minute query is now taking 30 minutes.
How it looks with temp tables:
select ... INTO #temp1 FROM ... WHERE <repeated filter> AND <temp1 filter> select ... INTO #temp2 FROM ... WHERE <repeated filter> AND <temp2 filter> SELECT ... FROM #temp1 join #temp2 WHERE <repeated filter>
And with the CTE's:
WITH Filter AS ( SELECT ... FROM ... WHERE ... ), Query1 AS ( SELECT ... FROM ... INNER JOIN Filter WHERE <query1 filter> ), Query2 AS ( SELECT ... FROM ... INNER JOIN Filter WHERE <query2 filter> ) SELECT ... FROM Filter LEFT JOIN Query1 LEFT JOIN Query2