Performance Tips

When talking about api performance, we are mostly looking at reducing the number of calls that we are making from client (browser based power app/integration clients) to Maximo Server and Maximo Server to Maximo database. Below we discuss the common programming mistakes that are made during app or integration development regarding the usage of the apis.

Analyze using the network tab the requests that are being made from your application. You may see a bunch of duplicate requests which maybe the by-product of erroneous js coding/event handling.

For example - do not give a big pagesize when you are going to show only a few. General rule of thumb - make it 1:2 - so if you are planning to show only 10 - give a pageSize of 20.

For example say we are fetching assets and the request looks like oslc.select=assetnum,description,location.description,location.location,site.description,siteid - you would perform much worse than if the query was oslc.select=assetnum,description,siteid,location - why? This is because for a pageSize of N, the former is going to fire 2N+1 sqls to the database as opposed to the latter - which will be always 1 sql. The former select will fire a sql to location table anf site table for every asset it selects. So if you were getting 100 assets - you will get 1+100 (for locations)+ 100 (for site) = 201 sqls. Also pay attention that location is a native attribute of the asset table - so using location.location will just make it go and make 100 calls for nothing. You could have just done location instead. In this case however it does not make a differnce as you are already fetching location.description - so you would have taken that hit anyways.

This is a common mistake we do. We are selecting all attributes from an OS or Mbo - when we may need only a few.

This is a common requirement - we get domain descriptions for status and other domain bound attributes. For this, the rest api framework maintains a ML cache to store the descriptions for different domains. Whenever the framework detects the domain bound attributes - it automatically adds the description (from the cache) to the response json. Unfortunately we are seeing a lot of the rest api calls include a select clause that refers to the domain by a relationship - thus bypassing the cache completely -> resulting in yet another sql.

There is a simple api call to fetch count <rest collection url to the resource>?count=1. When we fetch count, we donot need to fetch anything else with it, ie we should not be using oslc.select or collectioncount=1 as they would result it mbos getting initialized which will result in unnecessary sqls.

If we need to fetch count for a set of child objects along with data from the parent, we can use the oslc.select=<parent attributes>, <relation name>._dbcount, where relation_name is the maximo relation name to the related object.

if we just need to fetch count of a child object with no data from parent, use the GET call like /os/<os name>/{id}/<relation to child object>?count=1

In an multi-tabbed application, there is no need to fetch data for tabs that are not visible yet. Just-in-time fetching helps improve the app loading performance.

This is similar to oslc.select in GET cases. We should look out for things that we do not need - for example domain descriptions, imagelib references etc which are automatically populated. We donot need to explicitly specify them. Specifying them are costlier. Also avoid doing properties=*.

One of the common mistakes we see is rest queries use of oslc.orderBy clause sorting on non-indexed attributes like say description.Sorting on attributes that is not indexed will have a performance cost on the query. While developing the apps, consider not sorting by default and just use the order that the database provides. Let the end user drive the sorting.

This is another area for optimization. We can set this query parameter to 1 and reduce the size of the json payload response in cases when we are requesting data from a big OS like MXAPIWODETAIL. This will remove the collection ref to child collections from the response json. We should leverage it in our list page collections.

Look for evaluating/filtering data at the server side as opposed to client side and only transfer required data to the client, effectively filter at the server as opposed to getting all data and then filtering on the client side.

This use case deals with situations where we are getting information from a related mbo on a collection of selected mbos. For example we try to get information on locations and sites for a given set of Assets. Now if from a collection of 100 Assets, we have 20 distinct Locations and 5 distinct sites – we will stil end up firing 100 + 100 = 200 sql queries for each asset loading the site and location individually. To optimize that we need to make sure we have entries in the apilinkedobject table for the relations in use from Asset->Location and Asset->Site. A sample Asset->Location is shown below. This tells the system that Asset is related to location using the “matchexpr""" which helps the system to maintain a transient cache (of locations) while creating the json from the collection of Assets.

This will help reduce the number of queries to just the needed 20+5 based on unique locations and sites.

Avoid fetching all children objects in an object structure at once. Consider fetching those as needed basis ie if the requested by the end user. For example to fetch PO such that we can show it in a UI table/List – we maynot need to fetch the POLINES and Terms. They can be fetched after the PO’s are populated and as the user wants to drill down into the individual PO’s. This rest api provides various ways to enumerate a child relationship and those can be leveraged for this.

This section discusses how to debug and generate the SQL for your rest calls.

One of the ways we can troubleshoot performance of an api call is by checking the amount of sql its generating. The simplest way to do that would be to enable thread logging for the context “oslc” and for the user you intend to use for testing. This can be done using the “Custom Logging” action from the logging app -> Thread Logging. Note you need to enable the logger type – in this case SQL. Note if you want to track other loggers feel free to set them up as well here for the “oslc” context. Once you start making the rest api calls – you should see a file getting generated under your application server working directory that will have a naming convention like “OSLC_XXXXX.log” and that will contain all the sqls and other logs that this api call generated