WHERE clause WOs: Get children too

5. RE: WHERE clause WOs: Get children too

Like

Andrew Jeffery

IBM Champion

Posted Mon November 23, 2020 03:25 AM

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
------------------------------

Original Message

Original Message:
Sent: Sat November 21, 2020 06:20 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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.

Original Message:
Sent: Fri November 20, 2020 03:50 AM
From: Andrew Jeffery
Subject: WHERE clause WOs: Get children too

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

Original Message:
Sent: Thu November 19, 2020 07:27 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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!

Original Message:
Sent: Thu November 19, 2020 07:19 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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?

#AssetandFacilitiesManagement
#Maximo

6. RE: WHERE clause WOs: Get children too

Like

User1971

Posted Mon November 23, 2020 08:26 PM
Edited by System Admin Wed March 22, 2023 11:54 AM

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.

Original Message

Original Message:
Sent: Mon November 23, 2020 03:25 AM
From: Andrew Jeffery
Subject: WHERE clause WOs: Get children too

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

Original Message:
Sent: Sat November 21, 2020 06:20 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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.

Original Message:
Sent: Fri November 20, 2020 03:50 AM
From: Andrew Jeffery
Subject: WHERE clause WOs: Get children too

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

Original Message:
Sent: Thu November 19, 2020 07:27 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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!

Original Message:
Sent: Thu November 19, 2020 07:19 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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?

#AssetandFacilitiesManagement
#Maximo

7. RE: WHERE clause WOs: Get children too

Like

User1971

Posted Tue December 01, 2020 04:33 AM
Edited by System Admin Wed March 22, 2023 11:44 AM

This is the current query:

with nt as (select * from maxsnb.wotaskrollup_vw) --non-task WOs
select
    wonum,
    parent,
    hierarchypath,
    classstructureid,
    worktype,
    status, 
    glaccount, 
    trunc(actstart)  as actstart,
    trunc(actfinish) as actfinish,
    actlabcost,
    actmatcost,
    actservcost,
    acttoolcost,
    acttotalcost,
    parent_group
from
    nt
where
    exists (select wonum from maxsnb.woancestor where wonum=nt.wonum and ancestor in (select wonum from nt where 
        --parameters go here
    ))
order by
    parent_group,
    case when parent is null then null else hierarchypath end   nulls first --parents first, children second

I've left siteid=nt.siteid out of the subqueries for now, since it was making the explain-plan-cost go up. I'll need to revisit this.

#Maximo
#AssetandFacilitiesManagement

8. RE: WHERE clause WOs: Get children too

Like

User1971

Posted Sat December 26, 2020 07:00 PM
Edited by System Admin Wed March 22, 2023 11:49 AM

For those of us that have purchased the book SQL Performance Explained by Markus Winand, the book can also be downloaded here:

http://docshare01.docshare.tips/files/29375/293750304.pdf

Original Message

Original Message:
Sent: Mon November 23, 2020 03:25 AM
From: Andrew Jeffery
Subject: WHERE clause WOs: Get children too

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

Original Message:
Sent: Sat November 21, 2020 06:20 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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.

Original Message:
Sent: Fri November 20, 2020 03:50 AM
From: Andrew Jeffery
Subject: WHERE clause WOs: Get children too

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

Original Message:
Sent: Thu November 19, 2020 07:27 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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!

Original Message:
Sent: Thu November 19, 2020 07:19 PM
From: User1971
Subject: WHERE clause WOs: Get children too

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?

#Maximo
#AssetandFacilitiesManagement

Maximo

Maximo