The plain SELECT and the SELECT on a view below are not coded
the same way. You are making an optimization in the plain
SELECT that SQLite does not currently perform, namely
constraining the two SELECTs seperately _before_ the UNION.
The code generated by SQLite on the select on a view acts more
like this (untested) query:
SELECT * FROM (
SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment,
dr.rightfield1, d.status
FROM table1 u, table2 dr, table3 d
WHERE dr.ralentid=u.id
AND dr.ralentblc=u.blc
AND dr.lewisid=d.id
AND dr.lewisblc=d.blc
UNION ALL
SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment,
dr.rightfield1, d.status
FROM table1 u, table4 ugu, table2 dr, table3 d
WHERE ugu.userid=u.id
AND ugu.userblc=u.blc
AND ugu.userGroupid=dr.ralentid
AND ugu.userGroupblc=dr.ralentblc
AND dr.lewisid=d.id
AND dr.lewisblc=d.blc
)
WHERE uid=6 and ublc=8193
I would think it would not be too difficult to extend SQLite
to perform this type of transformation on a view.
i.e., transform:
SELECT columns0 from (
SELECT columns1 WHERE condition1
UNION (ALL)
SELECT columns2 WHERE condition2
)
WHERE condition3
to
SELECT columns0 from (
SELECT columns1 WHERE (condition1) AND (condition3)
UNION (ALL)
SELECT columns2 WHERE (condition2) AND (condition3)
}
or am I neglecting something?
--- Ole Göbbels <[EMAIL PROTECTED]> wrote:
> Hello everybody,
>
> I encountered some performance problems using a VIEW with UNION ALL
> statement.
>
> This statement works fine:
>
> SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment,
> dr.rightfield1, d.status
> FROM table1 u, table2 dr, table3 d
> WHERE u.id=6
> AND u.blc=8193
> AND dr.ralentid=u.id
> AND dr.ralentblc=u.blc
> AND dr.lewisid=d.id
> AND dr.lewisblc=d.blc
> UNION ALL
> SELECT u.id AS uid, u.blc AS ublc, d.id, d.blc, d.name, d.comment,
> dr.rightfield1, d.status
> FROM table1 u, table4 ugu, table2 dr, table3 d
> WHERE u.id=6
> AND u.blc=8193
> AND ugu.userid=u.id
> AND ugu.userblc=u.blc
> AND ugu.userGroupid=dr.ralentid
> AND ugu.userGroupblc=dr.ralentblc
> AND dr.lewisid=d.id
> AND dr.lewisblc=d.blc;
>
> When I create a view:
>
> CREATE VIEW testview AS
> SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status
> FROM table1 u, table2 dr, table3 d
> WHERE dr.ralentid=u.id
> AND dr.ralentblc=u.blc
> AND dr.lewisid=d.id
> AND dr.lewisblc=d.blc
> UNION ALL
> SELECT d.id, d.blc, d.name, d.comment, dr.rightfield1, d.status
> FROM table1 u, table4 ugu, table2 dr, table3 d
> WHERE ugu.userid=u.id
> AND ugu.userblc=u.blc
> AND ugu.userGroupid=dr.ralentid
> AND ugu.userGroupblc=dr.ralentblc
> AND dr.lewisid=d.id
> AND dr.lewisblc=d.blc;
>
> and then run a query like "SELECT * FROM testview WHERE uid=6 and
> ublc=8193",
> the performance is very bad.
>
> Each select of the UNION without the UNION and without the other select
> as a view works fast, on the other hand.
>
> Is there any error in the way the VIEW is created? Is there anything one
> can do to speed it up?
>
> TIA,
> OG
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
