0

I have a largish table (>126 million rows), and a need to optimize a specialized set of queries, so I created an Indexed view on the table, and coded a procedure that would select from the Indexed View when the conditions were favorable for it.

Execution of the procedure took longer than I expected, so I looked at the execution plan, and I see that the query that takes the longest is the one that selects from my view, and when I hover over the element that takes the longest it's a Clustered Index Seek of the CI of the underlying table. Not the Index on the view.

I understood that an Indexed View was a sort of virtual table that was stored on disk, and that when queried, did not pass the query through to the underlying table at all. Is this not the case?

Improve this question
1

1 Answer 1

Reset to default
2

The optimizer might choose to use the underlying table (it "expands" the view) based on how it chooses to implement your query. To force the query to use the indexed view, you could add a WITH (NOEXPAND) hint to the FROM clause of the query where the view is referenced.

This is useful for two reasons:

  1. It gets the query to use the view you want
  2. It might show you why the optimizer chose not to use the view (maybe the index definition needs to be tweaked to get better performance, etc)

By the way, it's recommended to use this hint regardless when referencing indexed views directly, in order to avoid other problematic behavior. See this article from Paul White for details: Indexed Views and Statistics

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.