Multi-Fetch in Windows

, , ,
#1

I was recently reading up on the Multi-fetch parameter, hoping to implement it in our Windows based environment (ADA and ADA2 DBs).

I stumbled across this great article by Steve Robinson. http://techcommunity.softwareag.com/AdaNat/downloads/A9RC7.tmp.pdf.

I also reviewed the Documentation in Natural 8.4, and found the following Note within the Read statement:

From these two sources, I am led to believe that using the following in Windows pointed at an ADA database:


Read multi-fetch 20 test-view logical by super-1 starting from #super-start ending at #super-end
    <some stuff>
end-read

Would ignore the value of ‘20’ specified, and instead replace it with ‘8’ if MFSET is set to ON.

Since this article is now 10 years old, is there anyone who is working in Windows OS who can confirm that setting a custom multi-fetch value is still replaced by a default, and has that default been updated to a larger value for improved ‘fetching’ or is it still ‘8’?

#2

It’s very frustrating that ADA2 doesn’t support the Multi-fetch clause. I had hoped that SAG would correct that flaw in Natural 9.

The documentation for the MULTI-FETCH clause is a bit confusing. I hope the following clarifies:

  • Multi-fetch was introduced on the mainframe with the syntax MULTI-FETCH #, where # is the multi-fetch factor. A factor of 10 provides excellent performance improvement at a reasonable cost; there is rarely a need to go higher.
  • Later, Multi-fetch was brought to Windows with the syntax MULTI-FETCH ON. Natural sets the factor to 8 for local databases and 32 for remote databases.
  • Generally it is not advisable to have MFSET=ON. How many of your application’s database accesses return fewer than 8 records? Adabas would return 8 every time, only to have the application ignore the surplus.
  • Rather than using MFSET, code MULTI-FETCH ON on those accesses that retrieve more than 8 records.
  • My personal rule is to use MULTI-FETCH ON only where the entire file is read, or in combination with the TO clause.
  • For modules which are routinely copied between mainframe and Windows, I code ON instead of a factor. Mainframe Natural also will translate ON to a factor of 8.

To answer your specific questions:

  • Yes, MULTI-FETCH 20 (that is, specifying a multi-fetch factor) will be ignored under Windows, and MFSET=ON would provide 8 records per access to a local database.
  • The implicit multi-fetch factors of 8 and 32 remain.
#3

I am running my development libraries of Natural 8.4.1 pointed at a remote database through Entire-Network. Does this mean that having ‘Multi-Fetch ON’ coded into a report which returns 1 million records would run faster (MF = 32) than to have it execute locally on the DB server (MF = 8)?

Is there a reason for the distinction (adabas calls through the network are 1/4th slower than locally)?

Is there any knowledge of internal workings to support ADA2 multi-fetch in the future? I have recently been using dynamic fields to store incoming XML strings, and this forced the whole File to be pushed to ADA2.

For now, my take-away is:

  1. MFSET=OFF to avoid unneccessary DB calls when performing any READ/FIND.
  2. Multi-Fetch ON included as a clause for READ/FIND where a large number of records is to be read.

Thanks for the illumination Ralph.

#4

  1. Will access to a remote database be better than to a local DB?
    Compare run times. It depends on CPU speeds, transmission speeds, server work loads, etc.

  2. Why 8 and 32?
    I can only guess that this is an attempt to offset data transmission time by reducing Adabas wait time.

  3. When will ADA2 support Multi-fetch?
    Anyone’s guess.

  4. Take-aways.
    Spot on. :slight_smile: