Hi Duleep!

Since your statement uses the primary key of BillMaster in its plan,
using an additional index for billdate is unnecessary. I also changed
your query to SQL-92 and added the alias to the billitemcode.

So this should be faster:

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

Tell us whether this is sufficient or if you still need some
performance improvement.

HTH,
Set
- I am still a member of the Firebird Foundation.
- Join today at http://www.firebirdsql.org/ff/foundation

--- In firebird-support@yahoogroups.com, "Duleep" wrote:
> 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
>
> Plan:
> PLAN JOIN (D INDEX (RDB$FOREIGN255),
> M INDEX (RDB$PRIMARY72,BILLDATEASC))
>
> Adapted plan:
> PLAN JOIN (D INDEX (DBFKBILLITEM),
> M INDEX (DBPKBILLMASTER,BILLDATEASC))