# ADABAS performance improvement **URL:** \ **Category:** Forum\ **Tags:** Adabas-Natural, Adabas, Adabas-on-Mainframes, Adabas-on-Windows-UNIX, Adabas-Basics-on-Mainframes\ **Created:** [August 29, 2016, 1:00pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424 "2016-08-29T13:00:49Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Ajay\_Madhavan](https://avatars.discourse-cdn.com/v4/letter/a/848f3c/32.png) [@Ajay\_Madhavan](https://techcommunity.softwareag.com/u/Ajay_Madhavan)\ **Post date:** [August 29, 2016, 1:00pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/1 "2016-08-29T13:00:49Z")
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. ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [August 29, 2016, 6:07pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/2 "2016-08-29T18:07:28Z")
If the number of records to be updated is very small, try this program structure: ```auto 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. ---
**Author:** ![Ch\_Mark](https://avatars.discourse-cdn.com/v4/letter/c/58956e/32.png) [@Ch\_Mark](https://techcommunity.softwareag.com/u/Ch_Mark)\ **Post date:** [October 5, 2016, 6:51am UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/3 "2016-10-05T06:51:43Z")
> [@Ralph Zbrog](#): > > If the number of records to be updated is very small, try this program structure: > > ```auto > 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. Go with Ralph. He gave you the perfect suggestion. ---
**Author:** ![Steve\_Robinson](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/steve_robinson/32/107_2.png) [@Steve\_Robinson](https://techcommunity.softwareag.com/u/Steve_Robinson)\ **Post date:** [October 7, 2016, 1:48pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/4 "2016-10-07T13:48:18Z")
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. ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [October 10, 2016, 6:47am UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/5 "2016-10-10T06:47:34Z")
> [@Steve Robinson](#): > > 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)). 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?” ---
**Author:** ![Steve\_Robinson](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/steve_robinson/32/107_2.png) [@Steve\_Robinson](https://techcommunity.softwareag.com/u/Steve_Robinson)\ **Post date:** [October 11, 2016, 9:56pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/6 "2016-10-11T21:56:23Z")
First, mea culpa, in my last post, when I said > [@](#): > > Assuming the 50 records are scattered through the file, you will also issue far fewer ET’s. I should have said something like; > [@](#): > > the program will issue far fewer END TRANSACTIONS , … 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. ---
**Author:** ![Ralph\_Zbrog](https://sea1.discourse-cdn.com/techcommunity/user_avatar/techcommunity.softwareag.com/ralph_zbrog/32/78_2.png) [@Ralph\_Zbrog](https://techcommunity.softwareag.com/u/Ralph_Zbrog)\ **Post date:** [October 11, 2016, 10:50pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/7 "2016-10-11T22:50:15Z")
> [@Ralph Zbrog](#): > > It was my understanding that when Natural executes an END TRANSACTION statement, an ET is issued only if the program is in ET status, … 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.) ---
**Author:** ![system](https://global.discourse-cdn.com/techcommunity/original/3X/c/d/cd19967bb605d28a72d77eb83a1cae22f3df2eb8.svg) [@system](https://techcommunity.softwareag.com/u/system)\ **Post date:** [April 2, 2021, 3:36pm UTC](https://techcommunity.softwareag.com/t/adabas-performance-improvement/190424/8 "2021-04-02T15:36:50Z")