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