Hi,

> SELECT
> p.productID,
> (SELECT SUM(o.qty)
> FROM OrderDetail o join ordermaster m
> on (o.ordermasterid = m.ordermasterid)
> and (m.orderdate between '2003-01-01' and '2003-01-02')
> WHERE o.productid = p.productid)
> FROM
> Product p
> ...
> PLAN JOIN (O INDEX (FK_ORDERDET_REFERENCE_ITEMS),M INDEX
> (PK_ORDERMASTER)) PLAN (P NATURAL)
> Execution time is almost instant (around 1 seconds), the fetching is a
> different story altogether...:)
>
> Since the performance difference is quite big, does this mean that I
> have to rewrite all my LEFT JOIN queries?

No, you see the difference only in execution time.
To compare the REAL speed between the two you must also include fetch-all
and then you'll see they are almost the same.

If you don't have it, download the great tool below :
http://delphi.weblogs.com/IBPLANalyzer


One question from my site :

What are your database-settings ?
- Page Buffers ?
- PageSize ?

Regards,
Arno Brinkman