October 12, 2011 at 11:05 am
If a view has no indexes, but is built from base table(s) that DO have indexes, do queries on the View benefit from the base table indexes?
Scenario:
1) Base table TblMain with an index (idxCol2) on column "Col2"
2) View TblMainV is created in a different database (same server), based on TblMain; contains most of the columns which are renamed for better readability;
3) View TblMainV has no indexes at all.
4) Query on the view: "Select Col5, Col6, Col1 FROM TblMain WHERE Col2 = 'blah' "
Will the query use the base table's idxCol2 index?
Thanks~
October 12, 2011 at 11:32 am
It will if this is the best way to access the data.
Now keep in mind that such a query would need to do a bookmark lookup to fetch the columns in the select. Those are VERY expansive to do. It's likely to use the index if 2-3% of the data is returned (ideally, less than that).
October 12, 2011 at 11:44 am
Thank you for the reply.
To further clarify, let's say
>the base table/view is updated only once daily.
>there are a number of queries, running throughout the day, that use the View table.
Given the same query as before, is it likely the addition of an index on the view itself would render significantly better performance?
I realize a legitimate response would be: "well just try it and see for yourself!", but I'm primarily a "reporting guy" and am just going by what I'm told by the "data guys", so I'm trying to educate myself more on performance issues (because my reports are the most noticable manifestation of poor performance!)
October 12, 2011 at 11:47 am
The 99% correct answer is to work on the base table's indexing.
Please post the actual execution plan and we'll see what we can do.
Also the list of index with their definition.
October 12, 2011 at 12:28 pm
Let me get some more facts together so I can come up with something meaningful.
Thanks for the input!!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply