How smart is the 'select' statement in QTP ?

[Latimer, Richard]

Listland,


We make extensive use of date ranged records in our billing application - eg
we record that a particular aeroplane was owned by customer a from 1 January
1993 to 30 July 1997 and customer b from 31 July 1997 to < some date well in
the future>.  This record is indexed on the aircraft registration
(ascending) and then the applies from date (descending). Code in the
maintenance screens ensures that there is only ever one 'active' record per
aircraft.

Lookups to get the customer for a particular flight are done by linking to
the aircraft registration and then selecting the record where the date of
flight is between the applies from and applies to date.  There was an
implicit assumption that the 'select' would not need to continue scanning
the file after the 'first' record since it's conditions would have been
satisfied . . . however I suspect this is not the case.

To date this method has worked well and run times are reasonable but we are
looking to extend the technique to another lookup - in this case the runway
and weather conditions applicable at the destination airport of a flight.

Our most traded aircraft has had 30 owners in the 10 years we keep so even
in the worst case the select only has to plough through that many records.
The new scenario has the potential to generate that many per day! !


My question is whether the select statement is smart enough to realise that
the date is indexed / sorted and that it can stop looking after it has found
the right 'handful' of records. ?

Alternatively does anyone have a better idea for handling the lookup ?


We are PowerHouse 6.07 on DB2/400


tia

Richard Latimer
Wellington MIS Manager
Airways New Zealand
Ph +64 4 471 4744
Fax +64 4 471 0395











**********************************************************************
This electronic message together with any attachments is confidential. If
you receive it in error: (i) you must not use, disclose, copy or retain
it; (ii) please contact the sender immediately by reply email and then
delete the emails. Views expressed in this email may not be those of the
Airways Corporation of New Zealand Limited
**********************************************************************