Maximo Open Forum

SR Module takes between 1-2 minutes to open a SR Record from both the find service request and list tab.
It loads the SR list in a timely manner when you select a SR # is when it takes between 1-2 minutes to load.

Steps taken to resolve
updated statistics and refreshed index tables
imported out of the box xml file. 

Have a case open with IBM support and are stumped.

This is the only module with the issue all others function as intended.

This started after upgrading to Maximo 7.6.1.1

System Information

App Server  IBM WebSphere Application Server 8.5.5.14

Version

Tivoli's process automation engine 7.6.1.1-IFIX20200522-1523 Build 20190514-1348 DB Build V7611-365 HFDB Build HF7611-13

IBM Maximo Asset Management Work Centers 7.6.0.4 Build $build$ DB Build V7604-119

IBM Maximo Asset Management Scheduler 7.6.7.3 Build 20190514-1348 DB Build V7673-78 HFDB Build HF7673-12

IBM Tpae Integration Framework 7.6.1.1 Build 20190419-2330 DB Build V7611-01

IBM Maximo Spatial Asset Management 7.6.0.5-20200127-1155 Build 20190522-1410 DB Build V7605-09 HFDB Build HF7605-06

IBM Maximo Asset Management 7.6.1.1 Build 20190514-1348 DB Build V7611-01

IoT Connection Utility 7.6.0.2 Build 20190426-2206 DB Build V7602-07

Server OS Windows Server 2012 R2 6.3

Server DB Microsoft SQL Server 13.0 (13.00.4435)

 

​
#EverythingMaximo

------------------------------
Diane Nohner
City of Minneapolis
------------------------------

Hi Diane,

We experienced similar and rebuilding indexes, especially on the workorder table resolved it. From initial investigation we also removed some sections from the DOCLINKS relationship, in particular anything to do with the WORKORDER table and that did seem to improve it.

Otherwise it would be a case of using SQL Profiler or Activity Monitor within SQL Server to find long running queries

Below is an excerpt of the DOCLINK relationship of SR object

(ownertable = 'SR' and ownerid = :ticketuid) or (  ownertable='SR' and ownerid in (select ticketuid from sr where ticketid=:origrecordid and class=:origrecordclass )) or (  ownertable='PROBLEM' and ownerid in (select ticketuid from problem where ticketid=:origrecordid and class=:origrecordclass )) or (  ownertable='INCIDENT' and ownerid in (select ticketuid from incident where ticketid=:origrecordid and class=:origrecordclass)  ) or (  ownertable='WOCHANGE' and ownerid in (select workorderid from wochange where wonum=:origrecordid and woclass=:origrecordclass)  ) or (  ownertable='WORELEASE' and ownerid in (select workorderid from worelease where wonum=:origrecordid and woclass=:origrecordclass) ) or (ownertable='SOLUTION' and ownerid in (select solutionid from solution where solution=:solution)) or (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:assetsiteid)) or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:assetsiteid)) or (ownertable='WOACTIVITY' and ownerid in (select workorderid from woactivity where origrecordid=:ticketid and origrecordclass=:class)) or (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum in (select jpnum from woactivity where origrecordid=:ticketid and origrecordclass=:class))) or (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownertable='SR' and ownerid=:ticketuid)) 

------------------------------
Chris Kung
SRO Solutions
------------------------------

removed the references from the doclinks for the WORKORDER and it did not improve.

Have run the update stats and refresh index tables and has not improved performance.

Posted in the next response about the long running queries.
Any suggestions? 

------------------------------
Diane Nohner
City of Minneapolis
------------------------------

Original Message:
Sent: 02-16-2021 08:39
From: Chris Kung
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

Hi Diane,

We experienced similar and rebuilding indexes, especially on the workorder table resolved it. From initial investigation we also removed some sections from the DOCLINKS relationship, in particular anything to do with the WORKORDER table and that did seem to improve it.

Otherwise it would be a case of using SQL Profiler or Activity Monitor within SQL Server to find long running queries

Below is an excerpt of the DOCLINK relationship of SR object

(ownertable = 'SR' and ownerid = :ticketuid) or (  ownertable='SR' and ownerid in (select ticketuid from sr where ticketid=:origrecordid and class=:origrecordclass )) or (  ownertable='PROBLEM' and ownerid in (select ticketuid from problem where ticketid=:origrecordid and class=:origrecordclass )) or (  ownertable='INCIDENT' and ownerid in (select ticketuid from incident where ticketid=:origrecordid and class=:origrecordclass)  ) or (  ownertable='WOCHANGE' and ownerid in (select workorderid from wochange where wonum=:origrecordid and woclass=:origrecordclass)  ) or (  ownertable='WORELEASE' and ownerid in (select workorderid from worelease where wonum=:origrecordid and woclass=:origrecordclass) ) or (ownertable='SOLUTION' and ownerid in (select solutionid from solution where solution=:solution)) or (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:assetsiteid)) or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:assetsiteid)) or (ownertable='WOACTIVITY' and ownerid in (select workorderid from woactivity where origrecordid=:ticketid and origrecordclass=:class)) or (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum in (select jpnum from woactivity where origrecordid=:ticketid and origrecordclass=:class))) or (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownertable='SR' and ownerid=:ticketuid)) 

------------------------------
Chris Kung
SRO Solutions
------------------------------

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.
------------------------------

the webclient.enabledoclinkonload is set to "false".

When I looked at the log file for long running queries 
This is from the log file - 

[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)

 [WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)

I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds. 

Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc

select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'


in admin mode it did not change the results of the SR it still took 1-2 minutes to load.

Any other suggestions?

------------------------------
Diane Nohner
City of Minneapolis
------------------------------

Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.
------------------------------

Diane,

This may be a long shot, but do you have any Data Restrictions set up for SR or Ticket? Maybe there's a Condition-related issue causing the performance hit?

------------------------------
Tim Ferrill
Intelligent Technology Solutions, LLC
------------------------------

Original Message:
Sent: 03-04-2021 16:35
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

the webclient.enabledoclinkonload is set to "false".

When I looked at the log file for long running queries 
This is from the log file - 

[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)

 [WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)

I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds. 

Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc

select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'


in admin mode it did not change the results of the SR it still took 1-2 minutes to load.

Any other suggestions?

------------------------------
Diane Nohner
City of Minneapolis
------------------------------


Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.

Another potential troubleshooting step would be to attempt to pull a single record using REST/MIF/API. If that works without any issues it could point back to something with the UI/App XML.

------------------------------
Tim Ferrill
Intelligent Technology Solutions, LLC
------------------------------

Original Message:
Sent: 03-04-2021 16:51
From: Tim Ferrill
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

Diane,

This may be a long shot, but do you have any Data Restrictions set up for SR or Ticket? Maybe there's a Condition-related issue causing the performance hit?

------------------------------
Tim Ferrill
Intelligent Technology Solutions, LLC
------------------------------


Original Message:
Sent: 03-04-2021 16:35
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

the webclient.enabledoclinkonload is set to "false".

When I looked at the log file for long running queries 
This is from the log file - 

[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)

 [WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)

I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds. 

Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc

select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'


in admin mode it did not change the results of the SR it still took 1-2 minutes to load.

Any other suggestions?

------------------------------
Diane Nohner
City of Minneapolis


Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.

Those execution times are concerning. Talking about over a minute just to run simply queries from SCCONFIG and MAXGROUP tables, yet ok when ran from Quering tool.

You could try creating a brand new app in Maximo that pulls from the SR view or duplicate the existing SR and see what happens

Note. Take DB backup before you do this to restore afterwards as deleting an app requires Deletion Scripts in the database

------------------------------
Chris Kung
SRO Solutions
------------------------------

Original Message:
Sent: 03-04-2021 16:35
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

the webclient.enabledoclinkonload is set to "false".

When I looked at the log file for long running queries 
This is from the log file - 

[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)

 [WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)

I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds. 

Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc

select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'


in admin mode it did not change the results of the SR it still took 1-2 minutes to load.

Any other suggestions?

------------------------------
Diane Nohner
City of Minneapolis
------------------------------


Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.

I would caution that the Maximo logs for SQL time can be wrong. What I mean by that is Maximo is the timer, so when the server has high CPU utilization for example that prevent threads from being able to execute the query the timer is still active. So Maximo thinks the query took 61 seconds for example, but may have spent 60 seconds waiting to execute the query. We see this a lot pre-heap dump or core dump on JVMs where you'll see a bunch of queries that look to be taking insane amounts of time.

Tim's suggestion of trying to retrieve using the REST API (or creating a custom app based on SR) is a good idea to see if there is some UI customization impacting performance. While importing the out of the box XML eliminates some configuration settings, there are others (such as Conditional UI) that are stored in tables that are not impacted by the import.

On the SQL platform, we recommend disabling mxe.db.optionuse (setting to 0) in every system. This is where that OPTION (FAST 1000) comes from. It tells the database platform to use an execution plan that will return 1000 records the fastest. It sounds OK on paper, but the Maximo framework depends on the query to complete so you really want the database platform to execute the fastest execution plan (which is the default). We've seen queries go from <1 second to taking >8 seconds just by having this flag. We have never seen a query execute faster with it. We've been discussing this (and other SQL Server issues) with the IBM team.

Another SQL Server specific option that improves query performance, but can cause serious issues, is mxe.db.sqlserverPrefetchRows. IBM's performance recommendations suggest setting this to 200 but the application will always run faster if it's set to 0 (disabling the setting). BUT there's a reason that the 200 recommendation exists (though I'd suggest a higher number than 200). If you have a larger database, Maximo can heap dump when someone does something such as "All Records" inside of the Work Order application because Maximo will load too much of the data from the database in memory at a single time. This helps avoid that, but makes it much slower because of it. I don't think this is the cause of your issue but would be something I'd recommend evaluating.

------------------------------
Steven Shull
Projetech Inc.
------------------------------

Original Message:
Sent: 03-04-2021 16:35
From: Diane Nohner
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

the webclient.enabledoclinkonload is set to "false".

When I looked at the log file for long running queries 
This is from the log file - 

[WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (SCCONFIG) : select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc OPTION (FAST 1000) (execution took 82502 milliseconds)

 [WARN] [MXServer] [] BMXAA6720W - USER = (MAXADMIN) SPID = (71) app (null) object (MAXGROUP) : select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN' ) OPTION (FAST 1000) (execution took 60579 milliseconds)

I ran each select query from the above log file in the database on SQL Server and it queried no issues with in seconds. 

Select * from scconfig where (userid = 'MAXADMIN' and display =1) and ((scconfigid = 22529)) order by isdefault desc,description asc

select * from maxgroup where groupname in (select groupname from groupuser where userid= 'MAXADMIN'


in admin mode it did not change the results of the SR it still took 1-2 minutes to load.

Any other suggestions?

------------------------------
Diane Nohner
City of Minneapolis
------------------------------


Original Message:
Sent: 02-16-2021 09:01
From: Steven Shull
Subject: The SR Module takes between 1-2 minutes to open a SR Record #

I think Chris might be spot on. I would check to see if you have the system property webclient.enabledoclinkonload enabled (IE true or 1). This is one of the only settings I can think of that can negative impact performance opening a record that wouldn't occur on the list tab. If enabled, this reverts back to how it functioned in the Maximo 7.1 days where the attached document indicator changes whether or not there are attachments on the record. Because the image changes, it needs to complete the query before it will open the record. 

Beyond that, looking for slow running queries in the logs. Maximo by default will log anything that takes longer than 1 second or returns more than 1000 records so you should hopefully be able to track it that way. 

If it's not related to SQL query performance, I'd lean towards some sort of customization (such as automation script) that fires on initialize (either of the object or attribute) that doesn't do anything on the list tab. If you can recreate this in a non-production environment, try to put Maximo into Admin Mode and try it there. This will disable most of the automation scripts, which makes triaging easier. If it's quick in Admin Mode, it's probably an automation script.

------------------------------
Steven Shull
Projetech Inc.

I had a similar issue and found it was related to the Time Tracking section on the SR. I don't recall if IBM gave me a solution but I know I ended up removing the Time Tracking section and that solved the issue. I will have to check my IBM tickets to see if they ever gave me a permanent solution. You could export the application xml, make a copy, remove the Time Tracking section, and then import the modified application xml. If that doesn't help then just import the original application xml.

------------------------------
Adam Ames
Kwik Trip, Inc.
------------------------------

I reopened the case I originally had with IBM on this one and here is what they came back with. 

We were discussing with Development about this issue that you reported, and after investigating it, Development mentioned this is a known problem in the SR's Time Tracking table, and a solution cannot be provided via APAR since it would involve the extensive changes including schema changes in the db.

Potential workarounds are...

• If the Time Tracking table is not being utilized, remove it entirely from the presentation xml
• If Time Tracking is occasionally used, have the table collapsed by default when the record is initially opened so that it does not load unless the user needs it.
• Restrict the relationship called LABTRANS from Ticket to LabTrans by the transdate so less records are loaded in the table, e.g. transdate > (getdate() - 7) (show records max one week old)

It could be considered as an enhancement on the product, which means that this functionality is not currently present in Maximo, and that this cannot be fixed on the current version since the fix would require changes to the product architecture.

However, we understand your point and we see there is room for improvement.If you would like to submit an enhancement request.

------------------------------
Adam Ames
Kwik Trip, Inc.
------------------------------