Re: Performance of Views

akreienbuhl_at_yahoo.com (alainkr) wrote in message news:<9822b77f.0210010654.13456cb8_at_posting.google.com>...
> Hello all,
>
> I need your advice for the following problem :
>
> I'd like to use a view for a query that is repeated througout our
> code. That query has 7 joins. From a developer I'd really like that.
> It would prevent each developer from aliasing with different name etc.
> I'm affraid of performance problem since that view would be against a
> quite large table 3,000,000 records / year
>
> A ran an execution plan for both (using the view and using tables)
> there're obliously identical. Now does that mean that I won't have any
> more performance problem with the view that without.
>
> I apreciate your help.
>
> Alain.

Alain, no this does not guarentee that you will never have a performance problem related to the view, but it does mean you are probably OK.

If you are using the CBO then outdated statistics or a permanent change over time in the relationship (relative size, data value range changes etc...) between some of the tables could affect the optimal plan and require revisiting the tuning process. This is also true with a straight query against the tables.

In a case like this the use of a view to define the join will probably help reduce the chance of bad SQL being developed to perform the same query since the view as coded should help ensure the best join order/method is in use.

IMHO -- Mark D Powell -- Received on Tue Oct 01 2002 - 13:36:56 CDT