My name is Tomas Särnhammar and I am a developer at Agresso HRM.
We are now rolling out release 4.4 to the first customers and last week it was time to upgrade the Market Place (http://provider.nextlearn.net/) used by many Swedish Customers like Telia and Saab to search for and enrol on courses by commercial providers such as AddSkills, AchieveGlobal, Lexicon etc.
The really big news i 4.4 is that the organization hierarchy can be arbitrarily deep. This is used in contracts and filtering so that contracts and filters on a higher level and valid on lower levels unless an organization on a lower level has its own contract or filter. This makes contracts an filtering both simpler to use and more powerful. We recognized early on in the development that this may be a performance problem if we where not careful. The reason is the that the rules for which filter and contract that is valid for a given seller and customer organization can be complicated:
- The amended filter rules can be complicated, by filtering on category and activity type etc.
- Both filters and contracts are typically done on a high level in the organization hierarchy and this means that for most organizations we must search up in the hierarchy to find the first organization with a contract and filter.
For filters we saw during development that evaluating the rules "in run time" was too slow, and we solved that by creating the system job "B2bAccessUpdateJob" that evaluates all filters (all recent changes to filters actually) and stores the result in a way that is fast to use in the catalog search, an indexed table in the database. This job must be scheduled to run periodically in order to keep the filters updated.
Initially we thought that the organization hierarchy search for contracts was fast enough as we store the hierarchy itself in a search-friendly way (again a separate indexed table). That was not the case.
When we upgraded the Market Place the database server almost stopped. The Market Place is an Agresso HRM talent management portal configured to accept catalog searches via a web-service interface from other portals. This means that the catalog search load is much higher than on a typical (or even large) installation.
It turned out that the contract search was the culprit. It was implemented in a view in the database, and it was too expensive to select from in the search. Last week we changed that using the same technique we used for the filters. We created a table that is the "expansion" of the view, meaning that the table has the same columns as the view, and it should contain the same rows. On the the table we created indexes that are relevant for the search. The same system job that keeps the filter tables updated, was amended to update the new contract table too. Every time the system jobs runs, the rows in the table are updated to match those of the view.
This update returned the load on the database server from 100% to a healthier 15%. The catalog search time for individual searches went from sometimes 30 seconds to near-instant in all cases.
regardsTomas Särnhammar
