SELECT Optimization

Now and then I must look at how long processes are running. Our web applications should be all running in under 500 milliseconds even though there are a lot of records in the database. An important datatable loading program on our web form was taking upwards of 2 seconds to load. I tested it on our development machine and with the client's data and the same number of indexes (7) and found our SELECT times were about the same. The PICKUP.DATE and ORIGIN.TERM are all indexed fields. Obviously, the SELECT optimizer does not work like I think it does.

Here is the test code abbreviated that runs in 643 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"'          
     LINE := ' AND WITH ORIGIN.TERM = "':TERMINAL.NO:'"'
     EXECUTE LINE CAPTURING OUTPUT
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
     REPEAT

Here is the 7 times faster code that runs in 89 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"' 
     EXECUTE LINE CAPTURING OUTPUT 
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
        IF LD.ITEM<7> # TERMINAL.NO THEN CONTINUE
     REPEAT

Posted: 07-14-2023 11:27

------------------------------
Doug Averch
Owner
U2 Logic
------------------------------

Doug,

If on UniVerse then the EXPLAIN keyword on a SELECT can be useful. You may also get the ability to control some of the optimisation process by using parentheses.

Regards

JJ

Now and then I must look at how long processes are running. Our web applications should be all running in under 500 milliseconds even though there are a lot of records in the database. An important datatable loading program on our web form was taking upwards of 2 seconds to load. I tested it on our development machine and with the client's data and the same number of indexes (7) and found our SELECT times were about the same. The PICKUP.DATE and ORIGIN.TERM are all indexed fields. Obviously, the SELECT optimizer does not work like I think it does.

Here is the test code abbreviated that runs in 643 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"'          
     LINE := ' AND WITH ORIGIN.TERM = "':TERMINAL.NO:'"'
     EXECUTE LINE CAPTURING OUTPUT
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
     REPEAT

Here is the 7 times faster code that runs in 89 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"' 
     EXECUTE LINE CAPTURING OUTPUT 
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
        IF LD.ITEM<7> # TERMINAL.NO THEN CONTINUE
     REPEAT

Posted: 07-14-2023 11:27

------------------------------
Doug Averch
Owner
U2 Logic
------------------------------

I guess my parents should have not spent the money on the computer business degree because this does not mean anything to me!

First Select:

SELECT MOVE.LOADS WITH PICKUP.DATE GE "07/01/2023" AND WITH PICKUP.DATE GE "07/09/2023" EXPLAIN
Optimizing query block 0
Tuple restriction: F4 >= '20279' AND F4 >= '20271'

Driver source: MOVE.LOADS
Access method: file scan

UniVerse/SQL: Press any key to continue or 'Q' to quit

Second Select:

SELECT MOVE.LOADS WITH PICKUP.DATE GE "07/01/2023" AND WITH PICKUP.DATE GE "07/09/2023"  AND WITH ORIGIN.TERM = "059" EXPLAIN
Single-variable predicates processed in index:
F7 = '059'
Optimizing query block 0
Tuple restriction: F4 >= '20279' AND F4 >= '20271'

Driver source: MOVE.LOADS
Access method: select list 0 with 189102 ids

UniVerse/SQL: Press any key to continue or 'Q' to quit

Posted: 07-17-2023 17:37

------------------------------
Doug Averch
Owner
U2 Logic
------------------------------

Now and then I must look at how long processes are running. Our web applications should be all running in under 500 milliseconds even though there are a lot of records in the database. An important datatable loading program on our web form was taking upwards of 2 seconds to load. I tested it on our development machine and with the client's data and the same number of indexes (7) and found our SELECT times were about the same. The PICKUP.DATE and ORIGIN.TERM are all indexed fields. Obviously, the SELECT optimizer does not work like I think it does.

Here is the test code abbreviated that runs in 643 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"'          
     LINE := ' AND WITH ORIGIN.TERM = "':TERMINAL.NO:'"'
     EXECUTE LINE CAPTURING OUTPUT
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
     REPEAT

Here is the 7 times faster code that runs in 89 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"' 
     EXECUTE LINE CAPTURING OUTPUT 
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
        IF LD.ITEM<7> # TERMINAL.NO THEN CONTINUE
     REPEAT

Posted: 07-14-2023 11:27

------------------------------
Doug Averch
Owner
U2 Logic
------------------------------

Now and then I must look at how long processes are running. Our web applications should be all running in under 500 milliseconds even though there are a lot of records in the database. An important datatable loading program on our web form was taking upwards of 2 seconds to load. I tested it on our development machine and with the client's data and the same number of indexes (7) and found our SELECT times were about the same. The PICKUP.DATE and ORIGIN.TERM are all indexed fields. Obviously, the SELECT optimizer does not work like I think it does.

Here is the test code abbreviated that runs in 643 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"'          
     LINE := ' AND WITH ORIGIN.TERM = "':TERMINAL.NO:'"'
     EXECUTE LINE CAPTURING OUTPUT
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
     REPEAT

Here is the 7 times faster code that runs in 89 milliseconds

     TERMINAL.NO = "059" ; BEGIN.DATE = "06/24/2023" ; END.DATE = "06/30/2023"
     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH PICKUP.DATE GE "':BEGIN.DATE:'"'       
     LINE := ' AND WITH PICKUP.DATE LE "':END.DATE:'"' 
     EXECUTE LINE CAPTURING OUTPUT 
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
        IF LD.ITEM<7> # TERMINAL.NO THEN CONTINUE
     REPEAT

Posted: 07-14-2023 11:27

------------------------------
Doug Averch
Owner
U2 Logic
------------------------------

Hi Doug,

The field ORIGIN.TERM is indexed, the the select get all ids from index the perform a scan to pickup.date.

I suggest you build a dict which concat the ORIGIN.TERM and the PICKUPDATE like ORIGIN.TERM:'-':PICKUP.DATE as ORIGPICK

At select, you query like this : WITH ORIGPICK GE ":TERMINAL.NO:'-':BEGINDATE:" AND ORIGPICK LE ":TEMINAL.NO:'-':ENDDATE:" 

I hope this help.

manu

Manu, I have built many complex I-descriptors years ago to solve the SELECT speed issues but I have not lately. As a matter of fact, I removed most of them when we moved our base code from Unidata to Universe. I, then, had to optimize each SELECT by hand that were problematic. Your proposal piqued my curiosity so I created a dictionary and built an index on it. Below is the code I used and it ran in about 20 milliseconds:

     LINE = 'SELECT MOVE.LOADS' 
     LINE := ' WITH TEST GE "':TERMINAL.NO:"_":ICONV(BEGIN.DATE,'D'):'"'       
     LINE := ' AND TEST LE "':TERMINAL.NO:"_":ICONV(END.DATE,'D'):'"'  
     EXECUTE LINE CAPTURING OUTPUT
     LOOP WHILE READNEXT ID
        READ LD.ITEM FROM MOVE.LOADS,ID ELSE CONTINUE
     REPEAT

Posted: 07-24-2023 14:16

Hi doug,

You ask for optimization :-) 

You could still scrape a few milliseconds by exploiting BSCAN directly and a cache on the filevar. 

Then you avoid  EXECUTE push/pop , SELECT engine start, OPEN file, OPEN index, read DICTionaries, analyse DICTionaries, ... )

With kind regards

I was going to implement your combined index in my test system.

However, I noticed something on LIST.INDEX MOVE.LOADS ALL

Can you see it?

LIST.INDEX MOVE.LOADS ALL
Alternate Key Index Summary for file MOVE.LOADS
File........... MOVE.LOADS
Indices........ 7 (0 A-type, 0 C-type, 4 D-type, 3 I-type, 0 SQL, 0 S-type)
Index Updates.. Enabled, No updates pending

Index name      Type  Build    Nulls  In DICT  S/M  Just Unique Field num/I-type
ORIGIN.TERM      D    Not Reqd  No     Yes      S    R     N    7
MASTER.INDEX     I    Not Reqd  No     Yes      S    L     N    IF AVAILABLE AND
                                                                 MASTER.LOAD THE
                                                                N MASTER.LOAD EL
                                                                SE ""
DRIVER1.AVAILABL I    Not Reqd  No     Yes      S    R     N    IF AVAILABLE THE
                                                                N DRIVER1 ELSE '
                                                                '
AVAILABLE        I    Not Reqd  No     Yes      S    R     N    IF (DISPATCH.DAT
                                                                E = '' AND CANCE
                                                                L.DATE = '') THE
                                                                N 1 ELSE ''
DRIVER1          D    Not Reqd  No     Yes      S    R     N    15
DRIVER2          D    Not Reqd  No     Yes      S    R     N    16
PICKUP.DATE      D    Not Reqd  No     No       S    L     N    4

The dictionary PICKUP.DATE says it is not in the DICT.

Our test machine, our development machine, and our live machine all had the same problem.

I deleted the index and rebuilt it and it now runs in 70 milliseconds and the second runs in 460 milliseconds.

Hi,

LIST.INDEX In Dict, says it does not found the definition of the field into the dict file, it's not a problem to run the index. 

do you use a remote dict via 'USING' or 'DF' file ? 

are you happy with the optimization ?