Sql-server – Can indexing views in SQL Server 2005+ speed up SELECT count(*) from a View

index-tuningmaterialized-viewsql serverview

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

Best Answer

Typically you index views if you are often running aggregates, not to magically speed up joins. Also if you are not using table partitioning there is no good reason to investigate partition-aligned indexed views.

You should be focusing on optimizing the query, irrespective of the view, IMHO. An indexed view is not a magic turbo button (though a lot of people seem to think it is).

Also consider whether you should just run a query separately from the view anyway - often people run a lot of queries through the same view for convenience, even if the view actually touches more columns / rows than are necessary for the specific query.

Related Question