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.

  • 6
    This doesn't sound like a good use case for an indexed view in the first place. A lot of people seem to think that slapping an index on a view will magically speed up the view, but this is rarely the case. – Aaron Bertrand Feb 7 '17 at 22:34
  • @AaronBertrand Hmm, perhaps. I was hoping to use the index view to cut down on compilation time, since it will already have done the joins. I'm sure I've seen that suggestion (doesn't make it right, though). Do you have a different suggestion for that? – Yishai Feb 7 '17 at 22:48
  • 3
    At 20 joins I'd probably start thinking about denormalizing if the query requiring them is running often enough, or critical enough, to be concerned about compile time (or timeouts). – Erik Darling Feb 7 '17 at 23:06

Your Answer

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

Browse other questions tagged or ask your own question.