Optimizing an indexed view used for selects to also do updates

All we need is an easy explanation of the problem, so here it is.

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:
enter image description here

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

See also:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply