Re: Views- Advantages and Disadvantages

Joshua D. Drake wrote:
> Brent Wood wrote:
>> Ashish Karalkar wrote:
>>> Thanks Brent for your replay,
>>>
>>>
>>> What about the Disadvantages, Performance issues?
>>
>> As far as I'm aware, performance is the only real disadvantage.
>
> What performance are we talking about here? Executing from a view
> although has *some* overhead, I don't even know that it is worth
> considering in most cases.

Sorry, I didn't express that clearly...

A view as a filter on a single table, or a simple two table join is
generally quite fast enough, and has no noticeable overhead over a query.

A complex self relation plus some joins instantiated via a view can be
slow. But a query to produce the same output from the same underlying
tables will be just as slow.

In such cases, (like some data warehousing) a redesigned, denormalised
table structure can be implemented to provide the same "look" as the
view would have, but as a physical table, well indexed and much faster
than the view, without the joins being required.

My comment was meant to indicate that a complex view wihich is too slow
may be replaced by a denormalised physical table, which is faster, not
that a view is significantly slower than the same query upon the same
tables.

To paraphrase, the view/query performance is dependent on the underlying
table structures & relationships, and these are what may need to change
if a view is too slow. In many cases, such changes can make the original
view redundant.

Cheers,

Brent