MboSet performance, Memory Cache, and DB Call

I recently had to look at ways to improve performance of a custom built operation in Maximo. Essentially, it is one of the many validation operations taken place after a user uploads a PO with a few hundred thousand lines.  The team here already built a high performance engine to handle the process, but even with it, this particular operation still take around 15-17 milliseconds to process each line which is too slow to their current standard. Imagine to process 200,000 PO lines, it will take nearly an hour just for this operation alone. There are a few dozen of these operations need to be executed, plus other standard basic Maximo operations like status change or save, the whole process takes up many hours.

With this millisecond operation, many assumptions or standard recommendations on improving performance may not work. In some instances, following the standard recommendations actually make it slower.
For example, many Maximo blogs suggests setting a MboSet to DISCARDABLE to reduce memory usage, but if the MboSet is re-used repeatedly, it will need to query from the DB again and again which can be costly. In this case, don’t set the MboSet to DISCARDABLE will allow Maximo to cache data in memory and thus make it faster. 

Usually, for performance tuning, database query is the most expensive operation, and with Maximo, it has something to do with the getMboSet method. Therefore, a better insight on how it works will help us to devise the best strategy for writing code for high performing operation. In this post, I’ll list out the findings on some of the experiment that I did around MboSet, hopefully will help save you some time if you have to deal with the same problem.

1. mboSet.count() versus getSize() versus isEmpty()

If we need to retrieve a MboSet via relationship such as itemSet = asset.getMboSet(“ALL_ITEM”), and we need to check if the result set is empty or not, we can use either itemSet.count() or .getSize() or .isEmpty(). The differences between them are:

• count() will fire off a “Select count(*) From ITEM Where…” query; isEmpty() will fire off a “Select * From ITEM Where…” query.

• getSize() will never fire off a DB query. It only returns the size of the mboSet which have been initialised in memory. Thus, if after the itemSet = asset.getMboSet(“ALL_ITEM”), we call cnt = itemSet.getSize(), we will get cnt = 0. After itemSet.moveFirst() is called, getSize will return cnt = 2 (because Maximo initialised one object ahead), and after we have loop through the whole itemSet using itemSet.moveNext(), calling itemSet.getSize() will return the actual size of the whole set.

• isEmpty(), together with moveFirst(), or moveNext() or getMbo(i) function will call a “Select * From ITEM Where…” query if the itemSet never been initialized. But if the mboSet is already initialised, any subsequent call to those functions will just access the object in memory and thus doesn’t trigger another DB query.

Considering the following piece of code:

With this code, the mboSet.count() method will fire off a “Select count(*)” query. Then, when getMbo() method is called the first time, it will fire off a “Select * From…” query to initialise the mboSet. Although inside DB, the Select count(*) cost much less; from Maximo JVM, with a small table, it takes about the same amount of time (a few milliseconds) to execute the mboSet.count() and the mboSet.getMbo(i) method.  As such, the above piece of code will take about twice amount of time to execute when compare to a loop that doesn’t use the count() method. This is consistent with the recommendation by Bruno in his blog.

However, considering a scenario when our logic only need to check if a result set returned is empty or not, with massive table, calling count() can be much faster than calling isEmpty().

2. Retrieve MboSet via a MXServer object versus via Relationship

When we retrieve a mboSet via relationship, the data will be cached in Maximo’s JVM memory. Thus, if the same relationship on the same mbo object is used again, it doesn’t fire off another DB query, thus improves performance significantly. However, if in our code, instead of using relationship, we retrieve the mboSet via mxserver.getMboSet(), it will query the DB every time, thus could impact performance if the same mboSet can be re-used (when there is no change to the DB query). Therefore, in this case, it is advisable to retrieve the data via relationship instead. But be aware that in the case the same relationship is used, but with a different where clause, Maximo will have to call the DB to execute a new query, thus there will be no difference in term of performance.

This leads to a scenario where you don’t want to add a new relationship to the object every time you need some specific operation. In this case, we can define temporary relationship in our code as in the following sample:

itemSet = asset.getMboSet(“$TEMP_RELATIONSHIP”, “ITEM”, “status = ‘ACTIVE’”)

This will add a new temporary relationship during runtime, and as long as the where clause doesn’t change, the itemSet data is cached and thus the next time you call the getMboSet using this temporary relationship, data will be accessed from memory instead of being queried from the DB.

Sometimes, you will want to shorten your code using the dot notation in mbo.getString(), getInt(), or getBoolean() method etc., Maximo will try to use cached data instead of querying the database, thus considering the following piece of code:

The first getString statement will initialise the mboSet retrieved by the “ASSET_ITEM” relationship. Thus, subsequent getString statements doesn’t query the DB again. Because of that, in term of performance, it will be similar to the following piece of code:

Using relationship will help caching the data in memory, but if the data is only used once, all it does is increase memory usage and thus could potentially degrade performance instead of giving any benefit. Thus, you should be considering whether data can be re-used. If not, setting the mboSet to DISCARDABLE will ensure memory is freed up after use. So my recommendation for you to deal with performance issue is to always run your own test and make sure the right strategy is used for your scenario.