I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) and the self-joins were only in two tables that were moved to an outer view.
The problem was that the view alone would take 3+ seconds for compilation, which was much too slow for the use case. Following advice like this:
Indexed view is also a great way to improve INNER JOINS performance. When two or more table are prejoined in an indexed view, the query optimizer can choose to retrieve the materialized view data instead of performing a costly join operation.
I was looking at solving the problem with an indexed view.
However, once this indexed view was created (it consists of about 20 joins) every update to the underlying table became very slow. Set Statistics IO On shows that it is re-accessing all of the underlying tables on statements that don't even modify any data as it reevaluates the view, for example an update that looks like this:
update a set y = b.y, z = b.z from a inner join b on a.x = b.x where exists (select a.x, a.y, a.z except select b.x, b.y, b.z)
So there are zero rows affected, but that whole indexed view and its dependent still gets a bunch of action and the execution time shows it.
Is there a way to get SQL server to not use that view unless it actually needs to update records? Perhaps there is a hint I don't know about.