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