Hi!

Just to add a comment...


I had experiencied a 1000 times improvement on specific queries using FB
2.0. Yes it is a very particular case, but anyway.. it gives a response
1000 times faster (down from 6 seconds to 6ms).

The query is similar to it (In fact is a bit more complex, but this
gives the idea)

create view vTest (Number, Total) as
select
O.Number, Sum(OI.Value)
from
Orders O join
OrdersItems OI on (OI.OrderID = O.OrderID)
group by
O.Number


Then I use this:

select * from vTest where Number = 1

In FB 1.5 the Optimizer will execute the above query to something
similar to:
select
O.Number, Sum(OI.Value)
from
Orders O join
OrdersItems OI on (OI.OrderID = O.OrderID)
group by
O.Number
having
O.Number = 1

while FB 2.0 execute it as
select
O.Number, Sum(OI.Value)
from
Orders O join
OrdersItems OI on (OI.OrderID = O.OrderID)
where
O.Number = 1
group by
O.Number

The first query (as execute in FB 1.5) will need to process all data and
filter it after having the agregations calculated it will be a bit slow
on big tables, while FB 2.0 use the WHERE clause to filter it wich gives
an instant result back.

In your case I think that FB 2.0 choosed the ideal plan while FB 1.5
choosed a sub-optimal plan.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br