June 3, 2012 at 1:11 pm
Does anyone have any tips on optimizing views?
June 3, 2012 at 2:25 pm
Assuming you mean a regular view and not an indexed view you would optimize the underlying query. A view (regular) is not able to be optimized in and of itself.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 4, 2012 at 12:01 am
CELKO (6/3/2012)
I have a two part article on VIEWs in Simple Talk that might help.
Links:
http://www.simple-talk.com/sql/learn-sql-server/sql-view-basics/
http://www.simple-talk.com/sql/t-sql-programming/sql-view-beyond-the-basics/
June 4, 2012 at 7:34 pm
...and do almost everything that is neccessary to avoid nesting your views. No matter how well your query is tuned, you will get performance issues sooner or later when nesting views, particularly if there are views joining more than 2-3 tables.
June 4, 2012 at 8:33 pm
Once you start using Views, how do you prevent other people from Nesting them?
June 4, 2012 at 9:26 pm
Smack the back of their hands when they try to.:hehe:
If you have a DBA reviewing code before it goes into production, it is done at this point.
If you are talking about ad hoc queries, well let them suffer unless they start to impact other users on the server. The problem I see most often with nested views is relatively long compile and parse times compared to data retrieval. I think the optimiser has difficulty unrolling the views and getting an acceptable plan. My guess is that optimising table access when the same table is potentially referenced at different levels within the nested view is a particualry curly problem for the optimiser.
June 5, 2012 at 6:20 pm
Thank you for the responses. I'll try not to use views. 🙂
June 5, 2012 at 6:52 pm
ReginaR1975 (6/5/2012)
Thank you for the responses. I'll try not to use views. 🙂
I wouldn't avoid using views, I'd just make sure I understand the limitations of them. The reason view hierarchies end up being a mess is because a lot of times instead of simply building a new view that restricts the data to the desired result multiple views are pulled in, bringing along a lot of unnecessary baggage to the task at hand.
The optimizer will strip these out until a certain point is reached, where the optimizer simply throws up its hands and will do the best it can. You'll notice these if you look at the XML in your .sqlplans where you see 'TIMEOUT' for the optimizer.
Views can be very useful methods to contain small, limited logical components that you'll use under certain circumstances with a limited scope for inclusion.
For example, my preference for building views is something like "Most recent record in change log". This view has encapsulated all the necessary logic to pull the most recent edit out of something like a price-change log. Instead of re-incorporating that logic all over the place, the wrapper keeps it in a single location and allows me to use it that way.
However, if I need to display the full price history of an item, I would avoid the view and go straight to the table. Where the compounded views gets hairy is when you have logic like that in a view that's being used in another view which in turn is... you get the drift. Somewhere buried in all that logic is some distinct or grouping code that makes what should be a simple data-grab into a churn because of the encapsulated, and hidden, logic that underlies the top layer.
Don't avoid views, just use them with intent, not as defaults.
I'd be poor assistance if I didn't include one other common usage for views, and that's as a 'table base' that you can keep sound for multiple interfaces into your system without having to update multiple external entities for minor schema modifications. For example, if the systems that pull data from your sales system need to pull cohesive lines of data for each line item in an invoice, you can provide them a view that will return them without them needing to code against all the underlying hierarchy. This way, if you need to do adjustments to the underlying components, you can leave the view intact and not have to do change-rolls to multiple systems.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply