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 

Reply via email to