Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
3  
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. What is your goal? Your question is like asking, when should I put 20" rims on my car? –  Aaron Bertrand Nov 14 '12 at 16:49
    
We are not running aggregates, we are trying to speed up repeated sub-selects of 2k-10K rows by a string type-code, from a view. –  Warren P Nov 14 '12 at 18:05
    
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). Instead of asking "should I index my view" why don't you post the details of your schema and query and ask if anyone has any suggestions on optimizing either the design or the query itself? –  Aaron Bertrand Nov 14 '12 at 18:06
    
Okay. So I'm doing this wrong. Look at the query underneath, not the view? –  Warren P Nov 14 '12 at 18:07
1  
Right, a view just runs your query, it doesn't store any results or optimize the query in any way. An indexed view does by materializing the data, but this won't necessarily improve your current query patterns - and there is also a maintenance cost involved. So even if you do speed up this query by a little bit, you might slow down the DML portion of your workload dramatically. Nothing is free. –  Aaron Bertrand Nov 14 '12 at 18:08

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.