Thanks for your reply. Using a stored procedure looks like the way to go.

-Ben


--- In firebird-support@yahoogroups.com, "Arno Brinkman"
<firebird@a...> wrote:

> 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