On Sun, Nov 2, 2008 at 4:11 PM, Simon Windsor
<simon.windsor@cornfield.me.uk> wrote:
> Hi
>
> Generally, I have avoided using VIEWS within application code and only used
> them for client interfaces, the sole reason being the performance of views
> against tables.
Have you confirmed this belief with tests? Or just following some
kind of general knowledge that's filtered down from other dbas?
> Changes to database over the past few years appear to have improved the
> performance of views, but I am still not comfortable with using VIEWS within
> application code. The main reasons I have are
This is true for some other dbms'. It's generally not true for
PostgreSQL. PostgreSQL views have for a very long history of running
as queries the same as if you ran them by hand. This is due to the
use of an advanced rules system that allows for the same performance
of views as for the original queries on the table.
> * Data within a view is not necessary sequential, unlike a table
Data in a pgsql view is EXACTLY the same as if you ran the query by hand.
> * Higher overhead mapping to original tables and indexes
The overhead in pgsql is measured in sub millisecond time.
> * Danger of linking views and tables and not utilising utilising
> underlying tables properly.
not sure what you mean there at all.
>
> Am I right to avoid to VIEWS within application code?
Yes. Absolutely. If you're running MySQL. OTOH, if you're in pgsql
there's no real reason to avoid them. unless you'll also be
supporting mysql. then mysql plays the lowest common denominator
trump card it so often does and forces you to dumb down your db layer
to make it happy.