>Hi Vixens
>
>The code below is taking forever to execute and doesn't necessarily give me the results I want. I was wondering if it were possible to speed it up (by an order of magnitude!) or possibly combine it all into one sexy SQL statement that runs like sh*t off a chrome shovel.
>
>Here's the background:
>
>Each Route is identified by a route no., generation letter, and direction (RTE_NO+RTE_LET+DIRECTN)
>Each route can have any number of Journeys, each id'd by the above and a sequence number.
>The Route has a set of stops, in the table RZStop, bearing the above FK + seq. no., and in a "presentation" order, given by the field NUMBER (sic). All the stops available to the route are in the RZStop rec's with seq. no. 1.
>Each Stop is id'd by a Stop No. + the ID of the county in which it resides (Stop nos may not be unique acroiss counties)
>Not all journeys need to have all the stops. e.g. some may be skipped at different times of the day.
>As a journey is created it gets a set of the stops pertaining to it, and a new seq. no., and each stop has a Time field filled in.
>The first Journey uses the "default" stops list in RZStop of seq. no. 1 - i.e. "shares" the Route's list. But if the 1st J doesn't use all the stops then some seq. 1 time values will be blank.
>
>Now, during my import application EVERY stop in a R should be "used" in at least 1 J, i.e. there should be a TIME value in at least 1 sequence. To check that the import has been totally successful I test for this and report any unused stops.
>
>I've wracked my brains but can't figure out a better way:
>
>

>Select RZSTOP
>Set Order To RGDSNum   && RTE_NO+RTE_LET+DIRECTN+STR(SEQNO,4)+STR(NUMBER,3)
>Locate
>
>* Get all the RZStop records for "Base line" (Seq. #1) with no TIME values.
>* If there is a TIME value in any seq. 1 then we know the stop is used.
>* __________________
>Select * From RZStop ;
>  where RZStop.SeqNo = 1 ;
>  and   EMPTY( RZStop.Time) ;
>  into CURSOR csrSubjRZStop ;
>  Order by Rte_No, Rte_Let, Directn, Number
>lnNoRecs		= _TALLY
>
>	
>* Go thru each one and see if ANY RZStop rec, any Seq. # of this RGD has not a Time
>* value attached.  This shows that such a stop is not actually used somewhere in
>* one of the route's Journeys.
>* __________________
>Select csrSubjRZStop
>
>SCAN
>  lcRteNo	   = ALLTRIM( RTE_NO)
>  lcDirectn = DIRECTN
>  lcStopNo  = ALLTRIM( STOP)
>  lcCounty  = ALLTRIM( COUNTY)
>  lnNumber  = NUMBER
>
>* each of these selects takes too much time and, as seen on an earlier thread, a C5 happens somewhere
>* _______________________________
>  Select DISTINCT TIME from	RZStop ;
>    where ALLTRIM( RZStop.RTE_NO)  == lcRteNo ;
>      and RZStop.RTE_LET	       == "A" ;
>      and RZStop.DIRECTN	       == lcDirectn ;
>      and	 ALLTRIM( RZStop.Stop)   == lcStopNo ;
>      and	 ALLTRIM( RZStop.County) == lcCounty ;
>      and	 RZStop.Number	       == lnNumber ;
>      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
>      && Code to report an "unused" stop
>  EndIf
>EndSCAN
>

>
>'ppreciate it.
>
>Terry

It seems it is the last SELECT statement that takes too long, right?

Do you have indices on the most important expressions? It might help if you have an index on fields that take many different values. It might also help if you DON'T have an index on fields that take only a few values, nor on DELETED().