On Thu, Aug 12, 2004 at 03:38:21PM +0100, Christian Smith wrote:
> AFAIK, views are compiled into their query definitions when executing a
> statement, so once the compilation is done, performance should be
> identical to their raw query form.
> In short, create a view if you have a common filter on some data. If
> performance is a factor, precompile the view.
At least in Oracle and PostgreSQL, the problem with views is that if
you then re-use them in a complicated query, you MAY get much worse
performance than if you wrote the query against all the underlying
tables directly. This is because:
1. Sometimes the query optimizer is just dumb about optimizing queries
containing views. (E.g., doing joins against views in Oracle,
sometimes.)
2. Sometimes it's not the query optimizer's fault at all; by using the
view in your query, you actually end up asking the RDBMS for something
subtly different, which (although the query results may happen to be
the same) makes your query legitimately much slower. Tuning your
query to fix this may require eliminating use of the view.
SQLite's query optimizer is presumably much simpler than that of
either Oracle PostgreSQL, so it probably shares these sorts of
problems. But views are still good. Generally, I'd only remove use
of a handy view once you see a real performance problem (a slow query
with a bad query plan).
--
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/
