August 31, 2012

MBO Performance Tip N.5 - Avoid using setQbe method, use setWhere instead

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

The MboSet.setQbe() method is designed to be used to build filters from the user interface. Using the MboSet.setQbe() method will automatically add jolly characters in text searches preventing the database server to be able to use indexes.
For example let's look at the following code snippet.

MboSetRemote assetMboSet = session.getMboSet("ASSET");
assetMboSet.setQbe("ASSETNUM", "1000");

This tells the TPAE server to execute an SQL query like this:

SELECT * FROM asset WHERE assetnum LIKE '%1000%'

This will lead to an inefficient table scan.
Therefore, where conditions on a MboSet should be set using the setWhere() method unless you explicitly need the functionality provided by setQbe().

3 comments:

  1. You're partially right, but this behavior can be changed by calling Mbo.setQbeExactMatch(true). Filter values will be used as-is and SQL query will be performance effective.

    ReplyDelete
  2. Hi what does this means

    setQbe("historyflag", MXFormat.getDisplayNoValue(mxSession.getLocale()));

    ReplyDelete
  3. Also, setQbe() may be useful when you want to prefilter and MboSet but then allow user to remove / amend default filtering (e.g. when the MboSet is displayed in a table with enabled filtering, like in the MboValueAdapter.getList() method). In this case Qbe conditions will be visible in the table's filter

    ReplyDelete