>>>What is wrong here?
>>>
>>>Have data (FoxPro tables) on a remote server and when executing a SQL through a .EXE ( using a stored procedure ) the resultant is 50 records equaling about a file size of 12K dbf yet we see over 25 megs. coming over the network? It wasn't until we put a sniffer on the network because of slow performance that this came to view. Comparing this to execting a SQL from the application showed no detecable difference. I was always under the impression that using stored proceedures only the resultant record set would be delivered and for that manner the same when execting a SQL from the client side.
>>
>>For a start, read my FAQ #8109, which has some ideas on query optimization. Also, post the actual query statement(s) here, so that we can help you.
>>
>>Hilmar.
>
>Here is the original query and one that now only passes 2meg.
>IF have tags: ALLT(wh_stat) ALLT(remoteunit) ALLT(site) but NO DELE() tag.. Early on the specs for this table should never have any deleted records. Subsiquently, it now does which is roughly 20%. In a test mode, I added a deleted key and did not see any difference. I do have SET DELE ON and in the last SQL statement here I did try using FORCE which again hasn't shown any differences. Key in mind I am passing ndispvalu='TOP 50 ' as this value is changeable by users. One other thing, This same query runs much faster when I filter for only one date whice has a key instdate.
>
>
>It was advise to SET OPTIMIZE OFF to disable rushmore and not pass any indexes back to the client. I tired it and couldn't detect any difference in the network overhead. I am still confused and would appreciate clarification that if I am requesting a record set, why should anything more than that (other than normal network overhead) be sent to the client ?
>
>ndispvalu='TOP 50 '
>cccsite='5'
>
>*** This the original query which get hosed on the network...
>
>* selethis=[SELE ]+ndispvalu+[ site,instdate,idlc,len,rcntelno,wh_user,wh_stat,wh_comp,action,RECN() AS recn,rsu_no,lastassgn ]+;
> [FROM work_que WHERE ALLT(remoteunit)="MIP" .AND. !ALLT(SUBS(wh_stat,1,1)$"C,X" .AND. ALLT(site)="]+cccsite+[" INTO CURS sw_curs ORDE BY 2]
>&selethis
>
>*** This following SQL passes 2meg. in test network versus 25 meg. for the previous SQL.. But still why 2meg. when the result is 50-records of may 12k size ??
>
>selethis=[SELE ]+ndispvalu+[ site,instdate,idlc,len,rcntelno,wh_user,wh_stat,wh_comp,action,RECN() AS recn,rsu_no,lastassgn ]+;
> [FROM FORCE work_que WHERE ALLT(remoteunit)="MIP" .AND. ALLT(wh_stat)!="C" .AND. ALLT(wh_stat)!="CX" .AND. ALLT(wh_stat)!="XX" .AND. ALLT(wh_stat)!="XC" .AND. ALLT(site)="]+cccsite+[" INTO CURS sw_curs ORDE BY 2]
>&selethis

My guess is that the whole Where is executed, transferred over the network, the ORDER is done, and finally the top 50 are shown

Suspect that if you were to drop the TOP 50, the result would be around 2 meg

What you can try is split it in two queries

&& (1)

Select  Top 50 ;
    SomeExpression as PrimaryKey, ;
    instdate, ;
    RECN() AS recn ;
    from work_que ;
    into cursor tmp ;
    where   ( ALLT(remoteunit)="MIP" ) ;
        and ( ALLT(wh_stat)!="C" ) ;
        and ( ... ) ;
    order by 2

&& (2)
Select site,;
      instdate, ;
      idlc, ;
      len, ;
      rcntelno, ;
      wh_user, ;
      wh_stat, ;
      wh_comp, ;
      action, ;
      recn, ;
      rsu_no, ;
     lastassgn ;
    From tmp,  work_que ;
    into cursor sw_curs ;
    Where ( SomeExpression = PrimaryKey ) ;
   order by 2