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.