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]
    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?

  • 1
    Have you tried to get an (estimated) plan for an update? We can't tell if you should worry about optimizing the plan, but it sure shows a lot of scans – Tom V - try topanswers.xyz Sep 7 '17 at 7:34
  • @TomV there are lots of different updates that happen...are the execution plans the same? I took a look at one of the 'main' updates, and the estimated cost for updating this view was 1%. Plan is similar in structure as above, but interestingly shows clustered index seek instead of scan. – RPM1984 Sep 10 '17 at 23:13

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:

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.