Optimization Techniques in Natural & Adabas - FROM-TO

Most often as developers we think what difference will it make if we have 1 additional call to the database, nobody is going to know or is it gonna effect the process in anyway. Is it? Then you are in for a rude shock, the bitter truth is it will - if its a frequently used module and called by multiple process we end up making an extra call even if the code as simple as below:

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM EMP CITY
0070     STARTING FROM 'VEGAS'
0080   IF CITY NE 'VEGAS'
0090     ESCAPE BOTTOM
0100   END-IF
0110   ADD 1 TO #I
0120   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0130 END-HISTOGRAM
0140 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0150 END

In the above code we know there are only 10 records for City names as "VEGAS" and we assume that it will make only 1 Adabas call when we use HISTOGRAM. But Running DBLOG for it, we get as follows:

_    1 L9     1    97                         10  -?? 00600101    EMPHIS   0060
_    2 L9     1    97                          1  -?? 00600101    EMPHIS   0060
_    3 RC     1    97                             -?? 00600101 SI EMPHIS   0090
_    4 RC     1                                       00000000 F  EMPHIS   0150

Did you see that? It made an extra call "L9 @ line 2" to see if we have exahausted all records with value "VEGAS". So even if you intended to read only "VEGAS", it went and read the next record after VEGAS which is "VENEZUELA". This brings us to the introduction of FROM & TO clause in READ LOGICAL/FIND/HISTOGRAM Statements. For clarity purpose i will be using HISTOGRAM in all the examples but is applicable to other database access statements like READ/FIND. In the following discussion we will try various permutation and combination when the "FROM-TO" option is used and which one helps the program in a better way.

HISTOGRAM USING "FROM & TO" - VALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM EMP CITY
0070     STARTING FROM 'VEGAS' TO 'VEGAS' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

The results is:
CITY: VEGAS                CNT :           1 NMBR:          10
NUMBER OF RECORDS WITH CITY VEGAS           1

Running DBLOG for it, we get it as follows:

_    1 L9    15    53                          1  ??? 00100101    EMPHIS  0010
_    2 L9    15    53    3                        ??? 00100101    EMPHIS  0010
_    3 RC    15                                       00000000 F  EMPHIS  0070

Look at the impact. It cut that extra call to the database within a snap, just by coding the "TO" clause. Now let use see what the impact is if the Record is not existing in the database.

HISTOGRAM USING "FROM & TO" - INVALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM EMP CITY
0070     STARTING FROM 'VDGAS' TO 'VDGAS' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

The results is:NUMBER OF RECORDS WITH CITY VEGAS           0

Running DBLOG for it, we get the following result set.

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53    3                        ??? 00100101    EMPHIS  0010
_    2 RC    15                                       00000000 F  EMPHIS  0070

Well well, we made 1 more call lesser! This is getting really intresting. If we had coded HISTOGRAM and then IF-ELSE with a ESCAPE BOTTOM, we would hit Adabas and made 4 calls as mentioned in the first example irrespective of whether the record exists in the database or not. Now let us see, what would it look like if we use HISTOGRAM(1) for a valid data and its impact on number of calls made.

HISTOGRAM(1) USING "FROM" ONLY - VALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(1) EMP CITY
0070     STARTING FROM 'VEGAS'
0080   IF CITY NE 'VEGAS'
0090     ESCAPE BOTTOM
0100   END-IF
0110   ADD 1 TO #I
0120   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0130 END-HISTOGRAM
0140 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0150 END

Running DBLOG for it, we get the result set as below:

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53                          1  ??? 00100101    EMPHIS  0010
_    2 RC    15    53                             ??? 00100101 SI EMPHIS  0010
_    3 RC    15                                       00000000 F  EMPHIS  0070

Hmm, intresting it made only one call to the database, but thats what we intended the program to do. Let us check its impact if we use "FROM-TO" option to see if there is any impact to it.

HISTOGRAM(1) USING "FROM & TO" - VALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(1) EMP CITY
0070     STARTING FROM 'VEGAS' TO 'VEGAS' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53                          1  ??? 00100101    EMPHIS  0010
_    2 RC    15    53                             ??? 00100101 SI EMPHIS  0010
_    3 RC    15                                       00000000 F  EMPHIS  0070

Well, no impact. It worked exactly the way we wanted and ran for the same time, used same cpu and did the same number of calls. So, we need to see if it will have any impact if a record value doesnt exists for the descriptor/super-descriptor.

HISTOGRAM(1) USING "FROM" ONLY - INVALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(1) EMP CITY
0070     STARTING FROM 'VENZUELA'
0080   IF CITY NE 'VENZUELA'
0090     ESCAPE BOTTOM
0100   END-IF
0110   ADD 1 TO #I
0120   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0130 END-HISTOGRAM
0140 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0150 END
0160

Running DBLOG for it, we get it as follows:

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53                          1  ??? 00100101    EMPHIS  0010
_    2 RC    15    53                             ??? 00100101 SI EMPHIS  0010
_    3 RC    15                                       00000000 F  EMPHIS  0070

No CHANGE!! Ok, this can be more intresting if we use a "TO" option.

HISTOGRAM(1) USING "FROM & TO" - INVALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(1) EMP CITY
0070     STARTING FROM 'VENZUELA' TO 'VENZ' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

Running DBLOG for it, we get the following information.

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53    3                        ??? 00100101    EMPHIS  0010
_    2 RC    15                                       00000000 F  EMPHIS  0070

Well, we finally get something different!!! 1 database call lesser. So that proves the fact that if the data is not existing a "FROM-TO" option better than a "FROM with ESCAPE BOTTOM" option. To make it more intresting let us see how a HISTOGRAM(3) with FROM-TO option behaves as opposed to HISTOGRAM(3) MULTI-FETCH OF 3 with FROM-TO option.

HISTOGRAM(3) USING "FROM & TO" - VALID DATA

0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(3) EMP CITY
0070     STARTING FROM 'V' TO 'WA' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

CITY: VANCOUVER            CNT :           1 NMBR:           1
CITY: VEGAS                CNT :           2 NMBR:          10
CITY: WARSAW               CNT :           3 NMBR:           1
NUMBER OF RECORDS WITH CITY VEGAS           3

Running DBLOG for it, we get the following information.

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 RC   133                                       00000000 F  ATEST    0220
_    2 L9    15    53                          2  ??? 00200101    EMPHIS  0020
_    3 L9    15    53                          1  ??? 00200101    EMPHIS  0020
_    4 L9    15    53                          1  ??? 00200101    EMPHIS  0020
_    5 RC    15    53                             ??? 00200101 SI EMPHIS  0020
_    6 RC    15                                       00000000 F  EMPHIS  0090

6 calls!! Let us check if a multi-fetch can help here? Let us see that as a final example.

HISTOGRAM (3) USING "MULTI-FETCH & FROM & TO" - VALID DATA
                                                                          
0010 DEFINE DATA LOCAL
0020 1 EMP VIEW OF EMPLOYEES
0030   2 CITY
0040 1 #I (I4)
0050 END-DEFINE
0060 HISTOGRAM(3) MULTI-FETCH OF 3 EMP CITY
0070     STARTING FROM 'V' TO 'WA' -H'FF'
0080   ADD 1 TO #I
0090   WRITE '=' CITY '=' *COUNTER '=' *NUMBER
0100 END-HISTOGRAM
0110 WRITE 'NUMBER OF RECORDS WITH CITY VEGAS' #I
0120 END

Running DBLOG for it, we get the following information.

M   No Cmd   DB   FNR  Rsp        ISN        ISQ  CID CID(Hex) OP Pgm      Line
_    1 L9    15    53                          2  ??? 00200101 M  EMPHIS  0020
_    2 L9    15    53                          1  ??? 00200101 <  EMPHIS  0020
_    3 L9    15    53                          1  ??? 00200101 <  EMPHIS  0020
_    4 RC    15    53                             ??? 00200101 SI EMPHIS  0020
_    5 RC    15                                       00000000 F  EMPHIS  0090

Well there you have proof of it. One more reduction of Adbas call!!

* Legacy used in the above descriptions

VALID DATA signifies Record found condition

INVALID DATA signifies Record NotF-Found condition