>Thanks for the replies from all of you.
>
> I got around 10% faster by setting DELETED off. By using SYS(3054), I
>found that the first SQL-SELECT (takes 35 sec) in the prg is not fully
>optimized. I try different ways to SELECT but not success.
>
>Table : SaleList (180,000 records)
>Fields: iSaleId, dSaleDate,....
>Index : iSaleId, dSaleDate
>
>Table : SaleProd (340,000 records)
>Fields: iSaleId, iPdid, iSaleQty,....
>Index : iSaleId, iPdid
>
>SELECT SaleProd.iPdid, SUM(SaleProd.iSaleQty) AS TotalSale ;
>FROM SaleProd, SaleList ;
>WHERE SaleProd.iSaleId = SaleList.iSaleId .AND. ;
> SaleList.dSaleDate >= CTOD("14/07/96") ;
>GROUP BY SaleProd.iPdid
>

two possibilities for improvement :

1. replace >= CTOD("14/07/96") with >= {14/07/96}

2. or leave the WHERE condition as is and have an index on CTOD(dSaleDate) instead of dSaleDate


hoping this helps