home   about us   contact us  
Distribtution
Financial
Appx
News
Search site for:




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 LOOP
It 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 ACTV
This 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 NO
This 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 NO
Here 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 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.


Do you have a tip you want to pass on? Contact Us.


« Return



© Copyright 2009 - C.A.N.S.Y.S. West Limited All Rights Reserved