Improving Read performance Leveraging the ADXFTL parameter

3 minute read time.

Within Sage ERP X3 version 6 it is possible to improve performance of intensive read operations by setting the ADXFTL parameter. You can set this parameter in one of two locations and each has its purpose. You can set it within a specific user session for testing purposes only. When you like the results from your testing you can harden the value of ADXFTL as a folder parameter inside the APL.ini file. In order to set this value you should have runtime 223 or better.

Warning: Before altering the APL.ini file in any way make a backup first. Also note, these instructions assume some background knowledge with installation on Sage ERP X3. Finally, be mindful of carriage returns and line feeds inside the APL.ini file. Do not modify this file with Window’s notepad, use a tool such as Notepad++, a more robust editor.

Why would you want to set ADXFTL?

In certain edge situations where data volumes are higher than normal you may find a benefit by setting ADXFTL. This is usually where a read operation exists inside of a FOR loop. This parameter tells the runtime to group the read operations within that FOR loop into a local buffer by the number you set. The database will then transfer the records in the grouping specified. If ADXFTL is 1 or less then the FOR loop will not be instructed to group. Note, you can have a negative byproduct if you set the value of ADXFTL too high as there will be some overhead for smaller tables.

In situations where the SQL Server is separate from the application server you may see a larger benefit than a single tier install. In all cases, you will want to test the results of setting this value before implementing this in any production setting.

So what should you set ADXFTL to?

What value should you set ADXFTL to?

You can start by setting ADXFTL equal to 10, and run your tests. Record the results of your test for your slow performance issue, then adjust the value upwards, to say 20, run your test again and record the results once more. Again check at a value of 30 and maybe then again at 40, test and record. Review your results and find the sweet spot. The results can be hardware and environment dependent, hence the heavy emphasis on testing within this article.

Setting the ADXFTL parameter for testing only

This technique lives for the duration of the user session only. Once you log out and back in the value will return back to normal.

  1. Launch the Process Editor (ADOTRT)
  2. Create a new process called ZADXFTLTEST

    # Set ADXFTL to an arbitrary value of 20

    # Test the performance of your slow performing query and adjust the value up or down until you find a sweet spot for performance

    ADXFTL = 20

  3. Compile the code
  4. Execute the code

Setting ADXFTL At the Folder Level

This technique will persist the values to the folder. Every time you log on to this folder you will retain this value.

  1. Locate your folder's APL.ini file
    1. Example location: C:\SAGE\SAGEX3V6\X3APP\Folders\NATRAINV6\APL.ini
  2. Edit the file to append the following line
    1. ADXFTL = 20
      WARNING: Editing this file incorrectly may have negative repercussions for Sage ERP X3. Be mindful of line feeds and carriage returns. It is advised to use an advanced editor such as NOTEPAD++. Do not use Window's built-in notepad tool. Backup your APL.ini file before editing.

Once you've edited the APL.ini file to append the ADXFTL parameter log out of Sage ERP X3 and back in and your value will be set.

Checking the Current ADXFTL Value

  1. Launch the calculator inside Sage ERP X3 from the menu bar's question mark menu
  2. Within the calculator type ADXFTL and press enter
  3. The value you set will be returned here

 

I hope this helps!