August 25, 2012

MBO Performance Tip N.1 - Avoid using MboSet.count() method in loops

This entry is part of the Java MBO performance optimization golden rules series.

I think this is one of the most common errors when developing Java MBO code.
The MboSet.count() method issues a "SELECT COUNT(*) from ..." SQL query to retrieve the number of records in the database table.
This can be a big problem especially when used in loop statements like this:

MboSetRemote mboSet = getMboSet("ASSET");
for (int i=0; i < mboSet.count(); i++)
{
  MboRemote mbo = mboSet.getMbo(i);
  ...
}

The previous code snippet will issue a new SQL count statement for every iteration of the loop.
A better approach is the following.
MboSetRemote mboSet = getMboSet("ASSET");
MboRemote mbo=null;
for(int i=0; (mbo=mboSet.getMbo(i))!=null; i++)
{
  ...
}
I have already tackled this argument in this post where I also describe a more elegant way of looping through an MboSet.

Calling MboSet.count() method is not always a bad practice. If you only need to ensure that there are a certain number of records in an MboSet, the count() method is better than trying to fetch the MboSet records. Note that this somewhat is in contrast with what the IBM Maximo wiki suggests.

No comments:

Post a Comment