MAM 7.6.1.2; Oracle 19c:

I have an automation script that can be used to populate a custom CLASSSTRUCTURE.USEWITH_CONCAT column:

from psdi.mbo import MboRemote
from psdi.mbo import Mbo
from psdi.mbo import MboSet
from psdi.mbo import MboSetRemote
from java.lang.System import out

objList="";
useWithSet=mbo.getMboSet("CLASSUSEWITH")

if (not useWithSet.isEmpty()) :
    s=useWithSet.getSize()
    for i in range(s):
        useMbo = useWithSet.getMbo(i)
        if not useMbo is None:
            obj=useMbo.getString("objectname")
            if (i==s-1):
                objList=objList+obj
            else:
                objList=objList+obj+", "

mbo.setValue("USEWITH_CONCAT",objList,11L)​

(I didn't write the script. Feel free to criticize it.)

Example script output:
'SR, WOACTIVITY, WORKORDER'

Row counts:
CLASSSTRUCTURE has 2,756 rows.
CLASSUSEWITH has 5,209 rows.

---

I've noticed that the automation script is relatively slow. When the scripts is used to populate USEWITH_CONCAT in a CLASSSTRUCTURE record, it takes about 5 seconds to run/save.

In comparison, a SQL query that gets the same kind of information only takes 32 milliseconds to run (fast).

select 
    listagg(objectname,', ') within group(order by objectname) as usewith
from 
    maximo.classusewith
where
    classstructureid = '68070'
group by 
    classstructureid


---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     2 |    28 |     2   (0)| 00:00:01 |
|   1 |  SORT GROUP BY    |                   |     2 |    28 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| CLASSUSEWITH_NDX1 |     2 |    28 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CLASSSTRUCTUREID"='68070')

The explain-plan reveals that an index was used: CLASSUSEWITH_NDX1

---

Question:

I'm trying to figure out why the automation script is so slow. And I'm wondering, do automation scripts utilize database indexes when selecting records?
Maybe my script isn't querying properly / isn't using the database index properly.

Thanks.

#Maximo
#AssetandFacilitiesManagement

Hello,

In you sql query you are filtering the rows with classstructureid=68070, while in the automation script that you pasted, you are looping through all the rows from the MBO classusewith (I don't find any filtering here).

Also, I don't think in your case, there is any need for the "getSize" method (which can trigger count(*) from table). Instead you can simply do something following:

------------------------------
Pankaj Bhide
Associate/Analyst
Berkeley National Laboratory
Berkeley CA
(510) 486-4681
------------------------------

the automation script is using the CLASSUSEWITH relationship (from the MBO) to identify which records to process.
The query to retrieve the records will only be processed once so the explain plan will be used once.
.

objList="";
useWithSet=mbo.getMboSet("CLASSUSEWITH")

if (not useWithSet.isEmpty()) :
    s=useWithSet.getSize()​

Looking at the script I can't see a reason why it should be particularly slow although it is repeatedly extending a string.

Consider using timing code to check which parts of the script are actually slow.
measure the time in lots of places and you will quickly see where the slow operations are
--

9.20 OPERATION: TIMING PART OF THE OPERATIONS OF A SCRIPT
The time module can be used to calculate the start/end time for sections.

import time
startTime = time.clock()
# do something
endTime = time.clock()
runDurationSecs = endTime - startTime

this is taken from Vetasi's Automation script course

--


It may be better to put the string value in a list and then build a string from the list.
I would expect that to be a much quicker process because it won't involve repeated modifications to the string object.

L = []

L.append(string)

I have created a modified version of this from here:
https://thispointer.com/python-how-to-convert-a-list-to-string/

this shows how to build a string from a list.

re the comment about isEmpty causing leaks - see this posting which explains the behaviour.
Maximo

------------------------------
Mark Robbins
Support Lead/Technical Design Authority / IBM Champion 2017 & 2018 & 2019 & 2020 & 2021
Vetasi Limited
https://www.linkedin.com/pulse/maximo-support-advice-from-non-ibm-engineer-article-mark-robbins/
------------------------------

Original Message:
Sent: Fri November 12, 2021 06:40 AM
From: Pankaj Bhide
Subject: Do automation scripts utilize database indexes?

Hello,

In you sql query you are filtering the rows with classstructureid=68070, while in the automation script that you pasted, you are looping through all the rows from the MBO classusewith (I don't find any filtering here).

Also, I don't think in your case, there is any need for the "getSize" method (which can trigger count(*) from table). Instead you can simply do something following:

if (not useWithSet.isEmpty()):

            useWith= UseWithSet.moveFirst()

           

            while (useWith):

                ........  your logic
                .........    

                useWith= useWithSet.moveNext()

        useWithSet=None

I recently heard from the forum that "useWithSet.isEmpty()" causes memory leaks which I did not understand. They alternatively suggested using notExists method. 

------------------------------
Pankaj Bhide
Associate/Analyst
Berkeley National Laboratory
Berkeley CA
(510) 486-4681
------------------------------