On Sun, 8 Apr 2001, John Passaniti wrote:

> First, is there a significant performance penalty for using a view verses
> creating the SQL for the complex join I'm trying to do on the raw tables?
> That is, does a view create something like a temporary table and then allow
> SQL to be run against that?  Or does it do something like take the canned
> SQL of the view that I set up in Access and somehow merge it with the SQL
> query I'm running from the DBI?

A view can be used very much like a table -- it is in essence like a
virtual table.  I prefer to use views for complex relationships and for
filtering large tables when I repeatedly need a subset of data.

How views are handled depends on the database engine you are using.  I
don't know how Access, which uses the Jet Engine, handles views (or as
they called them 'queries').  Some databases have updatable views.

> Second, how table-like is a view?  I found I was able to insert into a view,
> and it put the values I inserted into the underlying tables.  Does this
> always work with views, or are there limitations on how you can treat a view
> verses a table?  Does the DBI impose any limitations?

This depends on the database system.  I think Oracle supports updatable
views.  PostgreSQL has updatable views planned for an upcoming release.

> Third, how portable are views?  Does MySQL (the other database I would be
> using) offer them?  Is this something that is common?

Sadly, MySQL is an exception -- it does not support views, although you
will find just about every other database system (free or commercial)
does.  This is something that is sorely needed in MySQL.

> Fourth and last, when I first tried to connect to the database using the ADO
> driver, I saw the view but couldn't use it.  I then accessed it using the
> ODBC driver, and I could see it and it worked fine.  Does this mean that the
> view functionality is really part of the ODBC driver?

The ADO driver may handle your view differently.  I'm not familiar with
how ADO handles views and updatable queries, so you may need to do more
research.

-- Brett
                                   http://www.chapelperilous.net/btfwk/
------------------------------------------------------------------------
Due to a shortage of devoted followers, the production of great leaders
has been discontinued.

Reply via email to