A view is basically just a SELECT on a subquery. These two queries
are functionally equivalent:
SELECT * from va;
SELECT * from (SELECT a, b, 0 AS c FROM ta
UNION SELECT a, b, 1 FROM tb);
SELECTs on subqueries (or views) containing UNION or INTERSECT or
EXCEPT can be slow. Generally SQLite can "flatten" subqueries into
their parent SELECT resulting in better speed. But for compound
selects (i.e., selects with unions), no attempt is made by SQLite
to flatten the subquery, and a temporary table is created to hold
the intermediate result set of the compound query.
It is easy enough to add a few lines of SQLite source to recognize this
SQL parse tree pattern:
SELECT * FROM (...subquery...);
- a SELECT * on a suqbquery with no outer WHERE, GROUP BY,
ORDER BY or LIMIT clauses - and transform it to just:
...subquery...
prior to VDBE code generation, but I'm not sure this situation comes
up very often to warrant such a specific hack. It would be better
to fix the general case and change multiSelect() to make use of
flattenSubquery() for the rightmost query in a compound query prior
to code generation. But this change is not as straight-forward.
For a more detailed explanation look for "flattenSubquery" in
src/select.c or go to "9.0 Subquery flattening" in
http://www.sqlite.org/optoverview.html
See also:
Ticket 1924: optimize queries on unions, constant folding
http://www.sqlite.org/cvstrac/tktview?tn=1924
--- Brodie Thiesfield <[EMAIL PROTECTED]> wrote:
> Hi,
>
> In my database I find that the explain program for the view (114
> statements) is much longer than direct query that comprises the view
> (89) and almost twice as long as doing the 2 separate queries that make
> up the union in the view (39 + 30 = 69).
>
> To explain more clearly what I mean, if I create a database as...
>
> CREATE TABLE ta (a INTEGER NOT NULL, b VARCHAR(30));
> CREATE TABLE tb (a INTEGER NOT NULL, b VARCHAR(30));
> CREATE VIEW va AS
> SELECT a, b, 0 AS c FROM ta UNION SELECT a, b, 1 FROM tb;
>
> And then run the explain statements...
>
> EXPLAIN SELECT a, b, 0 AS c FROM ta;
> EXPLAIN SELECT a, b, 1 AS c FROM tb;
> EXPLAIN SELECT a, b, 0 AS c FROM ta UNION SELECT a, b, 1 FROM tb;
> EXPLAIN SELECT * FROM va;
>
> I get the results... 15, 15, 35, 45 (using sqlite 3.3.8)
>
> So in this contrived example, we have an overhead of 5 statements for
> the union, and 10 statements for using the view over issuing the direct
> union query itself. Not a lot in this example but still indicative of
> what I am seeing in my database.
>
> I assume from this, that querying from a view is slower than querying
> the tables directly or even just issuing the same query as the view
> directly? Is it worth going back to issuing direct queries? The
> advantage of the view is simplicity of course.
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
