Multi-Fetch - FACTOR

Hi, I would like to know if someone has found out a “magic number” or a easy way to calculate the best number for multi-fetch factor. we can consider a simple read loop such as READ (starting from) or FIND.
tks.

There are varying opinions, but many/most consider 10 a very good number. It’s what I use. 90% reduction in calls. No need for calculations.

That said, I don’t recommend Multi-Fetch in READ or HISTOGRAM unless it includes the TO clause or I intend to read the entire file.

Hi Ralph,

thank you for your information, but I didn’t understand why you would not recommend to use multi-fetch with read, except with using TO. I have tested the sample that we have in SYSEXV and there it is used READ and FIND, and, I think the elapse-time is not the best way to measure the improvement. I also dont know if the multi-factor changes how much command it is issuing or being saved. On the enviroment that I am testing does not have neither APAS, Review nor TRIM.

Is there a rule for this factor? you said that you use 10 as the factor, in the V4-MULTI sample the factor is 555, but for the optimization process, it is not so good you try and run many number, this way you are also not saving CPU usage or MSU.

Is there a factor number that it would be worst than the original (with no multi-fetch), or it does not matter the number that you use, it always better than the original?

thank you.

READ MULTI-FETCH 100
     EMPLOYEES BY NAME FROM 'RALPH'
  IF  NAME > 'RALPH'
    THEN
    ESCAPE BOTTOM
  END-IF

The code above will fetch from 1 to 100 records after the last RALPH.

READ MULTI-FETCH 100
     EMPLOYEES BY NAME FROM 'RALPH'
                         TO 'RALPH'

The code above will fetch 0 records following RALPH.

Multi-fetch will save Adabas commands and elapsed time. The only CPU saved is in a reduction in inter-region communication. You can use TEST DBLOG in Natural to see physical vs logical Adabas commands.

Use a factor of 10 unless you know the number of records to be retrieved always is smaller than 10 - then use the smaller number. You CAN use larger numbers, but you run the risk of negative impact on concurrent Adabas users.

In case you are relatively new to Natural, I thought I would add to Ralph’s last post.

Suppose there are six records with NAME = ‘RALPH’

Consider Ralph’s first code excerpt:

READ MULTI-FETCH 100  
     EMPLOYEES BY NAME FROM 'RALPH'  
  IF  NAME > 'RALPH'  
    THEN  
    ESCAPE BOTTOM  
  END-IF  

The first read command issued to Adabas will be an L3 command with a starting value of ‘RALPH’ specified. Adabas knows absolutely nothing about the processing of the record (unless the loop contains an update, delete, or store statement, in which case it would be an L6 command). Hence, Adabas will read 100 records and place them in the buffer for Natural’s use.

Suppose you changed the READ statement to:

READ MULTI-FETCH 100
EMPLOYEES BY NAME FROM ‘RALPH’ ENDING AT ‘RALPH’

This will NOT change the command issued to Adabas. ENDING AT is a Natural function. Hence, Adabas will again read 100 records and place them in the buffer for Natural.

HOWEVER, if you changed the the READ statement to:

READ MULTI-FETCH 100  
     EMPLOYEES BY NAME FROM 'RALPH'  
                                         TO 'RALPH'  

This is an entirely different matter. TO invokes an Adabas functionality (whereas ENDING AT, THRU, and WHERE invoke Natural functionality). Hence, Adabas will check records as it reads them. As a result, only six records will be read and placed in the buffer for Natural.

As Ralph noted, you can use DBLOG to see the difference between READs with ENDING AT, THRU, WHERE, and TO. If you are unfamiliar with DBLOG, find someone in your shop who is familiar with it and have them walk you through READs with the aforementioned optional clauses.