🚀 go-pugleaf

Here is a quite simple query and it's plan.  It takes 25 to 30 seconds
to complete, which is way too long.  I can't think of another way of
doing it.

The table ARITEM has a lot of rows.  Each ARITEM row can have an
ARTRANSACTION tied to it (only 1.  There is an ARTRANSACTIONID field in
ARITEM).  I need all the ARITEM that have a scandate >= to a specified
date and that there is no ARTRANSACTION linked to it, or, if there is
an ARTRANSACTION, the date on the ARTransaction must be > the same date.

Here is how I did it:

Select sum(amount) from ARITEM a  left outer join ARTRANSACTION art on
art.ID=a.ARTRANSACTIONID
  where a.client=:aClient and a.customer=:aCustomer
  and ((a.SCANDATE <= :aDate)
  and (a.ARTRANSACTIONID is null or art.trandate > :aDate))

Plan:

PLAN JOIN (A INDEX (I_ARITEM_SCANDATE),ART INDEX (RDB$PRIMARY64))

I can't think of another way of doing this query, or speeding it.


--