0

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 "do it" ELSE "don't do it" END

4
  • 3
    If you want it to behave like a temp table, why not just use a temp table instead of a cte? – AMtwo Jul 1 '20 at 3:31
  • @AMtwo not that straight forward because the vwSPLITCARGO view could bring up other records, so in essence it's "temporary" only for the processing of the above view - at the time. If you know what I mean? It would be good to have a way to refresh/reinsert a table in SQL server automatically based on a view change? – Fandango68 Jul 1 '20 at 4:03
  • sorry, but your explanation is too messy and it's hard to understand. Could you please add both view definitions (vwSPLITCARGO and CARGO_MANIFEST_DETAIL_VIEW) and also paste the actual execution plan - brentozar.com/pastetheplan – NikitaSerbskiy Jul 1 '20 at 9:45
  • You should try to rewrite your query without CTE. Provide both view script and an example of the content of the table if you need help to rewrite it. – Dominique Boucher Jul 2 '20 at 13:27
2

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
3
  • I am lazy, but can all this be acceptable into a single view? I didn't think sql server views allowed to create temporary tables. – Fandango68 Jul 2 '20 at 1:43
  • 1
    No. Views can't use temp tables, but Table-Valued Functions can. Or you might be able to make vwSPLITCARGO an indexed view. – David Browne - Microsoft Jul 3 '20 at 16:50
  • Perfect idea! Thanks. That solved it. – Fandango68 Jul 7 '20 at 2:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.