March 22, 2013

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!

22 comments:

  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?

    ReplyDelete
    Replies
    1. How many rows do you have in the DOCLINKS table?
      What database server are you using?

      Delete
  2. Oracle DB server

    DOCKLINKS = 15889 Rows

    WORKORDER = 37204538 Rows

    ReplyDelete
    Replies
    1. It is very strange that you are experiencing the opposite behavior.
      I have used this technique at least three times with excellent results.

      Delete
    2. Todd,
      Do you know if you have a maintenance job active to analyze or compute statistics on Oracle?

      Delete
    3. Yes we run Stats on a weekly basis, during a "quiet" time.

      Delete
    4. Has anybody tried this trick?
      Can you please share here your result?

      Delete
    5. Thanks Bruno!
      COUNT(DOCLINKS) = 850 000
      Mx STD execution time = ~7000 ms
      Mx CUST execution time = ~17 ms

      Delete
  3. We recently implemented this on our newly upgraded 7.5 environment and can attest to the dramatic improvements..

    ReplyDelete
  4. do you have any similar queries for DB2 database ?

    ReplyDelete
    Replies
    1. The above queries should work fine with DB2 also.
      Can you please try?

      Delete
  5. 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.

    ReplyDelete
    Replies
    1. 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)

      Delete
  6. I tried it but its only working in database if you implement this query in maximo then its showing null attachments.

    ReplyDelete
  7. Can anybody help me to find out the reason that why its not working in maximo...if I am using this union query then I am not able to view any attachment

    ReplyDelete
  8. Hi Nitin,

    What database are you using? Is an error displayed in Maximo?

    ReplyDelete
  9. 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!

    ReplyDelete
    Replies
    1. Good news!
      I have updated the article with your suggestion.
      Thank you

      Delete
    2. 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

      Delete
  10. 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)))

    ReplyDelete
  11. Appreciate your work , Thanks Bruno

    ReplyDelete