Re: Impact of views on performances

In mayority of cases views perfrom as well as the underlying query. Remember that a view is resolved at run time. If you have a query that joins 3 tables and a view that does the same perfromance will almost same. We have a view that does a join on 3 talbes 2 wirh 500k rows 1 with 20 million, and the performance of it and the view are identical.

However there are some special cases where a view can be 1000x slower than the a query (see http://home.clara.net/dwotton/dba/ojoin2.htm) and it occurs when doing outer joins and moving these into a view.

To tune a view tune the underlying query. And keep in mind that there will be cases where materializing a view or query will result in magnitude increases in performance. (we have at times created that 3 table join into an actual table (and left it static) for some reports situations where we need high performance.

In article <87uc1k$6le$1_at_nnrp1.deja.com>, karsten_schmidt8891_at_my-deja.com wrote:
>Hi Michel,
>
> AFAIK, views have no - to very insignificant impact on performance.
> The way it works ist that the view predicate and your query predicate
> is merged. An execution plan ist picked based on the resulting query.
>
> So, it depends on the actual query+view whether a given index will be
>used. If it makes sense to the optimiser, it will.
>
>Karsten
>
>
>
>In article <38a24a55_at_news.vo.lu>,
> "michel parisse" <michel.parisse_at_advalvas.be> wrote:
>> I want to use views to isolate code from changes of my database
>schema; Yet
>> I am told that views will have a negative impact on performance with
>the
>> growing size of my database (today with the largest table at 150000
>records,
>> but growing). Further, when I have index defined on the tables that
>I am
>> planning to access through views, will these indexes continue to be
>used?
>>
>> Any idea of impact or where to look for in depth information?
>>
>> Michel & Véronique Parisse
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Fri Feb 11 2000 - 12:29:09 CST