x

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?
more ▼

asked Sep 04, 2012 at 01:47 PM in Default

Bartoloz gravatar image

Bartoloz
160 3 3 4

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?
Sep 05, 2012 at 08:37 AM Bartoloz
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.
Sep 05, 2012 at 09:54 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.
more ▼

answered Sep 04, 2012 at 01:55 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.6k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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

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.

more ▼

answered Sep 04, 2012 at 04:37 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Excellent points. I should have pointed that out as well. Thanks for the addition.
Sep 04, 2012 at 05:00 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x245
x39
x31

asked: Sep 04, 2012 at 01:47 PM

Seen: 1081 times

Last Updated: Sep 05, 2012 at 09:54 AM