ADempiere and iDempiere – High Volume of Warehouse Locators Update

Hi Everyone,

The purpose of this post is to let you know about a performance tuning issue that was recently resolved in ADempiere and iDempiere, open source ERP. This post applies to anyone performing wholesale distribution or manufacturing across multiple locations or a single large location.

The Symptom:

Java CPU usage on a single core of a specific WebUI server was spiking to 100%. The server would eventually become unresponsive.

The Culprit:

By using the topics discussed in this post, we determined the cause of the CPU spike was due to the Locator Reference. In this instance, there are over 130K locators spread over 12 warehouses. When the Locator field was being loaded as part of opening a new window, the WebUI server would spend about 1 minute processing all the Locator options for each occurrence of the Locator field on the window. The Product window has about 3 occurrences; therefore, every time a user would open the Product window, the server would get hit hard. This issue was not obvious because the product window would load as if all were OK; however, behind the scenes, the server would stay busy churning away. If ten people concurrently open the product window on the same server, the system would grind to a halt.

The Solution:

Do not use the Locator Reference. Instead change the M_Locator_ID Reference to Search. The Search Reference is much easier to use, and it is more consistent with how ADempiere and iDempiere function everywhere else. Here are my thoughts:

1. You can type in the search field, and if what you type matches a locator value, it will automatically find your record. This point is especially valuable for distribution centers that make heavy use of barcode scanners.
2. The Locator Info window that pops up for Reference gives you the ability to search by the search key, Aisle (x), Bin (y), and/or Level (z). Unfortunately, the Locator Info window does not include the Warehouse. Let me know if you need help adding it, or just add the warehouse code to the beginning of your Aisle (x) field.
3. The old Locator Reference allows anyone to create locators. This behavior is typically not desirable.
4. The old Locator Reference drop down list of locators is clumsy.

Instructions:

To globally update ADempiere or iDempiere to use the search dialog, use the following query:

update ad_column 

set ad_reference_id = 30 

where ad_reference_id = 31 

and columnname = ‘M_Locator_ID’;

Note that there are a couple columns named M_LocatorTo_ID, that you need to update to use Search. The reason I call them out separately is because you also need to perform one additional step: set the Reference to ‘Search’ AND set the Reference Key to ‘M_Locator’. It is easiest to do this by hand. Here is a query that will point you to the tables:

select t.tablename
from ad_column c
join ad_table t on (c.ad_table_id = t.ad_table_id)
where c.ad_reference_id = 31
and c.columnname = ‘M_LocatorTo_ID’

Disclaimer and Hint:

Any time you perform an update on a table, you run the risk of contaminating your data. When contamination happens, it can cost your hours or even days to recover. Here is a quick shortcut to ensure you do not waste time when bad things happen. Any time you are updating data in a table, make a quick backup using the following statement:

create table deleteme_ad_column_back as
select * from ad_column

The above query is super fast. You can create a simple update statement to pull the original values from the backup table if needed. And, if you ever see a table named deleteme* in the future, you can always delete it because you know it was used for a temporary backup.

ADempiere vs iDempiere vs Openbravo vs Compiere

Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.