Tore

Further to my last reply to this message, I've now tried the following, with NO improvement. This is totally baffling me that something can be so slow. Weird thing is I got it to go really fast on Friday then it reverted to impossibly slow again, and I can't see what changed in between.

Terry

SCAN
  lcRteNo	= PADR( RTE_NO, 6)
  lcDirectn	= DIRECTN
  lcNumber	= STR( NUMBER, 3)
  lcStopNo	= PADR( STOP, 8)
  lcCounty	= PADR( COUNTY, 2)
  lcCompKey	= lcRteNo + "A" + lcDirectn			&& 21/03/05

  Select DISTINCT TIME from	RZStop ;
    where 	PADR( RZStop.RTE_NO, 6) + "A" + RZStop.DIRECTN = lcCompKey ;
    and	STR( RZStop.Number, 3)			          = lcNumber ; 
    and	PADR( RZStop.Stop, 8)			          = lcStopNo ;
    and	PADR( RZStop.County, 2)			          = lcCounty ;
    and	not EMPTY( RZStop.TIME) ;
    to SCREEN NOCONSOLE && Find any time value for this particular stop across all other sequences
  If _TALLY = 0		 && no hits on this stop # - i.e. all Time values empty
      ...

>To have this fully optimized, you need a lot of index tags.
>
> where PADR( RZStop.RTE_NO, 6) = lcRteNo ; && Tag on PADR(RTE_NO, 6)
> and RZStop.RTE_LET = "A" ; && Tag on RTE_LET
> and RZStop.DIRECTN = lcDirectn ; && Tag on DIRECTN
> and STR( RZStop.SeqNo, 4) = lcSeqNo ; && Tag on STR(SeqNo,4)
> and STR( RZStop.Number, 3) = lcNumber ; && Tag on STR(Number,3)
> and PADR( RZStop.Stop, 8) = lcStopNo ; && Tag on PADR(Stop, 8)
> and PADR( RZStop.County, 2) = lcCounty ; && Tag on PADR(County, 2)
> and not EMPTY( RZStop.TIME) ; && Tag on TIME
>
>But with only ANDs, you have an advantage. VFP will always start from the left, so it will first SELECT the records which match the first comparison, PADR(RZStop.RTE_NO,6) = lcRteNo. So it's important to have a tag on at least the first expression, and to have the first expression select as few records as possible. Then the records which are selcted/filtered out, will be compared against expression no. 2, and so on.
>