MAM 7.6.1.2:

I've noticed that deleting a CLASSUSEWITH record (where object='WORKORDER') is surprisingly slow -- 15 seconds. But deleting CLASSUSEWITH for other objects is fast.

I'm wondering if the slowness is because Maximo searches the WORKORDER table for the CLASSUSEWITH's CLASSSTRUCTUREID. And if it finds any records, then the CLASSUSEWITH record can't be deleted. Is that what happens behind the scenes?

select
    1
from
    maximo.workorder
where
    classstructureid = '78070'​

When I think about it, it doesn't make sense why that process is so slow. This particular environment does have 350,000 WOs, which is a lot, but I think the DB should be able to handle the query better than it currently does.

When I look at the WORKORDER table, I notice that it doesn't have an index on CLASSSTRUCTUREID. I'm wondering if that's the reason for the slow performance. And I wonder, did IBM deliberately choose to not create that index? Or was it an oversight?

#Maximo
#AssetandFacilitiesManagement

For most CLASSUSEWITH it checks for existence using the relationship on CLASSSTRUCTURE (the parent object, not CLASSUSEWITH) that matches the OBJECTNAME, WORKORDER in this case. It's not looking just for an exact match but also looks for ones below it in the hierarchy. For example, if you are trying to delete the CLASSUSEWITH for the classification WORKORDER\FACILITIES and have WO on WORKORDER\FACILITIES\HVAC Maximo can't allow that either. 

You are probably correct that the query is slow. There are indexes for CLASSSTRUCTUREID on some objects but the ones that are most commonly used (ASSET, ITEM, LOCATIONS, WORKORDER, etc.) do not have an index out of the box. I can't think of other scenarios out of the box where this is searched on so it's one of those subjective decisions to make. Sometimes it's better to have a poor performing query than add the overhead of an index, especially on a busy table like WORKORDER. But if your users search on it for example then it is something I'd probably index.

------------------------------
Steven Shull
------------------------------

Original Message:
Sent: Tue December 07, 2021 01:14 AM
From: User1971
Subject: DB Performance: Deleting a CLASSUSEWITH record

MAM 7.6.1.2:

I've notice that deleting a CLASSUSEWITH record (where object='WORKORDER', or an object that extends WORKORDER) is surprisingly slow -- 15 seconds. (But deleting CLASSUSEWITH for other objects isn't slow.)

I'm wondering if the slowness is because Maximo searches the WORKORDER table for the CLASSUSEWITH's CLASSSTRUCTUREID. And if it finds any records, then the CLASSUSEWITH record can't be deleted. Is that what happens behind the scenes? Possibly using EXISTS? (exists stops when it finds something, which is efficient)

select    1from    maximo.workorderwhere    classstructureid = '78070'​

It doesn't make sense to me why that process is so slow. This particular environment does have 350,000 WOs, which is a lot, but I think the DB should be able to handle the query better than it currently does.

When I look at the WORKORDER table, I notice that it doesn't have an index on CLASSSTRUCTUREID. I'm wondering if that's the reason for the slow performance. And I wonder if IBM deliberately chose not to create that index, or if it was an oversight. I know I would be able to easily create the index via db config,  but I'm hoping to get some info about the root issue first.

Thanks.

#Maximo
#AssetandFacilitiesManagement