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