Hi,

> > Have you tried Firebird 1.5 beta 3? You'll probably notice a big
> > difference in the performance.
> Just downloaded the beta. I'll try to summarize my results with this
> version below,
>
> CASE 1:
> select p.productID, sum(o.qty)
> from product p
> join orderdetail o on (p.productid = o.productid)
> group by p.productID
>
> Result:
> PLAN JOIN (P ORDER RDB$PRIMARY2,O INDEX (FK_ORDERDET_REFERENCE_ITEMS))
> First run : 4 seconds (1.0.796 = 9 seconds)
> Subsequent runs : ~1.5 seconds.(1.0.796 = ~2 seconds)
>
> CASE 2: (changed the above query from JOIN to LEFT JOIN)
> Result:
> PLAN SORT (JOIN (P NATURAL,O INDEX (FK_ORDERDET_REFERENCE_ITEMS)))
> First run : 20 seconds (1.0.796 = 32 seconds)
> Subsequent runs : ~17 seconds. (1.0.796 = ~20 seconds)
>
> CASE 3: (Already fast enough in 1.0.796)
> select o.productID, sum(o.qty)
> from orderDetail o
> group by o.productID
> Result: no change.
>
> CASE 4: (Quoted above, three table left joins with a where clause on
> date column)
> Result:
> PLAN SORT (JOIN (JOIN (P NATURAL,O INDEX (FK_ORDERDET_REFERENCE_ITEMS)),
> M INDEX (PK_ORDERMASTER)))
> First run : ~60 seconds (1.0.796 = ~60 seconds)
> Subsequent Runs : ~51 seconds (1.0.796 = ~60 seconds)

Is Product.ProductID unique ? If yes how does this perform :

SELECT
p.productID,
(SELECT SUM(o.qty) FROM OrderDetail o
WHERE o.productid = p.productid)
FROM
Product p
ORDER BY
1

Regards,
Arno Brinkman