I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there any way to cache the output of the view, and dynamically update it whenever any of the underlying tables are updated?
A few quirks of my system:
- The database only sees updates once a day, during off-hours. So a resource-intensive solution is fine, as long as the resources are consumed during these off-hours.
- Due to the way the system is set up, I would prefer to not clean up the data while loading it, and would rather leave the underlying tables untouched.
- I have appropriate supporting indexes set up
- The tables all contain distinct records with no overlap, so I need to pull them all in
Some options I've considered:
- An indexed view won't work due to the view relying on the UNION operator.
- I haven't found any way to cache the output of a TVF.
- I could create a cache table and manually load it any time the underlying table if modified, but this feels like a hacky workaround. I think I'd also have to create a SQL Agent Job for this, since setting it as a trigger would involve refreshing the table millions of times each evening while the table is overwritten.
- I could live with the poor performance, but this would negatively impact end-users.
Here's a simplified example of the view:
CREATE OR ALTER VIEW AllNamesAndAccountNumbers AS
SELECT
x.AccountNumber
, COALESCE(x.FullName, n.FullName) AS FullName
FROM (
SELECT
AccountNumber
, FullName
FROM TableWithNames
UNION ALL
SELECT
AccountNumber
, NULL AS FullName
FROM TableMissingNames
) x
LEFT JOIN NameMappings n -- this join is slow due to the table being really huge
ON n.AccountNumber = x.AccountNumber
Oh - if this is an antipattern, and I should be attacking this from a fundamentally different angle, please say so. I'm concerned I'm looking at this wrong.
