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
FROM #temp1 JOIN #temp2
really equivalent toFROM Filter LEFT JOIN Query1 LEFT JOIN Query2
, logically? Shouldn't the latter beFROM Filter JOIN Query1 JOIN Query2
? Or, alternatively, shouldn't the former perhaps beFROM #temp1 FULL JOIN #temp2
?