Multi-Fetch

Is there a rule-of-thumb as to when multi-fetch should be used, or should it always be used except in the cases where I expect to get only 1 or 2 records back?

Natural is pretty good at keeping us (developers) out of trouble when using Multi-fetch, but still I would apply it on a case-by-case basis on the mainframe (Windows is a different story). In training sessions, I typically spend an hour or two discussing the application of Multi-fetch - there’s too much to lay out here. But I’ll give one recommendation: when Multi-fetch is used in an on-line program, there should be a corresponding TO clause on the READ/HISTOGRAM statement, otherwise you run the risk of throwing away all the records in the Multi-fetched record buffer.

Be especially careful if any record maintenance is involved. This includes UPDATE, STORE, and DELETE. You can create nightmarish situations with MULTI-FETCH.

Also, be wary when working with READ/FIND/HISTOGRAM loops where you might conditionally escape the loop. e.g.

READ
:::
IF something true
::::
ESCAPE BOTTOM
END-IF

steve

R1.  
READ file BY descriptor  
  ...  
END-READ
R2.  
READ file BY descriptor  
  ...  
  UPDATE (R2.)  
  ...  
END-READ

Prior to statement-level Multi-fetch, you would apply Prefetch (or Multi-fetch) via JCL. R1 is an excellent Prefetch candidate, but performance would be adversely affected in R2 due to the hold status of all the records.

With statement-level Multi-fetch, this is no longer a concern.

R3.  
READ MULTI-FETCH 10 file BY descriptor  
  ...  
END-READ
R4.  
READ MULTI-FETCH 10 file BY descriptor  
  ...  
  UPDATE (R4.)  
  ...  
END-READ

Natural will apply Multi-fetch to the L3 (read logical) command in R3, but the Multi-fetch clause will be ignored in R4 because it is an L6 (read logical with hold) command.

As with Prefetch, statement-level Multi-fetch may increase the incidence of problems caused by “dirty reads,” but the larger, performance-related problem of multi-fetching L4, L5, or L6 commands has been eliminated.

Hi,

Regarding Multi-fetch issue, does anyone know how to choose the best multi-fetch factor?

Is there a rule that I can use to set the best factor to be used?

It seems that it could vary by the amount of the records that you want to return on buffer, but how much is enough?

We need to think that this area is taken from Operating system or monitor TP.

Thanks

Keep it simple. Use a factor of 10.

The tendency is to choose a very large number, to maximize the performance improvement. Sure, technically, in the right situation, you could set a number of 2,000, presuming that the memory is available, but many negative impacts must be considered.

A factor of 10 is an order of magnitude! This will give a substantial performance improvement with minimal chance of negative impacts.

On the other hand, if you have a scenario where you know the maximum (5 beneficiaries, 9 members on a team, 15 detail lines per screen), then use that number instead.

Hi,

If you have a batch program, and Natural program uses Multifetch feature. Can I use Adabas multifetch (PREFETCH) as well? Which one of them will be assumed?

BTW, according to the manual, one of the differences between ADABAS - PREFETCH vs MULTIFETCH, are the options - PREFETCH=YES, and PREFNREC. besides these parameters, does anyone know other difference between these two resource (PREFETCH vs MULTIFETCH) - specially when we are talking about execution?

Thanks.

Statement-level Multi-fetch will override Prefetch parameters specified in the JCL (ADARUN parameters).

PREFETCH=YES will initiate Multi-fetch at the session level. If you’re on an IBM mainframe running MVS, OS390, zOS, etc, then specify PREFETCH=OLD to initiate PREFETCH. PREFETCH is the forerunner of Multi-fetch, and performs better.

We recently started using FASTPATH. From what I learned, FASTPATH does have an option to read ahead and cache in a similar way to MULTI-FETCH, but according to Becky one should not use FP and MF together. It seems that FP is a better all-round solution (if it used at your site) for sequential read-ahead. We do not have FP on all our files, but for those files that have it, we have had very, very impressive results. I also have had great results with MF on some some batch jobs (from before we had FP).

I think there is a place for both products and of course every situation is unique so it is always good to analyze the file and its uses before making a decision.