I would not have SQL do the math on pass 1, ie SUM(). Have SQL grab all the records and then sum them in a local situation. You may get 500% decrease in time, and a wait wind that changes text from "Fetching the blasted Data" to "Figuring the Bottom Line" looks like things are running faster.

Sele .... ;
into table allbill

index on person + bill

total on person + bill to table allbills.

In the past I found that asking SQL to sort and sum was slower than VFP in old xbase methods to do the same.

5-6 seconds is not that long for the volume you have.

Another way could be to combine 2 views in a sql statement. View1 could take Bill + person. View 2 could take line and provider.

use view1 nodata in 0
index on the join condition

use view2 nodata in 0
index on the join condition

sele * from view1, view2..... into table allbills

Always look at new ways to skin the cat!

__Stephen

---

>I'm doing a very simple sql statement that have 50,000- 60,000 records in tables bill, billbi and line. The person table has 15,000 records and the provider table has 1 record. It takes 5-6 seconds to do this query under WinNT 40 SP6a, P3-550 with 128MB Ram.
>There are indexes set on the join fields, as well as each field to be retrieved in the query.
>
>Is there any way to speed this query up ???
>
>select ;
>bill.billdate,;
>bill.billno,;
>bill.altbillno,;
>alltrim(person.lastname)+", "+alltrim(person.firstname) as person,;
>alltrim(provider.lastname)+", "+alltrim(provider.firstname) as provider,;
>sum((line.ppu*line.qty)) as amount distinct;
>from force;
>bill ;
>inner join line ;
>on line.fkbill=bill.pkguid ;
>inner join person ;
>on person.pkguid=bill.fkperson ;
>inner join provider ;
>on provider.pkguid=bill.fkprovider ;
>inner join billbi ;
>on billbi.fkbill=bctop.pkguid ;
>group by bill.billno ;
>into table AllBills
>
>Jamie.