At 07:38 AM 4/08/2004 +0000, you 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
>
>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))

1. This plan will be slow if the index RDB$FOREIGN255 is on the column
BILLITEMDETAILS.BILLTYPE. Is it? If so, modify the query so that the
optimizer won't consider using it. Or look at the redundancy in your
master-detail structure and simply ignore BILLTYPE. Presumably, the master
can't have multiple billtypes, so all of the billitemdetails will be of the
same billtype without needing to join on it.

2. Use full aliasing throughout (alias is missing from one of the search
criteria).

3. Use SQL-92 explicit join syntax. Some database access layers get tied
up in knots with the outdated implicit join syntax.

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

Is this an alternative plan that you tried? Did it make any difference?

FYI, here's a modified statement that you could try, and see what plan the
optimizer gives you:

SELECT SUM(D.BILLITEMQUANTITY)
FROM BILLMASTER M
join BILLITEMDETAILS D on
M.BILLNUMBER=D.BILLNUMBER
/* AND M.BILLTYPE=D.BILLTYPE {remove} */
/* or, if you do need to join on billtype */
/* and ((m.billtype = d.billtype) or 1=0) { to disable use of the
index} */
where d.BILLITEMCODE= :ITEMCODE
AND AND M.BILLDATE BETWEEN :FROMDATE AND :TODATE
AND M.BILLCANCELLED<>'Y'
INTO :SALES;

/heLen