One way to solve this for an almost permanent time is to create temp-tables
from queries. Then query from from temp-table. Let the temp-tables be
regenerated once i a while. You have to figure out how often this must be.
That based on how often the database is updated with data.

JJ

On Wednesday 04 August 2004 09:38, 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
>
> 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))
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>

--

John H. Jaabæk
tlf. +47 915 24 599
Uavhengig Velværekonsulent for Nikken
http://www.nikken.com
http://www.nikkenuk.com