Hi Ben,

> I have a view (lets call it V_VIEW).

> select ID, A, B, t2.C, sum(t2.D)
> from table1 t1
> left join table2 t2 on t1.ID = t2.ID
> group by ID, A, B, C

> When I run a query like this:
> select * from V_VIEW where ID = X
> it is SLOW

> But if I don't use the view, and just take the SQL that makes up
> V_VIEW and put the "where ID = X" into that SQL, it is FAST

Your condition on the VIEW is placed in the HAVING clause instead of in the WHERE clause. The reason
for this is that you could filter on an aggregate function (in your case SUM(t2.ID)). The only
workaround is running the statement direct or use an SP. The same problem is there for UNIONs in
VIEWs.
Firebird 2.0 will become much smarter for these VIEWs and will try to "distribute" the condition
from the HAVING clause to the WHERE clause when possible.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info