MAM 7.6.1.2; Oracle 19c; 150,000 assets:

I've noticed that Maximo is 10x slower at querying the DB than SQL clients like Toad, SQL Developer, etc..

Examples:

1. Select assets (click the bumblebee in the Assets list view):

select * from asset where siteid = 'SERVICES'

    - Toad: 0.5 seconds (500 records)
    - Maximo Assets list view: 5 seconds (20 records)

2. Perform an attribute query (OOB Assets advanced search):

select * from asset where
exists (select 1 from assetspec where  assetspec.assetnum=asset.assetnum and  assetspec.siteid=asset.siteid and  assetspec.classstructureid = asset.classstructureid  and assetspec.assetattrid= 'SPECIES_NAME'  and upper(assetspec.alnvalue) like '%ASH%' and  asset.status in (select value from synonymdomain where domainid='LOCASSETSTATUS' and maxvalue in ('NOT READY','OPERATING')))

    - Toad: 8 seconds (500 records)
    - Maximo Assets list view: 1.5 minutes (20 records)

Has anyone else noticed this issue? Why would Maximo be so much slower than an SQL client?

It would be understandable if Maximo were slightly slower due to additional overhead, security group filtering, etc. But 10x slower seems rather unacceptable in my books.

Thanks.
#AssetandFacilitiesManagement
#Maximo

I would look at what customizations you have in place (automation scripts, java classes, etc.). Going from 8 seconds to 1.5 minutes is extreme and I rarely see anything like that. And 150,000 assets being a little higher than the normal customer is nothing that should stress the platform. We have customers with 8 million work orders which is significantly larger and more complicated than assets.

Utilizing admin mode in a non-production environment is a good way to eliminate any automation scripts from being a factor. I would also test with a user that has full site access (the checkbox checked for all site access) as IBM appends a where clause for users that don't have access to all sites that can drastically impact performance without the user being able to see it. 

As you're aware, Maximo is creating the MBO in memory which means all the setup logic for the object and attributes occurs. Things like the initialize event of the object/attribute, though there are more such as setting the editability of the attributes. Sometimes this logic has to execute additional queries to function properly. So instead of 1 simple query, you may be executing thousands of queries against the database without realizing it. 

As an example, if you display or have an attribute that retrieves something through a relationship (such as something on ASSETSPEC) or a non-persistent attribute (like DESCRIPTION_LONGDESCRIPTION) Maximo needs to get that as well which is another set of queries that executes at least once per record. In the case of going through a relationship, that means going through all the business logic of that as well. On ASSETSPEC for example it has to retrieve data from ASSETATTRIBUTE to set some of the data needed on ASSETSPEC and we found a performance issue where the code executed a count when it retrieved ASSETATTRIBUTE which caused another query to get executed that didn't need to be executed. The time for each query wasn't bad, but when we were doing bulk retrieval of ASSETSPEC over the REST API for Anywhere it dramatically slowed the performance because we were doing it for hundreds of thousands of records at once. 

APM tools here are definitely a good troubleshooting step if you have that available as an option. Unless you're seeing long running queries in the logs that explain the significant time delay, it's tricky to analyze when there are individually quick queries that get executed many times. But definitely look to see what attributes you're displaying on the list tab and see if admin mode helps it because those two things would at least give you a direction in triaging the issue.

------------------------------
Steven Shull
Director of Development
Projetech Inc
Cincinnati OH
------------------------------