ADempiere and iDempiere – Performance Tuning and Scale-Ability

Hi Everyone,

A common question is: “How well does ADempiere and iDempiere, open source ERP, scale to hundreds of users?”

The answer is: “It scales well so long as you know a few tricks”

The purpose of this blog is to share a couple of those tuning tricks to help you get the most performance out our your open source ERP with the minimum hosting costs. If you are interested, read the bottom results section to uncover why open source and performance tuning matters to the value of your company.

ADempiere and iDempiere are general purpose ERP systems, and as a result, they are designed to be used by a wide audience in various industries. This is a good thing in most circumstances; however, the costs comes in the form of general performance.

Almost every ERP administrator has been in the situation where a fresh ERP install performs admirably. Then, after loading the system with the myriad of products, business partners, orders, material transactions, and accounting details, the system starts to slow down over time.

The question is how do you continue to grow transaction history and concurrent users while controlling or even decreasing hosting costs Here are some ADempiere and iDempiere tips and tricks to accomplish this task. Admittedly, the below tips are geeky. If you are business user, read the Results section to understand why the geeky stuff matters.

1. Table Direct and Table references are very expensive compared to the reference type Search. Log in as the System Administrator role, open the Table and Column window, navigate to the Column tab, and look at the Reference field. This field tells ADempiere and iDempiere how to interpret data from the database. Reference options Table Direct and Table tell the system to look up all the field options available to the user and load them into a drop down field before the data is ever shown to the user. This lookup process is VERY expensive from the database perspective. Every time the system loads a record, the system has to find all field options for all fields before it shows the details to the user. The alternative is to use a reference of Search. Search waits until the user clicks the field’s lookup button before it pulls options. If a user does not need lookup options for a field, like Business Partner, the system never needs to perform the costly detail lookup. If the use does need to look up this field, the cost is incurred at the time of lookup – not when all the records are initially loaded. The result is this change is (a) better system performance and (b) better distributed load on the server. Be aware that the cost of using Search instead of Table Direct comes in the form of usability. Most users prefer a dropdown instead of a popup box to choose field options.
2. Indexes – unfortunately, ADempiere and iDempiere need more of them. When you repeatedly look up data from a database using the same criteria, you probably need an index. A database index is simply a database’s map to locate and retrieve data in the fastest possible manner. The best way to describe increasing performance using an index is with an example. A customer recently experienced a system slowdown. They narrowed down the culprit to the Sales Order window. Every time someone opened the 10K + sales orders in this window, the system hit 100%.  Nothing with the Sales Order window had changed; therefore, they wondered what was causing the sudden slowdown in performance. It turns out they recently loaded about 1K records of Partner Relation information. The Partner Relation window enables you to use different BPs for invoicing and shipping on a Sales Order. Because (a) one of the Sales Order fields did a Table lookup on both the Business Partner table and the Partner Relation table AND (b) the Partner Relation table did not have an index on C_BPartner_ID, the system was having to comb through the 1K records every time a Sales Order record was loaded (all 10K+ of them). By adding a single index on the Partner Relation table the system went from drowning to flying.
3. Views – there are times when you need to expose data from a database View through an ADempiere or iDempiere window. Because data from a view is read-only, there is never a time when a field should be listed as Table Direct or Table. All view Table Direct and Table references should be changed to Search!

You may be thinking to yourself, “the above seems fine and dandy”; however, “how do you uncover the problem areas?” The answer lies in the logs. This is typically not a favorite answer since digging through logs is often viewed like digging through trash. However, like digging through trash, you can gain amazing insight by learning how logging works.

Below is an example. When reading through logs, I discovered the culprit that was causing a 0.5 second delay when loading records. This SQL statement lead me to the Partner Relation index example above. A half second delay may not seem that much; however, when you multiple this times 10K records, it adds up.

Another way to steadily improve performance is to get a PostgreSQL performance tuning book or attend a class. Here, you will learn how to identify the low hanging performance issues that require little effort and offer high performance rewards. Over time, you will drive down the load and increase your ability to host more data and more concurrent users on the same hardware. An example of what you will find includes noticing that the M_Locator table does not have an index on M_Warehouse_ID (the locator’s primary foriegn key).

Lastly, if you are not interested in trying the above techniques, you can always just call me. I love this type work! During one project, I was able to take a 8-core Oracle database system with an average load of over 5 to an average load of less than 0.1 by applying the above techniques. More importantly, I was able to teach the system and database administrators how to continue to drive down performance without depending on me.

I hope this helps!

The Results

The concept of free open source ERP becomes profound when you exceed 100 concurrent users. If you can support your organization using free open source ERP, you can take your licensing savings and reinvest it back into your users and your processes. This example profiles a company that spent $2.3K per user per year to support Microsoft Dynamics for 100 concurrent users. Let’s assume that you take the license costs (estimated at $20K/month) and reinvest them in your company. And, let’s assume that your company can produce at least a monthly compounded return of 7.5% over the 14 years of an ERP’s lifecycle. The result is a net value increase of almost $6M dollars. And, you own the software! You are not leasing it from Microsoft. If you were to sell your company, the value of your ERP asset increases the ‘goodwill’ estimation of your selling price. This is my experience!