Jasper Reports load very slowly

[Marius Nitu]

Hello!

 

We have a new installation of CA PPM 15.3 with Advanced Reporting.

JasperReports is installed on a separate server with no internet access. The database is Oracle, on a third server.

 

When we try to open any report (for example Capacity vs Allocation by OBS) the report opens, but then a popup appears with the message:

"Please wait

Loading..." and a Cancel button.

 

It takes about 2-3 minutes to open the report. Opening Ad Hoc Views also takes a while (but with no Cancel button).

 

Latest patches are installed for both CA PPM and JasperReports.

 

Please help! Thanks!

 

Marius

[Jeevan.B]

Hi Marius,

 

This could be a problem with the processing power of the server on which Jaspersoft is being hosted. So, check the amount of RAM allocated at the server level, and also check if there are other applications hosted on this same server, diverting the processing power.

[Marius Nitu]

Hello, Jeevan!

I have checked before making this thread. The amount of RAM and the processing power meet the recommended requirements. Also, there is no other application installed on the server.

The used memory is always below 2.5 GB (out of 8) and the processor is usually at 20%.

It is true that on the development server (another environment) PPM, Oracle and JasperReports are all installed and maybe that would be the reason for the slow loading, but not on for the main environment.

Also, I have another server on a virtual machine with PPM 15.1, MS SQL and JasperReports and the reports are loading normally.

[SHUBHAM BHATIA]

Hi Marius,

 

 

I have also face the similar slowness issue, you can set JVM Options for Jaspersoft Servers: Java Virtual Machine (JVM) runtime parameters. It would increase the performance.

This can be configure in setenv.bat file, below are the settings you can try them:

 

• Find the file in <js-install>/apache-tomcat/bin/setenv.bat and edit the file
• set JAVA_OPTS=%JAVA_OPTS% -Xms1024m -Xmx2048m -XX:MetaspaceSize=32m
• Update this line to increase the maximum heap size from 2048m to 3072m, for example: -Xmx3072m

 

Also you can check the hardware requirement for the Jasper server, if allocated RAM is full then you need to increase the RAM as well.

Let me know if it works for you.

 

Kind regards

/Shubham

[Jeevan.B]

There is also a KB around this subject here: How to Set JVM Options for Jaspersoft Servers .

[Marius Nitu]

Thanks, Jeevan! I tried setting Java Options.

I will try again on Monday.

[Marius Nitu]

Hello Shubham,

I have tried setting the Java Options.

This is what I have tried:

- runing startup.bat with default options and the reports load slowly

- installed Tomcat as a service with default options and it loads slowly

- increased maximum heap size in tomcatw.exe, no luck

- increased minimum and maximum heap size, still no luck

 

I haven't tried modifying setenv.bat and running startup.bat, but I will try it on Monday and get back to you.

 

Thanks for your help!

[Marius Nitu]

Hello,

I modified setenv.bat, stopped the Tomcat service and started Jasper with startup.bat. The syntax is a little bit different than it is in the Knowledge Base article that Jeevan provided. This is how it looks like:

 

set JAVA_OPTS=%JAVA_OPTS% -Xms1024m -Xmx3072m -XX:MetaspaceSize=32m
set JAVA_OPTS=%JAVA_OPTS% -Xss2m -XX:+UseConcMarkSweepGC
set JAVA_OPTS=%JAVA_OPTS% -XX:+CMSClassUnloadingEnabled

 

Tomcat version is 7.0.82

Java version is 8 update 152

 

The reports still take a long time to load.

 

Thanks!

[Suman Pramanik]

Hi Marius

 

The loading of reports is based on Data and you need to see the database side of it too, when a input control is loaded how long the query execution time is

 

Regards

Suman Pramanik 

[Marius Nitu]

Hello

 

The behavior is like this:

- I click on the report and a "Loading.. Please wait" windows appears for a few seconds

- the report starts loading and you can see the report page on the right and the left side where the parameter controls should be placed is empty. Then a new "Loading.. Please wait" window appears for 2-3 minutes. If the JasperReports server is shutdown during this time, the page stops loading, but no controls appear on the left side. Also, there is high CPU activity on JasperReports server during this time.

- the report finishes loading and you can select parameters. If you click "Populate Resources", for example you have to wait 1-2 minutes again

- also, in the end you have to wait for the actual report to be displayed 

 

So it might be related to the database side. What can I check on the database side?

 

Thank you!

[Suman Pramanik]

When you click any out of box report it will have the input controls loading the data, at that time you can ask the DBA to check the query running and if its picking the right database plan as those are nothing but SQL queries. 

 

Also if there is high CPU then we need to understand how many CPU you have allocated to the Jaspersoft server.  Couple of things you can do 

 

1. Ask the DBA to check if the statistics are upto date on the Jaspersoft schema.

2. Please mention what database you are using i.e .oracle or MS SQL

[Marius Nitu]

Hello

 

1. I will ask. The query 

exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => USER, degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => 100, cascade => true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

needs to by run with "ppmjasperadmin" instead of USER and under ppmjasperadmin (the user in the Reporting page in CSA), right?

2. It is Oracle

 

Thank you!

[Suman Pramanik]

Hi Marius

 

It needs to be run by the sysdba account, ppmjasperadmin is the user for integration and nothing to do at database layer for running stats. Before running the stats DBA need to check if there are any stale statistics.

 

Are you running database on virtual machine or physical box? 

 

Regards

Suman Pramanik 

[Marius Nitu]

So run 

exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => 'PPMJASPERADMIN', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => 100, cascade => true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

or

exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => USER, degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => 100, cascade => true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

?

 

Yes, database is on a virtual machine.

[Suman Pramanik]

We have seen inconsistent performance on the database being on virtual machine, try gathering the stats and see if you get some benefit 

[Marius Nitu]

There appears to be a minor impovement after we run the query and verified for stale statistics.

But the reports still load in more than one minute.

[Suman Pramanik]

Thats one part of it, having said so we don't support database on virtual box and we have seen inconsistencies in terms of I/O responses. So at this point I would say you would need to fine tume more at Database by looking at database reports like Ash and co relate with AWR 

 

Regards
Suman Pramanik 

[Marius Nitu]

The problem can be reproduced by using the following software versions:

 

CA PPM 15.3.0.2

Java 1.8.0_152

Apache Tomcat 8.5.24

 

Jaspersoft 6.2.1_5.2.1.4.33

Java 1.8.0_152

Apache Tomcat 7.0.84

 

It was solved by upgrading Jaspersoft to 6.4.2.