> >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 > Christian, The index "dSaleDate" was used to optimize table SaleList and the optimization level was "full". The problem seems not related to field dSaleDate. The index "iSaleId" of table SaleProd and SaleLIst were not used and the optimization level of table saleProd was "none". I don't know why SQL didn't pick index "iSaleId" to optimize table SaleProd in the above SELECT statment. In the third SELECT statment in my last message, "iSaleId" was used. But I will try your suggestion when I go home. Thanks, S H