Boosting Query Speed

If you have a Query that reads secondary files for additional sorting or selecting criteria, there are a couple of techniques that can make the query run faster. The first one is simple to implement, and only requires a few lines of code. The technique is to check to see if you already have the record before you go through the overhead of reading it. For example, assume we are using ORDER2 (Order Line Items) as the PCF to the Query, and we also need fields from the parent record (ORDER1). The common approach is to simply read the ORDER1 record in the Pre-User Selection Event Point, but rather than unconditionally reading the ORDER1 record, check to see if we already have the right record:

     IF   XXX ORDER1 ORDER NO    NE   XXX ORDER2 ORDER NO
T    SET  XXX ORDER1 ORDER NO    =    XXX ORDER2 ORDER NO
T    READ XXX ORDER1        KEY IS ORDER1 ORDER NO

Since we are likely to get several ORDER2 records in a row for the same Order number, this will speed up the Query by reducing the number of read into ORDER1.

A more advanced approach would be to eliminate the reads to ORDER1 completely, if the user is not sorting or selecting on fields from ORDER1. How can you tell if this is the case? Check out this tip from Bruce Johnston @ Safety Kleen.


Boosting Query Speed II

Release 4.1 introduced improved query execution by automatically using an index if a record selection used a field that was a key or alternate key. However, it does not limit query records read when the right hand side of the record selection is a field as opposed to a value. This makes sense, APPX would not know if the value of the field on the right hand side would change during the execution. If you know that the RHS won't change during execution, then you can manually put the code to read the records in the 'Establish PCF Range' Event Point of the Query. For example, the following code would read YTDPOST, but only for the current fiscal year:

    *        Assume TGL PARAM has already been read
BEG AT   TGL YTDPOST  IN TGL PARAM FISCAL YEAR
END AT   TGL YTDPOST  IN TGL PARAM FISCAL YEAR
BEG READ TGL YTDPOST                HOLD 0 KEY IS  YTDPOST FISCAL YEAR
GOSUB    --- PROCESS QUERY RECORD
END READ TGL YTDPOST

Remember that putting your own code in this even point will override anything APPX might do, so be sure your code is the best way to do it.





For additional information, contact tips@cwi-appx.com

[back to top]