Maximo Open Forum

I'm working on a report and, admittedly, it's a behemoth.  Eight different queries, most of them against Work Orders (and I don't think I can narrow it down any further than that; it's looking at very different data sets -- and I already reduced it from more than 20 different queries).  If I run each query individually against the database, none of them take terribly long.  The worst was just over a minute.  The report has 10 pie charts, a bar chart, and several pieces of data that has to filter through and/or calculate upon the result set.  There's a lot of filtering through the result set going on before rendering it.

When I run it in Maximo -- granted, the production environment is just a database server for SQL Server and another server for Maximo & WebSphere; no clustering, no separate Java heap for reporting or Cron Tasks -- it takes HOURS.  Somewhere in the neighborhood of 8 hours.  And that's with my intervention.  I scheduled the report to run and email to me, and if I view it in Report Administration --> View Report Processing, I can see how long it's been going.

However, somewhere around the hour-and-a-half mark, it spins up another process to run the report.  I assume it's hitting some kind of timeout setting and tries to submit the report request again.  So I cancel that additional process.  If I don't, this could end up taking 24 hours to process, and it keeps spinning up additional processes.

So, short of making a separate report server, what can I do to make this better?

I assume my issue is going to be with the amount of compute needed to do all the filtering & rendering and I may just have to live with it for now, but I'm hoping for other ideas. . .?
#Reporting

------------------------------
Travis Herron
Pensacola Christian College
------------------------------

Hello Travis,

Can you provide details on these questions so that we can decide our plan of action-

1. Are you able to run this report w/o scheduling and getting the output?
    1.a. How much time does it take for you to get the output when you run it on the go w/o any scheduling from Run Reports.

2. Or This problem is happening only when you are scheduling the report?

3. Do you have multiple data sources involved in BIRT Report?

4. If you go to Report Administration> Performance >  Setting - what are the values for this report for these fields - Limit Record and Max Record Limit?



------------------------------
Prashant Sharma
EAM360
------------------------------

Original Message:
Sent: 06-07-2021 11:56
From: Travis Herron
Subject: How can I make my BIRT report run faster?

I'm working on a report and, admittedly, it's a behemoth.  Eight different queries, most of them against Work Orders (and I don't think I can narrow it down any further than that; it's looking at very different data sets -- and I already reduced it from more than 20 different queries).  If I run each query individually against the database, none of them take terribly long.  The worst was just over a minute.  The report has 10 pie charts, a bar chart, and several pieces of data that has to filter through and/or calculate upon the result set.  There's a lot of filtering through the result set going on before rendering it.

When I run it in Maximo -- granted, the production environment is just a database server for SQL Server and another server for Maximo & WebSphere; no clustering, no separate Java heap for reporting or Cron Tasks -- it takes HOURS.  Somewhere in the neighborhood of 8 hours.  And that's with my intervention.  I scheduled the report to run and email to me, and if I view it in Report Administration --> View Report Processing, I can see how long it's been going.

However, somewhere around the hour-and-a-half mark, it spins up another process to run the report.  I assume it's hitting some kind of timeout setting and tries to submit the report request again.  So I cancel that additional process.  If I don't, this could end up taking 24 hours to process, and it keeps spinning up additional processes.

So, short of making a separate report server, what can I do to make this better?

I assume my issue is going to be with the amount of compute needed to do all the filtering & rendering and I may just have to live with it for now, but I'm hoping for other ideas. . .?
#Reporting

------------------------------
Travis Herron
Pensacola Christian College
------------------------------

The spinning up multiple instances is due to the REPORTLOCKRELEASE cron task. This cron task causes scheduled reports to execute again if it believes it has failed. By default this occurs after 15 minutes (based on the LOCKINTERVALINMINUTES parameter) but we've bumped it up in some of our environments so I assume your organization has changed the number of minutes. 

When you tested the queries, did you get the exact queries from the report (by setting the BIRT logger under REPORT to DEBUG) or did you approximate what it would be? There are things that can be appended to queries (such as application restrictions, site restrictions, etc.) that can increase the time. You mentioned SQL Server and there are settings there that negatively impact performance too.

mxe.db.optionuse we suggest to disable but is enabled out of the box. This setting tells SQL Server to find an execution plan that will return X number of records the fastest. But every query in Maximo needs to complete, which means the fastest 1000 records is irrelevant. This has been shown to make queries slower with no evidence of it ever improving a query.

mxe.db.sqlserverPrefetchRows is trickier. If you set it to 0 (disabling the setting), performance of queries will be significantly faster. But, the catch is that your application can crash with heap dumps if you pull in large datasets at once. This setting limits how much of the dataset is pulled into memory. If you have 10 million work orders for example and someone uses all records, this will likely crash your application server. 

These settings I wanted to highlight as we often copy and execute queries in a SQL editor (such as SSMS for SQL Server) which won't always tell us the whole story. These settings can drastically impact the time it takes to complete.

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

Original Message:
Sent: 06-07-2021 11:56
From: Travis Herron
Subject: How can I make my BIRT report run faster?

I'm working on a report and, admittedly, it's a behemoth.  Eight different queries, most of them against Work Orders (and I don't think I can narrow it down any further than that; it's looking at very different data sets -- and I already reduced it from more than 20 different queries).  If I run each query individually against the database, none of them take terribly long.  The worst was just over a minute.  The report has 10 pie charts, a bar chart, and several pieces of data that has to filter through and/or calculate upon the result set.  There's a lot of filtering through the result set going on before rendering it.

When I run it in Maximo -- granted, the production environment is just a database server for SQL Server and another server for Maximo & WebSphere; no clustering, no separate Java heap for reporting or Cron Tasks -- it takes HOURS.  Somewhere in the neighborhood of 8 hours.  And that's with my intervention.  I scheduled the report to run and email to me, and if I view it in Report Administration --> View Report Processing, I can see how long it's been going.

However, somewhere around the hour-and-a-half mark, it spins up another process to run the report.  I assume it's hitting some kind of timeout setting and tries to submit the report request again.  So I cancel that additional process.  If I don't, this could end up taking 24 hours to process, and it keeps spinning up additional processes.

So, short of making a separate report server, what can I do to make this better?

I assume my issue is going to be with the amount of compute needed to do all the filtering & rendering and I may just have to live with it for now, but I'm hoping for other ideas. . .?
#Reporting

------------------------------
Travis Herron
Pensacola Christian College
------------------------------

Hi Travis,

As suggested by Steven, Please start from gathering actual sql queries from maximo logs. A lot of questions will be answered there. My suggestion will be to run the report in a environment with smaller data size like dev.

Next thing to do would be to check the nested sql queries created from your nested datasets. Often the nested sql queries are the root cause.
one more important thing will the use of siteid parameter in your queries. Also, some queries might need indexes for better performance.

------------------------------
Biplab Choudhury
Tata Consultancy Services
------------------------------

Original Message:
Sent: 06-07-2021 11:56
From: Travis Herron
Subject: How can I make my BIRT report run faster?

I'm working on a report and, admittedly, it's a behemoth.  Eight different queries, most of them against Work Orders (and I don't think I can narrow it down any further than that; it's looking at very different data sets -- and I already reduced it from more than 20 different queries).  If I run each query individually against the database, none of them take terribly long.  The worst was just over a minute.  The report has 10 pie charts, a bar chart, and several pieces of data that has to filter through and/or calculate upon the result set.  There's a lot of filtering through the result set going on before rendering it.

When I run it in Maximo -- granted, the production environment is just a database server for SQL Server and another server for Maximo & WebSphere; no clustering, no separate Java heap for reporting or Cron Tasks -- it takes HOURS.  Somewhere in the neighborhood of 8 hours.  And that's with my intervention.  I scheduled the report to run and email to me, and if I view it in Report Administration --> View Report Processing, I can see how long it's been going.

However, somewhere around the hour-and-a-half mark, it spins up another process to run the report.  I assume it's hitting some kind of timeout setting and tries to submit the report request again.  So I cancel that additional process.  If I don't, this could end up taking 24 hours to process, and it keeps spinning up additional processes.

So, short of making a separate report server, what can I do to make this better?

I assume my issue is going to be with the amount of compute needed to do all the filtering & rendering and I may just have to live with it for now, but I'm hoping for other ideas. . .?
#Reporting

------------------------------
Travis Herron
Pensacola Christian College
------------------------------