I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH splits(ID, [NEW TEU]) AS (SELECT s.[DTL ID] AS ID, s.[NEW TEU] FROM [POTS].[vwSPLITCARGO] AS s) SELECT * FROM [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd LEFT JOIN splits AS s ON s.ID = cmd.ID
vwSPLITCARGO view, references the same
CARGO_MANIFEST_DETAIL_VIEW table to get it's calculations.
I know why it's slow. It's because the
vwSPLITCARGO view is reading all the data from the same
CARGO_MANIFEST_DETAIL_VIEW view for every record already in the CTE. In fact, the
vwSPLITCARGO is always returning the same small set of records (approx 4 or 6 records). I don't need it to get those records all the time. Once would be great!
Is there a way to make the CTE behave like a temporary table, in that it retrieves the records once, and not be processed over and over?
Something to tell the CTE:
CASE WHEN RunOnce = 0 THEN "do it" ELSE "don't do it" END