When accessing an MboSet, it is common to need to find a particular record. There are two primary methods for this. The first is call Query By Example (setQbe). The second is the Where clause (setWhere).
Let’s do an example. Say we need to find the Asset with the assetnum = ‘1000’. (We assume there is only one site in this example.) Here is a sample using the Query By Example:
assetMboSet = session.getMboSet("ASSET")
assetMboSet.setQbe("ASSETNUM", "1000")
This would work but it has consequences. First, this would return all assets that have ‘1000’ in the assetnum. The bigger consequence (at least for performance sake), is what Maximo does in the background. QBE translates to this SQL:
SELECT * FROM ASSET WHERE ASSETNUM like ‘%1000%’
If you are truly looking for all assets that have ‘1000’ within their assetnums, then this the right approach, but if you only was the asset with the assetnum ‘1000’, this is a slow approach as Maximo will do a full scan to find all the records.
The second approach is to use the Where clause. Let’s look at the same example but using the setWhere method:
assetMboSet = session.getMboSet("ASSET")
assetMboSet.setWhere("ASSETNUM = '1000'")
This approach will return just the asset you are looking for. This approach would translate to this:
SELECT * FROM ASSET WHERE ASSETNUM = '1000'
The setWhere method basically gives you the ability to add a SQL ‘Where’ clause to the MboSet. In reference to performance, the ‘like’ method is slower as database has to look at the data in the ASSETNUM column to see if it contains the 1000 value, whereas the ‘=’ method just checks to see if the entire content of ASSETNUM matches.
Unless you need to perform a ‘LIKE’ method, you should always use the setWhere method as it will be faster. And, if you really need to use a ‘LIKE’ query, you can always use: setWhere("ASSETNUM like ‘%1000%’”), as it would do the same thing as setQbe does.
Now that I have shown that setWhere is better than setQbe, there is another and even better way to do this. That is to us a dynamic relationship. As you have seen, getMboSet method is passed the name of the MBO that you want to access. However, there is an overloaded version of this method that allows you to create a dynamic relationship. Using this, you can pass the condition as part of the relationship. This will allow the database to do the filtering instead of Maximo. The new code would look like this:
assetMboSet = session.getMboSet("$tempAsset","ASSET","ASSETNUM='1000'")
The first parameter is a temporary name for the relationship. Make sure it starts with a dollar sign. Next is the name of the MBO and the last parameter is the where clause (without the word 'WHERE'). This method has the smallest impact on Maximo's overhead.
Happy Coding