Performance tuning your Sage X3 system: X3 specific considerations

15 minute read time.

Updated: 10 August 2023

Back to Index

Please note this document and all other documents it links to are living documents so will evolve over time as new things are discovered, new functionality is provided, best practises adjusted and/or when I get time to add content; so please make sure you come back and visit this source document often.

Contents

Performance tuning your Sage X3 system: X3 specific considerations

Introduction

Performance sensitive parameters

Checking indexes

Home pages (aka Landing Pages)

Screen sizing

APLLCK table

Performance index tool

Logging and tracing

Auditing

Archive/Purge

Sequence numbers

Batch Server throughput

Accounting Task

Requesters

Database Optimisation function

Web Services

Classic screens slow to render

Left list memos

Migration/upgrade performance

General blogs, KB articles, etc.

Introduction

There are a multitude of different areas within X3 functionality and/or configuration which can have an impact on overall system performance, specific functions performance or a specific user.   The areas highlighted below hopefully picks out the main areas that have been known to influence performance.

Performance sensitive parameters

Some X3 parameters can modify default behaviour and have a profound impact on the speed of X3 from a user perspective.  Those most likely to impact are listed below:

SUP, SEL, LFTBOX

Number of elements in the left lists ( <= 100 if possible) *

SUP, SEL, NBDERLUS

Number of last read lines (<= 50) *

SUP, SEL, NBRCHOOSE

Number of elements for selection (<= 100 if  possible)

SUP, SEL, CHDERLUS

Load last read record.   If large record was viewed on last visit, will be reloaded on next visit and potentially take some time to draw the screen.

SUP, INT, NBRREQ

Number of query lines (20 default) per page.

SUP, INT, NBRREQBLK

Number of query lines - Bulk mode (200 default)  *Used by Excel

SUP, PRF, MAXUPDTRS

Number of lines before calling a table commit (50’000 default)

SUP, PRF, NBTRABUFF

Number of lines in memory before flushing to log file (50 default)

SUP, PRF, ROLLBACK

Lock attempts before rollback  (Default 3)   **

SUP, PRF, TIMLCK

Unlock time in seconds (Default 5, recommended not to change) When faced with a first failed attempt at locking because another user has already placed a lock, the number of seconds during which the engine automatically repeats its attempt before failing by warning the user via a locking message

SUP, PRF, TIMOUT

Time limit for response (30 default) Duration for the display of the error window.  If no response in this time, the process restarts

SUP, SEC, TABTRA

Trace system transactions.   Log nothing is best for performance

*LFTBOX, NBDERLUS The loading time of left lists is proportional to the number of elements read and the number of lines

**ROLLBACK (following text extracted from the online help) Its normal value is equal to 3, and it is recommended not to modify it, except in extreme cases (highly active database with a lot of conflicts). But in any case, because its modification can have an influence on the system performances, it is recommended to take advice from a database specialist before carrying out this type of modification.

In addition, you should also ensure your environment is setup as described in Error: "Too Many Distributions" (KB article) as having these parameters set incorrectly can effect performance, particularly the "MAXVANA" parameter (Recommeded activity code values are MAXVANA = VTL, VTP = 3x VTL value)

Don’t forget to check the user specific parameters as well as the folder/company/site level parameters.  You can use “..\InvestigationScripts\SQL\X3_General\ mzX3Parameters.sql” to quickly gather this information if needed.

Of course, any changes to parameters should be timed and tested before and after to ensure performance is not adversely impacted by any changes.

Checking indexes

Sage X3 comes embedded with default table indexes.  You can run a report to check any differences between the X3 metadata and the SQL server to identify indexes that are potentially disabled or added.

Run the "Search Indexes" report

Development, Utilities, Verifications, Database, Search Indexes (SQLDICO)

Refer Online help for more details.

Home pages (aka Landing Pages)

(Also refer to the requesters section)

Landing pages are displayed to users when they login, and users may even be able to create their own.   Depending on what is on the landing page, it may take some time to render when the user logins in and maybe even every time they return from a function.   It is therefore important to understand whether users have anything on their landing pages that may trigger long running requesters or other gadgets.     Probably the easiest way to check is to review the Syracuse logs, after adding the “http.in” logging at “info” level, which will show the length of time for each request.

When using requesters on a home page, if the number of rows of the requester query is more than the number defined in the SELWARN parameter (SUP Supervisor, PRF Performances) then the requester generation will be sent to the batch server, otherwise it is rendered in real time.   The number of rows considered is calculated without any filter taken into account.  e.g. you may have defined filters on the requester to only return a small number of rows, but if the number of rows before the filter is applied is more than SELWARN parameter, then it is still sent to the batch server.  This facility is to help reduce the time taken to render the home page for the user.

Screen sizing

If certain screens are very slow to render grids, it could be the screen dimensions have been modified, or there are a large number of records.  The time taken to render grids is a product of number of records multiplied by number of columns.

You could check what dimension or screen sizes have been changed from default settings by using the InvestigationScripts “mzActivityCodes.sql” and “mzScreenSizes.sql” to gather this information.

APLLCK table

The APLLCK table has been linked to possible performance issues.   It should be self-maintaining but sometimes find session information is not properly cleared out and the table grows.  With no users logged into the folder, check if this table has a large number of rows in it.  If so, the table can be truncated* (* but ONLY if there are no active user sessions)

For example, in SSMS you can run:

-- START OF SQL

-- Check the number of rows in APLLCK table

select * from SEED.APLLCK

-- If there are a lot of inactive records, can truncate the table

-- but ONLY if there are no active user sessions

truncate table SEED.APLLCK

-- END OF SQL

 

Performance index tool

Sage provides a performance index tool which can provide a consistent load to stress the classic components.  This is useful to ascertain how the base system is performing.  The tool has changed significantly over recent release, so review What is Sage X3 performance measurement tool (KB article) to confirm which method to use. 

For V12 patch 25+ (2021 R1), navigate to Development, Utilities, Verifications, Performance Index (AIOBENCH)   You can also review the Online Help

The resulting Global performance index number can be interpreted from the table shown in the Results Interpretation of the online help.  At the time of writing this table shows:

Run-time and database on different servers

Run-time and database on the same server

< 80 Outstanding

< 60 Outstanding

80 < Excellent < 150

60 < Excellent < 100

150 < Good < 190

100 < Good < 140

190 < Average < 260

140 < Average < 200

>= 260 Review architecture to gain improvement

>= 200 Review architecture to gain improvement

Logging and tracing

In general, the more that is being written to a log file, the slower the process will be.  Therefore, although you should enable logging and debugging when needed, you should keep any logging and debugging to be the minimum that is required. 

I have no specific tips to share, other than suggest regularly reviewing the log file locations for any large logs and then identify any processes that seem to be logging more than needed, in order to reduce the logging levels wherever possible.

Auditing

Auditing is often required and/or desirable in order to achieve conformance to business requirements.  Enabling auditing will add database triggers to each table, so this will potentially increase the time spent doing insert/update/delete operations, depending on what you are auditing.  The general rule is therefore to only enable auditing where it is required.

To quickly identify what auditing has been configured within X3 you can consider running "..\InvestigationScripts\SQL\X3_General\mzAuditedTables.sql" to gather this information.  Check the output and ensure all the tables/fields being audited are still required.

Don’t forget to Archive or Purge the audit tables data when no longer needed to be kept for immediate or historic review.

Archive/Purge

Archive and purging are important to ensure old data is removed from the live folder when no longer needed.  Archiving allows the data to be kept but is moved to a historic folder so it no longer needs to be referenced during normal business operations.

This is discussed further in the articles Time to tidy up? (Blog) and Archive and Purge explained (Blog)

Sequence numbers

The sequence mechanism can have a big impact on perceived user performance, although this is often manifesting as a popup error due to application level locking contention.  If the locking conflicts are linked to the allocation of document sequence number counters (orders, postings, or more generally transactions), it is advisable, to decrease these conflicts by applying one of the following methods:

  • Use sequence number counters with criteria that differentiate the largest possible number of numbering sequences (for example, by using the company and site)
  • Define the grouped sequence number counters or use the database sequence management.

NOTE: changing the sequence number mechanism should of course be done in a controlled manner and tested before applying to LIVE folder, don’t just change it on the fly.

Refer the online documentation for more information.

Also review the article from the Center of Excellence and Expertise team "Steps to set a sequence number from NORMAL to DATABASE in a system already in production using SQL SERVER"

Batch Server throughput

Determine a suitable value for "Maximum active queries"  "Maximum active queries" specifies the maximum number of  batch jobs that can be run concurrently, spread across all the available runtime servers.   The best value for this parameter will depend on your system architecture, the hardware (CPU, RAM) available and the nature of the batch jobs that are running (i.e. whether there are lots of very quick jobs, small number of very long running "heavy" jobs, or a mix of different types of job running)   If you want to maximise throughput, a starting point is to configure this as the available CPU cores + 50%.  For example, if you have two runtime servers, both with 4 CPU, then you could configure "Maximum active queries" up to a maximum value 12.  Of course you need to monitor your system to confirm this value is suitable for your own situation, but should provide a good rule of thumb.

Are any jobs running with the “Single user” flag set?   If so, this will be “blocking” the batch server mechanism for the period of time it takes to run batch jobs with this flag set.

Review the batch server log file “serveur.tra” for errors, such as out of memory

For more information, you can review blog “Understanding and troubleshooting the Batch Server

Also be aware of the issue described in Error "FINREQ - Locking error ABATPAR/ABP fstat=1 (44000)" in serveur.tra (KB article) as this may also manifest as a performance issue.

On a more general note, check for any long running batch jobs which could be run out of hours, so they do not interfere with user working hours?   Run the “..\InvestigationScripts\SQL\mzBatchJobs.sql” script to resolve the following questions to help with this analysis:

  • What are the Batch server parameters
    • Also get a screen shot of the batch server setup page
  • How many jobs are run, when and how long are they taking
  • What recurring jobs are setup

Accounting Task

With version 12, the Accounting Task runs as a recurring task via the batch server.  You should first ensure the batch server throughput is adequate to cope with the volume of batch tasks being processed.  After that, you can consider the configuration of your Account Tasks, with Working with Multiple Accounting Tasks (Blog) providing more information on how this could be configured.

Also be aware of the issue described in Accounting tasks: intermittent issues with "Locking error on Table IDTCCE" (KB article) which could manifest as a performance issue.

Requesters

Requesters, especially SQL requesters, could be poorly designed and/or be run too often and/or be run without sufficient selection criteria – any of which could affect overall system performance by flooding the database cache.   The ideal solution would probably be to only allow database aware users to create or modify requesters and make sure any updates are thoroughly tested on a test system with a recent copy of live data to confirm performance.

Wherever possible you should design requesters data to be reused by users; and also set the data to only refresh when needed by using the appropriate “frequency” flag (when called from a menu item)

When using requesters anything more than occasionally, you should ensure you are purging the ALISTER table regularly, which may need to be every day for busier systems.

For more detail see the Online help

Database Optimisation function

Although you can create your own database indexes using the database management tools, such changes can easily get overwritten, for example when running folder validations and can often be missed when reviewing an unfamiliar system.  If you need to optimize standard processes, left lists or inquiries, Sage X3 provides the ability to easily add additional indexes to such processes, and to deactivate them again easily if needed.  This function is also a form of self-documentation as you will see the list of all added indexes and whether they are currently active or not.

This process is of course different from the ability to create and manage custom indexes for use with your own custom code, which would be added to the data dictionary and flagged with an activity code.

To use this facility, navigate to Parameters, Usage, Data, Database Optimisation where you can define additional indexes to be used and retained by X3 standard processes.

If you connect to the X3 folder, you will find there are some examples provided out the box, which can then be copied to your data folders if relevant to your needs.  For example, there are several indexes provided to potentially speed things up when deleting products, or when running a period close.

See Online Documentation for more information

Web Services

You can refer to the Web Services explained presentation for an explanation of how web services work. 

For SOAP web services, the main tuning is to decide how many “channels” you need, then work out whether it is better to have more web service processes (defined in “Hosts” function) or more channels specified in the Soap Pool configuration.    The considerations are how many CPU you have, how much memory can be allocated to Syracuse node processes, as well as how many concurrent SOAP requests you need to handle, and how long each request takes on average.    

The general recommendation for companies with large number of SOAP requests is to dedicate a node to running Web Services only, i.e. route interactive users and SOAP requests onto different Syracuse servers.

REST requests are processed in the same way as interactive users, so there is no REST request specific tuning as such, other than the timeout setting specified in the nodelocal.js but even this shouldn’t need changing from default.

Another general rule is that processing long-running requests is a bad thing.  In particular, if you have import or exports to run as a web service, it is recommended to use the Web Service batch mode for import and export (AOWSIMPORT / AOWSEXPORT).  See Online help http://online-help.sageerpx3.com/erp/12/staticpost/import-export-soap-web-service/

 

Classic screens slow to render

You may find certain classic pages can be reported by users as slow to render.  In the main this is more prevalent when dealing with pages that show a large amount of data, such as Products screen (GESITM) 

  • If comparing from V11 to V12 screen performance (for the same data set) this may be partly down to the change in how screens are rendered in V12 compared to V11. In essence, the whole screen is rendered at once in V12, whereas in V11 the data was read when you clicked a tab… in other words, V12 is doing more work up front, so may be slower under certain circumstances because of this.
  • From our testing, we have found that Chrome/Edge performs up to 50% slower than Firefox browsers, so certainly worth testing different browsers.
  • You may also find that the time taken to render the left list is a factor. If so, then reducing the parameter value LFTBOX "Number of QuickSelect Elements" can improve the initial screen load time.   (Also see Left List memos below)

 If there is an issue only when initially loading certain pages, it would be worth running a "Global Validation" of the Window relating to the screen that is slow, as we have seen this improve performance under certain circumstances.

Left list memos

All Object based screens have a Left List that allows users to select the record they want to work with.  Sage X3 allows users to apply their own filters to the Left List to reduce the number of records to search through.  Sage X3 has a report that identifies where a user filter is not making use of a key field, and where the table holds a large number of records.  To ensure the Left Lists are populated as quickly as possible, we can use this report to add additional indexes to the database tables concerned.

Run the "Analysis of memos" report Development, Utilities, Verifications, Analyse memos (ANAMEMO)

Refer to the online help for more details

Migration/upgrade performance

Performance tuning your Sage X3 system: Migration/Update  (Blog)

General blogs, KB articles, etc.

Time to tidy up? (Blog)

Tracing classic functions (Blog)

Improving Read performance Leveraging the ADXFTL parameter (Blog)

How do I determine the code that is performing poorly (slow)?  (Blog)

What is Sage X3 performance measurement tool (KB article)

Performance index (Online help)

How to troubleshoot slow performance for Sage X3  (KB article)

Load screens in less time (Blog)

Have you seen lockups or slow processing with stock related functions, but have not been able to identify a blocker? (Blog)

Import performance enhancement (Online help)

INFO: Potential tuning to speed up SOAP Web Services (Alert)

Searching on sold-to column in left list of Sales Orders is very slow  (KB article)

User sessions randomly "hanging" for 3 to 30 minutes (KB article)

Supplier situation inquiry (CONSBAGF) takes significantly longer in V12 than V11 (KB article)

Back to Index

  • Hello there,

    Thanks for your comments, it is good to hear the articles are useful.

    The "Investigation Scripts" are discussed in technical tips and tricks presentations at communityhub.sage.com/.../index-page-sage-x3-technical-support-tips-and-tricks-september-2021    In summary, if you are a Sage UK Business Partner you can email me to request access to the scripts via GitHub.  Otherwise you can contact your local X3 support organisation who can contact me if they do not already have access themselves.

    Hope this helps

    regards

    Mike

  • Thanks for this Mike. Have implemented some of the suggestions here with good effect!

    Question, I've seen these type of scripts referenced frequently:

    "Don’t forget to check the user specific parameters as well as the folder/company/site level parameters.  You can use “..\InvestigationScripts\SQL\X3_General\ mzX3Parameters.sql” to quickly gather this information if needed."


    Where exactly is that folder "InvestigationScripts" ? I cannot find it with a standard install of Sage. Is it part of the CCoE install?

    Thanks