I'm not an expert on databases, so please be kind.
I've defined a set of tables in Microsoft Access. These tables have various
complex relationships between each other. Constructing the SQL to access
data in these tables may be fine for more experienced folk. For me, I'd
rather use the graphical tools that Microsoft Access provides.
I was pleasantly surprised to discover setting up a query in Access results
in something that appears to work like a virtual table. I can write a
SELECT query against it and it seems to work like any other table. It shows
up as a "view" under the /table_info command of dbish.
Well, I was impressed. Four questions about this:
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?
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?
Third, how portable are views? Does MySQL (the other database I would be
using) offer them? Is this something that is common?
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?