>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
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.
