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
