David,
Thanks.
Here's one of the queries that I'm trying to speed up as its taking almost 20 seconds to run over a Novell network. The tables each contain at least 1000 records each.
Begin >>
select 0 as pf, a.hkey, a.sorder_no, a.sorder_date, spac(10) as cust_name,;
spac(10) as supp_name, a.cust_ref, a.order_status ;
from tsorder a, tarmast b;
where b.custcode==a.custcode ;
and a.order_status="C" ;
and a.hkey in;
( select distinct b.hkey ;
from tsorderdtl b, tsorder c;
where b.hkey==c.hkey;
and c.custcode=fheader.custcode;
and b.order_q-b.delivered_q>0 );
union ;
select 0 as pf, a.hkey, a.sorder_no, a.sorder_date, spac(10) as cust_name,;
spac(10) as supp_name, a.cust_ref, a.order_status ;
from tsorder a, tarmast b;
where b.custcode==a.custcode ;
and a.order_status="C" ;
and a.hkey in;
( select distinct b.hkey ;
from tstokmast a, tsorderdtl b, tsorder c;
where b.prodcode==a.prodcode ;
and b.hkey==c.hkey;
and c.custcode=fheader.custcode;
and a.pr_sign="Y" ;
and b.order_q=0 );
order by 3 ;
into cursor clist
<< END
Indexes have already been created for custcode, hkey, prodcode fields in the tables above.
Hope you can shed some light for me.
Thanks
Yau