Boosting Query Speed

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.

« Return




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

[back to top]