Optimizing an indexed view used for selects to also do updates

Question

I have a materialized indexed view that returns ~2.6 million of the ~2.7 million rows in the table, causing the optimizer to mostly use clustered index scans. If I do an update on this view will it use the same execution plan? If so, what should/could I do differently to optimize updates? These tables have roughly 1:1 relationships.

This question was originally part of another question but it was suggested I ask this on its own. Related question: Why is an index I created not being used in a query execution?

For example, consider the following indexed view:

CREATE VIEW [dbo].[ListingSearchView]
WITH SCHEMABINDING
AS
    SELECT      -- lots of fields
    FROM        [dbo].[Listings][a]
    INNER JOIN  [dbo].[ListingFeatures][b] ON [a].[ListingId] = [b].[ListingId] 
    INNER JOIN [dbo].[ListingBuildingDetails][c] ON [a].[ListingId] = [c].[ListingId]
    INNER JOIN [dbo].[ListingLandDetails][d] ON [a].[ListingId] = [d].[ListingId]
    WHERE       [a].[IsVisible] = 1
    AND         [a].[IsLive] = 1
    AND         [a].[AgencyCompanyId] IS NOT NULL

If i run the SELECT through the estimate in SSMS, i get this:

Is that the same query used when the data behind the various tables are updated? Do i need to worry about optimizing the above query plan?

Paul White · Accepted Answer · 2017-10-06T12:56:44.417

If I do an update on this view will it use the same execution plan?

No. When an (non-snapshot) indexed view is present, SQL Server automatically adds the right execution plan operators to all insert, delete, update, or merge statements that affect tables used in the view, to keep the view perfectly synchronized with the underlying data.

The general strategy is to compute the net (delta) effect on the view, then apply those deltas to the view index(es). For a simple view with only selections, projections, and inner joins, this is fairly trivial. For some more information on the topic, see Conor vs. Indexed View Updates by Conor Cunningham.

...what should/could I do differently to optimize updates?

In many cases, no special steps are required beyond those you would normally take to achieve a well-designed and indexed set of base tables.

Examine the estimated execution plans for a set of representative updates (including insert, update, delete, and merge) against each of the base tables underlying the view. Be sure to try single row updates, and larger updates.

The shape and runtime performance characteristics of the indexed view maintenance part of the execution plan can be tuned much like any other plan-analysis exercise. Often, it will be painfully obvious if any indexing changes are required. Feel free to submit a specific example with execution plans as a new question.

Optimizing an indexed view used for selects to also do updates

1 Answers1

Linked