Yau,

Ok, that's a pretty complex query. 4 queries actually. Have you tried running each one independently to see if any one of them is grossly affecting the performance?

> and a.order_status="C" ;
> and b.order_q=0 );
> and a.pr_sign="Y" ;

You probably want an indexes on order_status, order_q and pr_sign

What is fheader.custcode ? a single row cursor? an object?

You are joining tsorder to tarmast, but no fields from tarmast are in the result? Why is tarmast in the query then?

DISTINCT and IN can be a real performance hog. Perhaps you can pull your two inner selects out as seperate queries.

Your where clause contains a calculated expression that might be better handled testing one field against the other.

Personally I hate using aliases inside selects, in your case you are reusing them and I'd just be afraid of things getting confused.

Here's how I'd write this just to see if it works any better:

select distinct tsorderdtl.hkey ;
   from  tsorderdtl
   inner join tsorder ;
      on tsorderdtl.hkey == tsorder.hkey;
         and tsorder.custcode=fheader.custcode;
   into cursor orderspending nofilter ;
   where tsorderdtl.order_q > tsorderdtl.delivered_q 

select distinct tsorderdtl.hkey ;
   from tsorderdtl ;
   inner join tstokmast ;
      on tsorderdtl.prodcode == tstokmast.prodcode and tstokmast.pr_sign = "Y"
   inner join tsorder ;
      tsorderdtl.hkey==tsorder.hkey and tsorder.custcode=fheader.custcode;
   into cursor zeroorders nofilter ;
   where tsorderdtl.order_q = 0

select 0 as pf, tsorder.hkey, tsorder.sorder_no, tsorder.sorder_date, spac(10) as cust_name,;
       spac(10) as supp_name, tsorder.cust_ref, tsorder.order_status ;
   from tsorder ;
   inner join orderspending ;
      on tsorder.hkey = orderspending.hkey ;
   where tsorder.order_status="C" ;

union ;

select 0 as pf, tsorder.hkey, tsorder.sorder_no, tsorder.sorder_date, spac(10) as cust_name,;
       spac(10) as supp_name, tsorder.cust_ref, tsorder.order_status ;
   from tsorder ;
   inner join zeroorders ;
      on tsorder.hkey = zeroorders.hkey ;
   where tsorder.order_status="C" ;

   order by 3 ;
   into cursor clist

You can also use sys(3054) to show you VFP query plan and indexes it'll use.

>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.