Many customers experience issues with performance and often times they do not take advantage of a simple change that would benefit overall Sage 500 ERP system performance. This change applies to the default settings for BIEs and Lookup Views to prevent auto-loading data when launched. In many cases, this is a huge benefit as some views/lookups would default to return thousands, hundreds of thousands or in extreme cases millions of rows. The likelihood that the user truly needs all of this data is typically low.
Use of Filters
It would be far more efficient for the user to first identify filter criteria prior to searching for a result data set. This in turn would result in only a subset of records being returned to the user. Below is an example of an explorer view where the user has provided this filtering criteria.
A training tip that comes out of this: You will want to ensure that you let users know that they are not going to get data without clicking the refresh or search button. This is something they quickly adapt to.
Figure 1: Example of a Business Insights View with a filter applied. The result set is greatly reduced.
Sage 500 User Tasks to Update Settings
Users have two tasks available to disable the auto load of data on launch for individual BIEs or Lookup Views.
Using the Sage 500 ERP Maintain Lookup Views task you can change each lookup view one-by-one to uncheck the “Load Data Immediately” setting. Unchecking this option prevents the Lookup view from returning records until the user clicks the Refresh button to populate the grid.
Figure 2: Maintain Lookup Views task
Figure 3: Example of the Lookup View after disabling Load Data Immediately (data does not auto load)
Figure 4: Clicking the refresh button on the Lookup View will display the data
Using the Sage 500 ERP Maintain Business Insights Views / Context Menus task you can change each BIE one-by-one to uncheck the “Allow Load at Startup” setting. Unchecking this value will make it such that data will not auto load initially.
Figure 5: Maintain Business Insights Views / Context Menus
System Administrator Update All BIE Views and Lookup Views
Now that we have reviewed how to make these changes via the user interface, there is a much more efficient way for a system administrator to update all BIEs and Lookup Views for your Sage 500 ERP installation. This can be done by a user that has access to the database and can execute some basic update statements. Below are the commands to accomplish these updates. Please note that the values are different based on whether you want to disable auto-load in the BIEs or the Lookup Views.
We Can Help You With a System Performance Review
Read Consultant John Puttmans Blog Series on Using Extended Events to Performance Tune in Sage 500.
Full Troubleshooting Series
Parsing an Extended Event Attach_Activity_ID into Component Parts with the String_Split Function
Using Extended Events to Performance Tune Sage 500 - Overview
IDENTIFYING THE SAGE 500 TASK NAME AS SEEN IN SQL SERVER
CREATE AN EXTENDED EVENTS SESSION FOR RECORDING ALL THE ACTIVITY UNDER A TASK
Running the XE Session and Brief View of Events
EXTRACT THE XE DATA FROM THE EVENT FILE AND LOAD IT INTO A SQL TABLE
ADD HELPER COLUMNS TO THE XE TABLE
ANALYZE THE DATA FOR LONG-RUNNING SINGLE QUERIES
Using Extended Events to Performance Tune Sage 500 – Part 7
Please reach out to the RKL Support team.
