How to improve DocLinks performances in Maximo/TPAE applications

During a performance assessment of a production customer environment I have set the mxe.db.logSQLTimeLimit system property to 2000 to log all long-running SQL statements (see this TechNote for details). Looking at the system logs I have noticed many entries like this.

select * from doclinks where (ownertable='WORKORDER' and ownerid=1065680) or (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where ...) execution took 4653 milliseconds

Five seconds each time you view a work order in Work Order Tracking application just to check if the there are any attached document is reeeaaally baaad!

Those long and heavy SQL queries are executed by Maximo whenever an application is opened or a new record is inserted into an application to determine if there are any documents attached to the object or one of its related records. This slow down significantly Maximo UI.
After having googled a little I discovered that the IBM System Performance Whitepaper suggests two approaches to solve this issue.

1. Set the enabledoclinkonload system property to false to turn off the doclinks queries.
2. Simplify the doclinks queries by removing unnecessary checks in unused child tables as described in this post.

Both approaches have their drawbacks.

1. By disabling the docklinks check the paperclick icon will always turned on as described in this TechNote. Users will loose a useful information and needs to manually check if there are attached documents. Furthermore, this setting will disable the query only when opening objects and not for saves.
2. Removing part of the SQL query will only slightly improve performances and can be practically a non effective solution when your users use Maximo at its best.

That's why I have decided to follow another path... optimize the SQL statement.

Doclinks queries is composed by several subqueries. You can find the docklink query for work orders in WORKORDER.DOCKLINK relationship definition in Database Configuration. Here is how it looks like after a little restyling.

(ownertable='WORKORDER' and ownerid=:workorderid) or
(ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) or
(ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) or
(ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) or
(ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid))) or
(ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) or
(ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) or
(ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) or
(ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) or
(ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WORKORDER')) or
(ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))

Looking at the SQL access plan I discovered I much inefficient is this way of searching records in the DOCKLINK table.

I have rewritten the previous where clause using a UNION clause and I ended up with this SQL.

doclinksid in
(
select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where ownerid=:workorderid or (parent=:wonum and istask=1 and siteid=:siteid))) union 
select doclinksid from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid is null or siteid=:siteid))) union 
select doclinksid from doclinks where (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid)) union 
select doclinksid from doclinks where (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) union 
select doclinksid from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass=:woclass and relatedreckey=:wonum and relatedrecsiteid=:siteid)) union 
select doclinksid from doclinks where (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WORKORDER')) union 
select doclinksid from doclinks where (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER' and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))
)

With this piece of code I went to Database Configuration and opened the DOCLINK relationship of WORKORDER object and I have pasted it in the 'where' field. So what were the results?

The average docklink check went from an average execution time of 4.5 seconds to 0.6 seconds with a 750% performance improvement. That's great!

If the above query is not giving the desired result you may also try using UNION ALL instead of UNION statements - thanks Elliot for the tip.

I have modified several docklinks queries defined in the Database Configuration with a similar approach. Many applications are now more responsive when opening and saving records and customer is now more than happy!

Source:http://maximodev.blogspot.com/2013/03/improve-doclinks-performances-maximo-tpae.html