My initial question was How does someone determine if it is a good idea to index a view?.
Update: My underlying assumption was that indexing views speeds them up. This is apparently not sensible, since views just execute queries, as the comments below point out.
What I'm doing right now is very similar to what this guy here is doing: I'm finding views that are selecting about 2K rows out of 2 million rows, take far too long to fetch rows, and I thought that a schema bound indexed view might help.
The operation that I was trying to optimize is:
select * from vwSample where TYPE_CODE = 'X';
TYPE_CODE
is a 10-character string, there are about 2K unique TYPE_CODE
values in vwSample
, and there are about 2-5 million rows in vwSample
, and there are between 2K and 10K rows for each unique value of TYPE_CODE
. I didn't post my execution plan because I was trying to ask a "general practice" question.
Update: it looks like my question is wrong-headed, I should just write and optimize queries, and ignore the fact that there's a view, maybe even just write it as a regular query and ignore this view. And there are no "turbo buttons" available in views, that can make my view faster, by magic as the accepted answer suggests. My question appears to boil down to "I'm a SQL Server n00b
and I know nothing about optimizing views, how do I speed up select * from vwSomething where KEY=X
where vwSomething
is a view?".