>>If you have an index on the combined expression, a SEEK on the combined expression would be extremely fast. On the other hand, this index would not be used to optimize the SELECT statement as you posted it. For example, an index on Field1 + Field2 would only be used if you include a
WHERE Field1 + Field2 = ...". IOW, the index has to exactly match part of the WHERE clause, or all of it.
>>
>>>Well, as there could be many J rec's with the same RTE_NO + RTE_LET + DIRECTN (c.f. Composite PK), but, of course, incrementally differnnet SEQNOs, do you mean I should knock off the PK?
>>
>>No, apart from the fact that you need it, the primary key has unique value, and will cause no problem. I mean to knock of indices that have only a few possible values.
>
>So now I've changed it to:
>
>>...
>Set Order To RGDSNum
>...
>SCAN
> lcRteNo = PADR( RTE_NO, 6)
> lcDirectn = DIRECTN
> lnSeqNo = SEQNO
> lnNumber = NUMBER
> lcStopNo = PADR( STOP, 8)
> lcCounty = PADR( COUNTY, 2)
>
> Select DISTINCT TIME from RZStop ;
> where PADR( RZStop.RTE_NO, 6) == lcRteNo ;
> and RZStop.RTE_LET == "A" ;
> and RZStop.DIRECTN == lcDirectn ;
> and STR( RZStop.SeqNo, 4) == lcSeqNo ;
> 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
> If _TALLY = 0
> ...
>
>
>... and it does indeed go like sh*t off a chrome shovel, that's been lubricated with buckminster fullerase :-).
>
>I get no reported anomolies in my error report table (which is toi be expected but I'm not sure if it is correct). But the main thing is it goes through the whole cursor super-fast, does each SQl and gets out without a C5.
>
>So way to go, Hilmar and thanks very much. I'll be looking at the import app's other loops now to see if I can optimise similarly.
>
>Terry
With the PADR(zz,x) expression, there is no need for the dounle equal signs, a single equal sign will give the same result. But it won't hurt either.
