A way to process a CTE once? 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

The 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 &quot;do it&quot; ELSE &quot;don't do it&quot; END
There is no query hint to spool or materialize a CTE.  So use a temp table, and optionally index the temp table.
SELECT s.[DTL ID] AS ID, s.[NEW TEU]
into #splits
FROM   [POTS].[vwSPLITCARGO] AS s;

WITH
  splits(ID, [NEW TEU])
  AS
    (select * from #splits)
     
SELECT *
FROM   [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd
       LEFT JOIN splits AS s ON s.ID = cmd.ID