asked
Drawbacks of creating a view on large live table
We have a concept which assumes that we will change original table name with a view for some other purposes. This very large table (ca. 80mln rows) is lying in OLTP system but will not be changed straight forward but by triggers. When view comes it will filter data from underlying table with only one conditions in WHERE clause. Unfortunately this column is not very selective. Currently we have many indexes on that table, and many are used in the query hints during daily work. I have a questions what would be a drawback of such action? During some test I noticed that: - query index hints are not used when are used with a view - view's WHERE condition enforces index choice which is not exactly the most optimal one Do you have any other negative experiences?
Thanks for your insights Grant and Timothy. The table is updated quite often so we have decided not to use indexed views. Speaking about hints, maybe I went too far, usage of them is not so common but usually is caused by really complex queries which are not covered by indexes and sometimes database developers are not satisfied with the response time. But those hints are not my main concern. As I pointed out (during some tests which could have not really reflected real environment), after filtering by the WHERE clause in the view, optimizer firstly used the conditional column which decreased performance instead taking more optimal paths. Is there a way to filter data by the view and still make a usage of indexes from underlying table?
Short answer, yes. But it really depends on what the query in the view is doing and how the view is being referenced in the query that is calling it. Also add in your indexing structure and the accuracy of the statistics of those indexes. But yeah, you can see good index use when referring to a view, even one with hard-coded filtering mechanisms. I just can't tell you based on what you've supplied that you will absolutely see that in your situation.
Sounds like you're starting to dig a hole. If you have lots of indexes, but in order to get them to work with the queries you're writing you're using hints, I suspect, rather strongly, that there are issues with those queries. I would focus first on why you need so many hints in your queries. You should be using very, very few. The reason that a hint isn't working on the view is likely because the optimizer recognizes that it can't use the hint to satisfy the query because of the WHERE clause in the view. How often is the data in these tables updated? If it's only occasionally, or at scheduled times, I'd consider making a materialized view instead of a standard one. This will literally change the storage of the system to only hold what you're trying to get into the view. And if, that WHERE clause isn't terribly selective within the regular data set, at least you've moved all queries from that non-selective data set to a different location, which will help with blocking and contention on the original table. But this is only possible if you don't update the data too frequently. I still think the problems are more fundamental though.
Grant Fritchey is absolutely right, but to expand slightly on what he said: Hints should work quite nicely inside of views, unless there is a different reason that SQL Server would disregard them. But as Grant said hints should generally be used sparingly and the only one I use on a regulare basis is [NoExpand][1] One other thing to remember is that indexes can reduce write performance. Normally this isn't a major factor since reads will generally outnumber writes and the trade off is well worth it, but if you start having many indexes on a large table that gets written to frequently it may be something to look at. [1]:
http://www.sqlservercentral.com/articles/Indexed+Views/63963/
Excellent points. I should have pointed that out as well. Thanks for the addition.
9 People are following this question.
