Maximo

Maximo-ICON.png

Maximo

Learn how to increase the operational efficiency of the assets you manage, and improve overall equipment effectiveness by using IoT data and AI.

Maximo-ICON.png

TRIRIGA

Reduce the operational costs of the facilities you manage, and create more engaging occupant experiences through the application of IoT data and AI.

Maximo-ICON.png

Engineering

Learn how IoT data and AI are being applied to transform the end-to-end engineering lifecycle.

Expand all | Collapse all

WHERE clause WOs: Get children too

  • 1.  WHERE clause WOs: Get children too

    Posted 7 days ago
    Edited by User1971 7 days ago
    I have a WHERE clause in the WOTRACK list view:

    classstructureid = '60720'
    and status = 'APPR'
    


    For the WOs that were returned, I also want to get their child WOs too.

    What's the best way to do this?



  • 2.  RE: WHERE clause WOs: Get children too

    Posted 7 days ago
    Edited by User1971 7 days ago
    One way to do this is to wrap the WHERE clause in an EXISTS operator that references the WOANCESTOR table:

    exists (select wonum from woancestor where ancestor in (select wonum from workorder where

    classstructureid = '60720' and status = 'APPR'

    ) and wonum=workorder.wonum)


    The performance of that EXISTS operator is surprisingly good.

    I tried various other 'proper' ways of getting the children in a full-blown query in Toad, but EXISTS in the WHERE clause had the lowest explain-plan cost. I guess the saying, "It's not stupid, if it works" applies here!




  • 3.  RE: WHERE clause WOs: Get children too

    Posted 6 days ago
    With 40+ million WOANCESTOR records this might not be as good as you might think.

    When you are compiling a query the first thing you must do is understand the unique key of a foreign table which will match the attributes from the primary table you are querying from. In Maximo you will learn that the best way to do this is to understand whether the object/table exists at SITE, ORG, SYSTEM or other levels, which you can do in Database Configuration.

    WORKORDER object exists at the SITE level and so you should always join by both WONUM and SITEID. Forgetting to join to the SITEID for site level objects is incredibly common, so you are not alone.

    You haven't done this in your code snippet but this message is for other techies. DO NOT join site level objects including the ORGID. I remember one query which a colleague was working on, the query ran to about 5-6 pages in a single SQL statement and was taking way over 30 minutes, I think it was actually more than an hour. There were 20+ million work orders. My colleague asked me whether I could look at the query to see whether I had any ideas. It was riddled with clauses of ORGID=ORGID. Take that out, and it will probably cut the amount of time to run the query by about half, it did. For every subquery, the database was having to check whether for a large number of records the ORGID in one row was equal to the ORGID in the other. In Maximo, the SITEID is unique across all organizations, making a check on ORGID superfluous for two tables that are both at the SITE level, for example, finding the work orders belonging to an asset or location. 


    ------------------------------
    Andrew Jeffery
    ------------------------------



  • 4.  RE: WHERE clause WOs: Get children too

    Posted 5 days ago
    Edited by User1971 5 days ago

    Thanks Jeffery. What you're saying makes perfect sense.

    It's really strange though, when I add and siteid=workorder.siteid  to the end of the EXISTS query from my original comment, it actually got slower. I have no idea why though.

    Before adding and siteid=workorder.siteid: the Oracle explain-plan cost was 1,792.
    After adding and siteid=workorder.siteid: the Oracle explain-plan cost was 1,896.

    That's 5.5% more expensive. Clearly I have no idea how Oracle works.




  • 5.  RE: WHERE clause WOs: Get children too

    Posted 3 days ago
    Hi - Not sure where my reply over the weekend went. I'm assuming you received it, perhaps it was sent privately.

    Anyway the book I was referring to which I found useful in my near year on performance analysis on a very large database, 20million+ work orders, was called "SQL Performance Explained" by Markus Winand. ISBN 978-3-9503078-2-5. It is not a thick book, just short of 200 pages, but it makes it a relatively easy read for a difficult technical subject.

    It uses the Explain Plan to compare queries and helps to explain what the text in the Explain Plan means rather than just focusing on the end result. After I got more into Explain Plan I started to look more carefully at some of the earlier statements in the query rather than just the ones towards the end.

    My copy covers Oracle, SQLServer, MySQL and PostgresSQL.

    ------------------------------
    Andrew Jeffery
    ------------------------------



  • 6.  RE: WHERE clause WOs: Get children too

    Posted 3 days ago
    Edited by User1971 3 days ago

    Thanks Andrew. That really helps. I ordered that book this morning. Looking forward to reading it.

    And yes, I got your other message as a private message:

    "Learning to work with Explain Plan takes time. What I would do is learn what the words in Explain Plan actually mean. I spent a year on performance tuning, and that was one of my Eureka moments, when I actually understood the differences. I'll try and find the book I found for reference.

    Also, use a Maximo SQL log to trap the actual SQL performed. You'll find that there can be some big differences, Maximo will be adding to the query your security profile, Site access, Data Restrictions, Global or Application restrictions, etc. Use the Explain Plan on the result.

    My biggest tip on performance though will be to be very carefully about Workorder indexes, especially those involving the Status attribute. Indexes are easy to add, less easy to take out if you do not document why they were created in the first place. Workorder is often the largest table​. I've seen indexes involving a status field being greater than 1GB, that is a lot of disk IO when you change Status, even more so if it is also changing status on child work orders and tasks. More so again when there are more than 10 indexes involving the Status column !"

     

    Thanks again for your help. I've been struggling with this explain plan business.