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!

How to improve DocLinks performances in Maximo/TPAE applications

24 thoughts on “How to improve DocLinks performances in Maximo/TPAE applications

  1. Can you answer a question?

    The statement (rewritten relationship) with the union has a cost of 1608 on my system but the original only has a cost of 57.

    I know plans differ by untilization and size of different tables, but why SO MUCH?

  2. Thanks Bruno Portaluri's article. We learnt from it a lot.
    After adopted the mentioned SQL, I discovered that it is much slower than before. (We are using DB2 9.5)
    Original: 12 seconds
    Modified: 31 seconds

    And we aware that the problem is caused in the following parts:
    ====================================
    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
    ….
    ====================================

    If we changed the SQL into this way, Maximo would only need to take around 2 seconds (in my environment) to finish the attachment loading.
    ====================================
    doclinksid in
    (
    select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid=:workorderid) union
    select doclinksid from doclinks where (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)) union
    ….
    ====================================
    Share our experience to you all.

  3. I ran into this a while ago and found that union introduced other problems when main record has no attachments and related record has some.

    Change I made to improve performance was to change "IN" predicate on the locations table to "=".

    changed:
    or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid))

    To:
    or (ownertable='LOCATIONS' and ownerid = (select locationsid from locations where location=:location and siteid=:siteid))

    since the subquery can only return one record (locations table has a unique index on location,siteid)

  4. Hi Bruno,

    I took your query and managed to optimise the performance even more…

    When you select the workorderid and children, use a UNION ALL and dummy_table (standard maximo table):
    "ownerid in (select :workorderid from dummy_table union all select workorderid from workorder where parent=:wonum and istask=1 and siteid=:siteid)"

    Then use UNION ALL instead of UNION. This got our execution time down from ~5 minutes to a split second!

  5. Thanks for taking it into consideration! Did you notice the select from dummy_table? This is where the real time savings are, because it avoids running an "or" on every record in the work order table. It is especially noticeable when dealing with millions of work orders!

    Cheers, Elliot

  6. I have a problem with PURCHVIEW table relationship. When create a PR and attach a file in it, If I create one PO, the attachment is displayed indicating that the source is the PR.
    But when I create a from the PO, I do not see the attachment.
    Where I'm missing?
    Follows the relationship of the object purchview:
    I added the last line according to his example
    (ownertable='PURCHVIEW' and ownerid=:contractid)
    or (
    ownertable='RFQLINE' and ownerid in (select rfqlineid from rfqline where contractnum=:contractnum and orgid=:orgid))
    or
    (ownertable='PRLINE' and ownerid in (select prlineid from prline where contractnum=:contractnum and orgid=:orgid))
    or
    (ownertable='COMPANIES' and ownerid = (select companiesid from companies where company=:vendor and orgid=:orgid))
    or
    (ownertable = 'PR' and ownerid in (select prid from pr where prnum in (select prnum from prline where ponum=:ponum and positeid=:siteid)))

  7. Hi @Prakash Kumarasamy ,Did you find any solution for the issue?
    We are facing an issue similiar to yours.
    Please do let us know on the resolution part.
    Thanks in Advance,

    Ramesh

Leave a Reply to Prakash kumarasamy Cancel reply

Your email address will not be published. Required fields are marked *

Scroll to top