Slow Epicor 9 Reporting Performance

My Reports are taking a long time to print – A common problem we see with customers we pick up that were installed prior to our involvement.

Steps to Address

  • On the application server, make sure you disable any type of ‘On Access Virus Scan’ for all directories that relate to where the XML results of the associated BAQ are being left and to the Crystal Report Definitions.  you can include them in your periodic virus scans which are usually once per day.  If you don’t do this, every single time reports are run from any user, your anti virus engine is firing up causing a significant spike in hard drive and CPU utilization

  • The Epicor System Monitor’s job is to retrieve the XML that results from the BAQ (Business Activity Query) of the report.  It then picks up the Crystal Report definition which is the physical layout of the report and passes them off to Crystal for actual final report generation.  This monitor is not as ‘scientific’ as you might think in terms of the  way it runs.  It periodically polls the server and looks to see if any fresh XML data (again the result of a reports underlying BAQ being executed) and when it finds it, it sends it off to Crystal which reads the XML data (not your native SQL or Progress data) and presents it in the nice format you think of as Crystal Reports.
    • The problem is there are default settings which are called Normal, Priority & Duration.  The defaults are 30,000, 3,000 and 15,000 respectively.    These can be viewed and set from the Actions>Configuration menu when you pull up the System Monitor.  To change these settings you will need Customization Rights.  These values are stored in the xxx.Mfgsys (usually Default Mfgsys) file in the config directory on the Client computer.
    • These values make the following occur.  When a report is submitted for printing, the System Monitor goes into High Priority Mode and uses the polling defaults which default to checking the server every 3 seconds (thus the 3,000) and continues to do this for 15 seconds (the 15,000 value)  Thus if the XML query isn’t finished within 15 seconds, it will not then check for another 30 seconds (the ‘Normal’ polling frequency).  Thus report generation could easily not even start generating on the workstation until 45 seconds after you start.  By the time Crystal is done, this could easily be 1 minute to 1.5 minutes.
    • Clients First Business Solutions strongly suggests adjusting these defaults.  The 3 second polling interval is fine (more polling puts more stress on the network and server).  However, you should adjust the duration from 15,000 to at least 30,000 which gives the BAQ at least 30 seconds to return its data.  If you have very large data sets you may even want to increase it longer.  If you are a power user and know the underlying BAQ used in a report, feel free to use the BAQ Manager and time how long it takes to retrieve the query which would give you a good feel for how much time (taking into account future growth) you should set your Duration to.
  • There was a bug prior to version 9.05.606 that caused the system to never use priority mode if a user logged into one company, then switched companies and ran reports.  The problem wouldn’t go away until they completely exited Epicor 9 and re-logged in.
  • There is also an enhancement in 9.05.605 and up that significantly improves reporting performance with reports that have Queries that pull from many tables
  • Starting with version 9.05, there is a setting under Users in the System Options section labeled “Enable Network Compression.”  We suggest using this, especially for users using the Smart Client over a WAN (Wide Area Network).  This will should improve speed as it will compress the XML and Report Definition data on the Server before sending it over the network to the Client Workstation and thus send less data and use less bandwidth on your WAN.
    • Due to the odd way this is stored in the client Default.Mfgsys file, you won’t see the effects of this change until after the second time a user logs into the system because it updates the settings file on the first login, but that setting change isn’t seen until the next login.
  • In most cases, we recommend using Terminal Services or Citrix rather than installing the Smart Client on workstations over a WAN.  It results in easier upgrading, a more stable environment that eliminates Internet interface and generally performs better.  For example, if you run a large report (or a report that uses a BAQ with a large result file, say 3mb, then that entire 3mb has to be transferred from your server to the client over the Internet.  Depending on the upload bandwidth (and other activity) where your server is located, and the download speed where the client is located, this can be a very slow process and saturate your Internet connection if you have a lot of users.
  • For reports you build, the usual rules apply. Make sure any tables you join are based on defined index keys.  If you need to join by a field that isn’t already indexed, we recommend adding an index to it.  Using non-indexed fields as keys will results in slow report/BAQ generation.
2013-02-28T15:35:35+00:00 February 28th, 2013|Epicor|1 Comment