Absolutely. For each child MBO Set retrieved, Maximo has to execute a new query. This is because the relationships reference attributes specifically on the record in question (IE assetnum=:assetnum and siteid=:siteid). If you need ASSET & ASSETSPEC for example, you may have 1 query executed to get all the assets but would have 1 query per asset to get the asset specs. So if you retrieved 9000 assets, to retrieve the asset specs Maximo would execute a minimum of an additional 9000 queries.
Using inventory as a specific example, the Inventory class during the init event (which fires whether you're on the list tab, REST API, start center, etc.) for LIFO/FIFO items will execute 1 or 2 additional queries. It gets the child object INVLIFOFIFOCOST utilizing relationship INVLIFOFIFOCOST_COND. It executes a count (which is the first query) and then if the count is > 0 it sets the LIFOFIFOCOST as the average cost of those cost records (which causes it to execute a query to retrieve those records).
If INVLIFOFIFOCOST had its own init (or similar like initFieldFlagsOnMbo) event that retrieved child objects, those would also fire so it can cascade. Specifications are a good example of this because to make the other attributes read-only, it has to retrieve ASSETATTRIBUTE. So ASSET retrieves ASSETSPEC which then has to retrieve ASSETATTRIBUTE to determine if ALNVALUE, NUMVALUE, etc. should be editable. If you have 9000 assets, each with 5 specs, you would execute
1 query to get assets, 9000 queries to get asset specs, and an additional 45,000 (9,000*5) to get the ASSETATTRIBUTE record.
This is why firing on appropriate events is critical. Sometimes people will use INIT at the object level for example but if it's just to set a single attribute, you're typically better to do INIT on the attribute so it doesn't fire unless that attribute is needed. If it's something you can do on save of the record that's even better.
------------------------------
Steven Shull
Director of Development
Projetech Inc
Cincinnati OH
------------------------------
Original Message:
Sent: Thu May 13, 2021 01:06 PM
From: User1971
Subject: Downloading Lookup data is slow
@Steven Shull
Would you be able to elaborate a bit more on this part?
"..if a script or the java code retrieves additional child objects it can slow it down because you could be executing an additional 9000+ SQL queries to get that data."
Thanks!
Original Message:
Sent: Wed May 12, 2021 08:22 AM
From: Steven Shull
Subject: Downloading Lookup data is slow
To answer your question generically, since Anywhere goes through the MBO layer, there are a lot of things that can impact the performance negatively including the underlying java classes to automation scripts. The first thing I'd always suggest is making similar REST API requests from a computer to see how long it takes for you with good network speeds.
An APM tool can help identify where most of the time is being spent on the request. For example, if a script or the java code retrieves additional child objects it can slow it down because you could be executing an additional 9000+ SQL queries to get that data. Even a query that executes in 5 milliseconds would add over 45 seconds to that process if there are more than 9000 records so it adds up quickly. We've found situations where IBM executes a count in their code for example which triggers a SQL statement to get executed and is almost never needed. If you find these you can typically open a case to get addressed as a performance issue.
Specifically regarding inventory, it is a slower data set than some of the other lookup data sets. 3 minutes for 9000 seems a bit high, but I might try to do some benchmarking here to see how long it takes and try to get an idea of where it's spending most of the time.
------------------------------
Steven Shull
Director of Development
Projetech Inc
Original Message:
Sent: Tue May 11, 2021 10:22 AM
From: Mathieu Guilmette
Subject: Downloading Lookup data is slow
Hi,
What can affect the time that it takes to download lookup data ?
To download 9000 additionalInventory it takes more than 3 minutes. I think that 9000 is not that many.
We have to add some 1=2 query on some resources because it's too long
Thank you
------------------------------
Mathieu Guilmette
------------------------------