The initial version of this program can be made just as efficient as the second improved example by including the KEEP_LAST parameter on the FETCH commands like this:
GROUP_BY NAME(#TRANS) FIELDS(#TRANNUM #TRANDATE #TRANTIME
#TRANTYPE #TRANDESC #TRANUSER #TRANSTATE #STATDESC)
SELECT FIELDS(#TRANS) FROM_FILE(TRANS)
FETCH FIELDS(#TRANS) FROM_FILE(STATES)
WITH_KEY(#TRANSTATE) KEEP_LAST(6)
FETCH FIELDS(#TRANS) FROM_FILE(TRNTYP)
WITH_KEY(#TRANTYPE) KEEP_LAST(20)
UPRINT FIELDS(#TRANS)
ENDSELECT
If the TRANS file contained 3 different state codes and 5 different transaction types then in the case where 10,000 TRANS records were processed the RDML program would do (10,000 + 3 + 5) = 10,008 database accesses. This is a net saving of 19,992 I/Os. This version of the program would probably run in about 1/3 of the time of the original version.
Refer to the FETCH command in this guide for more details of the KEEP_LAST parameter.
