Performance Tuning of Maximo NextGen Rest APIs

Mobile Applications or External System using Maximo NextGen Rest APIs for data exchange requires APIs to be performant in order to avoid slowness on mobile or external system side.

The basic building blocks for Maximo APIs are Object Structure provided by Maximo Integration Framework. These object structures can be used not only for Integration but also for Migration using Migration Manager and for Ad-Hoc Reporting in BIRT.

An Object Structure in Maximo Integration Framework is collection of 1 or more MBOs depending upon the fields which are required to be provided in API Response. If there are more than 1 objects needed, then they follow Parent-Child relationship and Child is linked with its Parent using Relationship based where clause. Behind the scenes, Maximo uses the where clause of this relationship and creates an inner query to fetch the required data from different database tables.

Though this provides the ability for users to add as many objects as they want to get the desired response, inability of Maximo to handle Join creates the problem when number of MBOs increases in the Object Structure as number of Inner Queries increases and which result in slowing down the API performance.

How The Performance of APIs Can Be Improved

Following the steps needs to be taken on Application as well as on database side for improving the performance of APIs-

• Review the Object Structure and remove any unwanted MBO that you don’t need. For example- if you are working with WO Details API MXWODETAIL then below are the MBOs part of OOB API given –
  Let’s consider, if your customer is not using TOOLs or SERVICEs or any other MBO then duplicate the OOB API and delete the unwanted MBO. It will eliminate the Inner Query generated and improve the response.
• Utilize Exclude Attribute Mode? Check Box and select only required attributes on each MBO. For example- You need to provide only WO Number, Description, WO Status and WO Priority from Work Order MBO in the API response, so we can check checkbox Exclude Attribute Mode? in front of Work Order MBO which will exclude all attributes from response. Then Go to Select Action > Include/Exclude Field and include ONLY those fields which are required in API.
  
  This will ensure that your API response payload doesn’t include all other extra fields and payload size of response remains short, helping the overall API Response time.
• Avoid OSLC.SELECT=* and pass field names to be received in API Response. As given in example in #2, if we need only WO Number, Description, WO Status and WO Priority for Work Order Mbo then we can pass the OSLC.SELECT=WONUM, STATUS, WOPRIORITY, DESCRIPTION exclusively rather than passing the * as * fetches all included attributes. Though we need to be careful with overall length of API parameters, especially if you are using the GET method, you are only limited to a maximum of 2,048 characters. Passing column names with OSLC.SELECT specially when there are more than 1 MBO is there, and each have multiple fields can increase the length of API parameters and after a certain point it will give length error. In such cases OSLC.SELECT=* can be used along with checking Exclude Attribute Mode? checkbox and then selecting required fields for each MBO as explained in #2.
• Review the relationship queries and tune them if required as database performance tuning best practices. Here are few examples-
  1. A query which uses NOT IN will result in Full Table Scan and it can be rewritten with IN clause.
  2. Avoid unwanted IN to avoid full scanning of tables. Query with clause STATUS IN (‘APPR’) can be rewritten as STATUS=’APPR’
  3. Avoid Order By clauses until necessary to meet specific requirements.
  4. Use UNION ALL rather than UNION.
  5. Avoid DISTINCT if you can.
  6. Review Index on tables and ensure columns which are included in where clauses have indexes.
• Review DOCLINK relationship if this MBO is added in Object Structure.
• Database Table Maintenance and Turning should be done for all MBOs which are involved in the Object Structure by running Gather Stats and Index Rebuild.

I will be writing in detail for #5 DOCLINK Relationship tuning and #6 Database Table Tuning in upcoming parts of this post. I hope this article will help you to troubleshoot and navigate through API performance issues in the future.

Author: 

PRASHANT SHARMA

Principal Solutions Architect