ADABAS performance improvement

, , , ,
1

Hi,
Currently i am working on improving performance of a Natural program, which read the whole ADABAS file and updates few records on certain condition ( there is like more if’s in the program). Currently the ET is given using a counter (working variable), if the count goes above 50. The program basically reads the whole ADABAS file (which has 13 mil + records) and issue update for the valid records. Is there any best practice for this kind of requirement.

2

If the number of records to be updated is very small, try this program structure:

R.
READ MULTI-FETCH ON
    view BY descriptor
  IF  condition
    THEN
      G.
      GET view
      ASSIGN view.field = ...
      UPDATE (G.)
      ADD 1 TO #ET
  END-IF
  IF  #ET           > 50
     OR *COUNTER (R.) > 2000
      THEN
        RESET #ET
              *COUNTER (R.)
        END TRANSACTION
      END-IF
  END-IF
END-READ
END TRANSACTION

The GET statement breaks the rule of “don’t retrieve a record that you already have,” but it allows for Multi-fetch which will result in overall performance improvement.

3

Go with Ralph. He gave you the perfect suggestion.

4

Just a performance note.

If by “few records” you mean Very Few, (say 50 records out of the 13 million you mentioned), you could simply issue an ET after every UPDATE. This will save you many IF statements which test #ET or *COUNTER, and many RESETs.
Assuming the 50 records are scattered through the file, you will also issue far fewer ET’s. (50 rather than thousands (every 2000 records from the file of 13 million)).

Of course, if the number is on the order of 50, or 100, or even a thousand, it would be good if you could use some code to minimize the records being read. Perhaps a descriptor or super descriptor that limits the records that have to be read could be added to the file.

5

It was my understanding that when Natural executes an END TRANSACTION statement, an ET is issued only if the program is in ET status, so the program will issue at most 50 ETs in your scenario, not thousands. And if two or more UPDATEs occur in a single 2000-record block, then the number of ETs would be fewer than 50. So the question becomes “How many IFs and RESETs can I execute in the same elapsed time as an ET?”

6

First, mea culpa, in my last post, when I said

I should have said something like;

, … not ET’s.

Back to the discussion, here is a program that demonstrates the time for END TRANSACTIONS

DEFINE DATA LOCAL
1 #LOOP (I4)
1 #LOOP2 (I4)
1 #NUMBER (I2)
1 #CPU-START (I4)
1 #CPU-ELAPSED (I4)
1 EMP VIEW OF EMPLOYEES
2 NAME
2 FIRST-NAME
END-DEFINE
*
INCLUDE AATITLER
INCLUDE AASETC
*
MOVE *CPU-TIME TO #CPU-START
SETA. SETTIME
FOR #LOOP = 1 TO 100
READ EMP
END-READ
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-START
WRITE 3/10 ‘NO END TRANSACTION LOOP TIME==>’ *TIMD (SETA.) #CPU-ELAPSED /
*
MOVE *CPU-TIME TO #CPU-START
SETB. SETTIME
FOR #LOOP = 1 TO 100
READ EMP
END TRANSACTION
END-READ
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-START
WRITE 3/10 ‘NULL END TRANSACTION LOOP TIME==>’ *TIMD (SETB.) #CPU-ELAPSED /
*
END

PAGE #   1                    DATE:    16-10-10
PROGRAM: ET01                 LIBRARY: BOSTON01



     NO END TRANSACTION LOOP TIME==>        7          38



     NULL END TRANSACTION LOOP TIME==>        9          54

I used 100 iterations of the READ loops to produce meaningful output. On my laptop, the output above is very consistent (a few outliers in the CPU times).

Percentage wise, the difference is fairly substantial. My guess is that the code and variables for END TRANSACTION are external to the code for the program, and hence we are seeing the overhead of what is basically a CALLNAT.

Next, I added a third loop. To keep things comparable, I changed the FOR loop to 99 iterations and added an iteration with an UPDATE statement (null, nothing changed).

DEFINE DATA LOCAL
1 #LOOP (I4)
1 #LOOP2 (I4)
1 #NUMBER (I2)
1 #CPU-START (I4)
1 #CPU-ELAPSED (I4)
1 EMP VIEW OF EMPLOYEES
2 NAME
2 FIRST-NAME
END-DEFINE
*
INCLUDE AATITLER
INCLUDE AASETC
*
MOVE *CPU-TIME TO #CPU-START
SETA. SETTIME
FOR #LOOP = 1 TO 100
READ EMP
END-READ
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-START
WRITE 3/10 ‘NO END TRANSACTION LOOP TIME==>’ *TIMD (SETA.) #CPU-ELAPSED /
*
MOVE *CPU-TIME TO #CPU-START
SETB. SETTIME
FOR #LOOP = 1 TO 100
READ EMP
END TRANSACTION
END-READ
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-START
WRITE 3/10 ‘NULL END TRANSACTION LOOP TIME==>’ *TIMD (SETB.) #CPU-ELAPSED /
*
MOVE *CPU-TIME TO #CPU-START
SETC. SETTIME
FOR #LOOP = 1 TO 99
READ EMP
END TRANSACTION
END-READ
END-FOR
*
READ EMP
AT END OF DATA
UPDATE
END TRANSACTION
END-ENDDATA
END-READ
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-START
WRITE 3/10 ‘NULL UPDATE TRANSACTION LOOP TIME==>’ *TIMD (SETC.) #CPU-ELAPSED /
*

END

PAGE #   1                    DATE:    16-10-10
PROGRAM: ET02                 LIBRARY: BOSTON01



     NO END TRANSACTION LOOP TIME==>        7          43



     NULL END TRANSACTION LOOP TIME==>        8          57



     NULL UPDATE TRANSACTION LOOP TIME==>        9          65

My guess here is that Natural “sees” the UPDATE statement and does send an ET command to Adabas. I will play a bit more with this program; in particular, I will up the number of iterations. The disparity between the last two loops is a bit strange.

The programs above were run on my PC. Eventually (hopefully tomorrow) I will also run code on the mainframe.

7

I made this statement in this Mainframe forum. Steve’s timings were produced with Natural for Windows where ETs are issued regardless of ET status, so longer elapsed times for the ET loops should be expected. (The same goes for Natural for Linus/Unix.)