1

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.

2

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

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.

1
  • 1
    I'm getting diminishing returns trying to tune the view, I think I'm just going to cache it into a table. Probably a simpler answer in the long run. Thanks for the answer! – Cowthulhu Apr 28 '20 at 15:37

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.