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.
Best Answer
That's the fallback. And it's totally normal to run an ETL job after loading staging tables.
You might be able to do something with 2 indexed views instead of one. And, of course, you can play with indexes and columnstore tables to improve query performance.