An index has a fixed length. An index on alltrim() doesn't make sense (to VFP), and VFP may assign some arbitrary length (based on the length of this expression in the first record it encounters when creating the index).

Try to replace with an index on padr(expression, size), or padr(alltrim(expression), size), and use the selfsame expression in the query. Or forget about the alltrim() altogether.

HTH, 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