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 the tip below from Bruce Johnston.
Boosting Query Speed - Alternative Method
Background: When an APPX query is run, 2 memory files are created to store the information about what fields to sort on and what fields to select on. These are the QSORT and QSLCT files respectively. Bruce's approach is to read these files to see if a particular non PCF file is required, and if not, completely skip the overhead of reading the associated records. In Bruce's example, he is checking to see if the DAR CUSTOMER file is used in the query (for either sorting or selecting) before going through the overhead of reading the DAR CUSTOMER table.
Here's Bruce's tip:
Create three domains in your main app, for example:
WORK QSLCT ACTV LOGIC y/n WORK QSLCT APP ALPHA X(3) WORK QSLCT FLD ALPHA X(22)Now create three work variables in your main app, for example:
WORK TEST QSLCT ACTV DOMAIN y/n WORK TEST QSLCT APP DOMAIN X(3) WORK TEST QSLCT FLD DOMAIN X(22)Use the domains you defined in the first step. Mine are subprocess 'cause I use them all within the same process but yours might have to be RELATED of even DETACHED. Here's the main subroutine that I use called TEST FOR ACTIVE QSLCT ENTRIES:
SET DTR WORK TEST QSLCT ACTV = 0 BEG READ --- QSLCT HOLD 0 KEY IS QSLCT KEY IF --- QSLCT ACTV EQ 1 T IF --- QSLCT AP ID L EQ DTR WORK TEST QSLCT APP T AND --- QSLCT FLD NAM L IN DTR WORK TEST QSLCT FLD TT IF --- TEXT AT POSITION EQ 1 TTT SET DTR WORK TEST QSLCT ACTV = 1 TTT GOTO :EXIT QSLCT READ LOOP END READ --- QSLCT LABEL :EXIT QSLCT READ LOOPIt simply reads through the runtime 0LA QSLCT file searching for an Active entry for AP ID L and FLD NAM L in question. Active entries are those that had non-blank specs AFTER the user was through with them. DANGER: I assume that only ONE query is being invoked at a time in the current job. You would have to add two more work fields WORK QRY AP and WORK QRY NAM to distinguish between more than one.
Now define a work variable like WORK QSLCT ON CUSTOMER, DOMAIN y/n pointing back to the logical domain in your main app. Next, create a subroutine called TEST FOR QSLCT ON CUSTOMER with the following but with your specific info:
SET DTR WORK TEST QSLCT APP = DAR SET DTR WORK TEST QSLCT FLD = CUSTOMER GOSUB DTR TEST FOR ACTIVE QSLCT ENTRIES SET DAR WORK QSLCT ON CUSTOMER = DTR WORK TEST QSLCT ACTVThis routine sets the 2 work fields to tell the TEST FOR ACTIVE QSLCT subroutine which application and file name we are interested in. Finally, in any START OF QRY EXECUTION put in
COPY DAR TEST FOR QSLCT ON CUSTOMER
It is essential that this be a COPY, as nested GOSUBs within Start of Query Execution can cause problems in APPX 3.5 - 4.05. This routine will set the DAR WORK QSLCT ON CUSTOMER field to tell whether or not the CUSTOMER file was used in the Record Selection Screen of the Query. What I do is something like the following in Pre-User Selection
IF DAR WORK QSLCT ON CUSTOMER EQ 1 AND DAR CUSTOMER NO NE DSA SALES CUSTOMER NO T SET DAR CUSTOMER NO EQ DSA SALES CUSTOMER NO T READ DAR CUSTOMER KEY IS CUSTOMER NOThis means that I ONLY read the secondary file CUSTOMER if I'm selecting on a field in DAR whose name starts with "CUSTOMER" AND the value of the DSA SALES CUSTOMER NO has just changed. Note that this is most useful if you've followed the APPX standards suggestions and have all of the fields within a file start with the filename itself... :-)
So far, we have only dealt with checking to see if a field was used on the Record Selection Screen. We also need to check if the field was included in the sort via the Sort Order screen. Go through from the beginning and design transfer and replace all QSLCT with QSORT. In the Post-User Selection Event Point, put the following code:
IF DAR WORK QSORT ON CUSTOMER EQ 1 AND DAR WORK QSLCT ON CUSTOMER EQ 0 AND DAR CUSTOMER NO NE DSA SALES CUSTOMER NO T SET DAR CUSTOMER NO EQ DSA SALES CUSTOMER NO T READ DAR CUSTOMER KEY IS CUSTOMER NOHere we first test to see if the user is sorting on a field in the CUSTOMER file, and if so, we further check to see if they also selected on it. If they selected on a field from CUSTOMER, we don't have to read the record here as we already read it in the Pre-User Selection. If they didn't select on it, but they are sorting on it, then we need to read the CUSTOMER file here.
Now all of my large Queries only read "secondary" files when necessary.
Boosting Query Speed - Part 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 YTDPOSTRemember 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.
Do you have a tip you want to pass on? Contact Us.