Performance issue in Work Order Tracking application

Body

Problem:
Performance issue while retrieving a Work Order with Tasks or navigating to Plans and Actuals tab.

Symptom:
SystemOut.log shows up a query similar to the following that takes long to be executed:
[mm/dd/yy hh:mm:ss] SystemOut [WARN] [MAXIMO] [] BMXAA6720W - XXX:USER = (XXX) SPID = (1251) app (null) object (DOCLINKS) :  select 1 from dummy_table where exists (select 1 from doclinks where

(ownertable='WOACTIVITY' and ownerid= XXX ) or (ownertable='WOACTIVITY' and ownerid in (select workorderid from workorder where parent= 'XXX'  and istask=1 and siteid= 'XXX' )) or

(ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum= 'XXX'  and siteid= 'XXX' )) or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where

location= 'XXX'  and siteid= 'XXX' )) or (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum= ''  and (siteid is null or siteid= 'XXX' ) and pluscrevnum = 0 ) ) or

(ownertable='PM' and ownerid in (select pmuid from pm where pmnum= ''  and siteid= 'XXX' )) or (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where

safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum= 'XXX'  and wosafetyplan.siteid= 'XXX' )) or (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid

from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass= 'ACTIVITY'  and relatedreckey= 'XXX'  and relatedrecsiteid= 'XXX' )) or (ownertable in

('WORKORDER','WOCHANGE','WORELEASE') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass='ACTIVITY'

 and relatedreckey= 'XXX'  and relatedrecsiteid= 'XXX' )) or (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid= XXX  and ownertable in (select value from

synonymdomain where domainid='WOCLASS'))) 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='WOCHANGE' and slarecords.ownertable='WOACTIVITY' and workorder.wonum= 'XXX' )) or (ownertable='JOBTASK' and ownerid= XXX ))  (execution

took 178214 milliseconds)

Resolving the problem:

Procedure:

1. Go to Database Configuration application.
2. Bring up WOACTIVITY object.
3. Go to Relationships tab.
4. Bring up DOCLINKS relationship.
5. Backup the existing Where Clause.
6. Copy and paste the modified relationship below in the Where Clause field.
7. Save.

Modified relationship: 
(ownertable='WOACTIVITY' and ownerid=:workorderid) union all select * from doclinks where (ownertable='WOACTIVITY' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1

and siteid=:siteid)) union all select * from doclinks where (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid)) union all select * from

doclinks where (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid)) union all select * from doclinks where (ownertable='JOBPLAN'

and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid  is null or siteid=:siteid))) union all select * from doclinks where (ownertable='PM' and ownerid in (select pmuid from

pm where pmnum=:pmnum and siteid=:siteid)) union all select * 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 all select * 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 all select * from doclinks where (ownertable in ('WOCHANGE','WORELEASE','WORKORDER') 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 all select * from doclinks where

(ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WOACTIVITY')) union all select * 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='WOACTIVITY' and slarecords.ownertable='WOACTIVITY'

and workorder.wonum=:wonum))