Hello everyone.

My client database has over 200,000 transaction records / 4000
products. The following query takes over 10 minutes per product. I
suspect something wrong with my plan. Can anyone suggest a solution ?

Many thanks
Duleep

SELECT SUM(D.BILLITEMQUANTITY) FROM BILLMASTER M, BILLITEMDETAILS D WHERE
BILLITEMCODE= :ITEMCODE AND M.BILLNUMBER=D.BILLNUMBER AND
M.BILLTYPE=D.BILLTYPE AND M.BILLDATE BETWEEN :FROMDATE AND :TODATE
AND M.BILLCANCELLED<>'Y'
INTO :SALES;

Plan:
PLAN JOIN (D INDEX (RDB$FOREIGN255),M INDEX (RDB$PRIMARY72,BILLDATEASC))

Adapted plan:
PLAN JOIN (D INDEX (DBFKBILLITEM),M INDEX (DBPKBILLMASTER,BILLDATEASC))