3

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

Improve this question
  • 4
    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
4

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.

Improve this answer
  • A great case for indexed views are joins where you filter and/or sort on both sides. No way to avoid O(N) in the general case without indexed views. In such cases they can be a magic unicorn solution! – usr May 8 '17 at 21:12
  • @usr Compared to indexes? – Aaron Bertrand May 8 '17 at 22:56
  • You can't index on columns of multiple tables at once normally. That's what I was getting at. E.g. filter on Orders.Status = 1 AND Customers.IsPremiumCustomer = 1. Both predicates low selectivity, together possibly high selectivity. Need indexed view or denormalization. – usr May 9 '17 at 12:19
  • @usr If that is a high explicit use case I would suggest that a filtered index on each table might be more effective than just denormalizing, since those indexes could be used for similar queries that don't rely on the join, too. Indexed view seems like a bit of a stretch here IMHO - I've found them to be most effective when you eliminate a lot of rows due to reads and pre-aggregation, rather than making joins slightly more efficient. I'm happy to be shown an actual example that demonstrates this though, do you have one at hand? – Aaron Bertrand May 9 '17 at 12:45
  • I do. I work on a web app that frequently renders lists of "items" that are connected to other tables over joins (think rendering orders associated with products and customers etc.). The filters being used are very varied but always equality predicates. My code can automatically generate a set of dozens of views joining the respective tables together and indexing them in complex ways. The data size incl. all indexes is maybe 1GB. Those indexed views have greatly accelerated the site. Going from basically hash joining large fractions of multiple tables to trivial range seeks. – usr May 9 '17 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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