Jaspersoft Studio 6.5 Slow Performance with using Parameter

[bernlad]

Hi there,

I have a sql statement that run in a DB around 3s to complete.

When I run the report with the SQL like this "select xxx from yyy where x =  '123' and y = '456' and z = '789'" the report can be generate within few seconds.

However when I change the 123 to $P{P1} it still fast as we expected.

However when I change all fields by using parameter like this "select xxx from yyy where x =  $P{P1} and y = $P{P2} and z = $P{P3}"

then the report run for 30mins without completed.

Any idea whats happening ???

[joseng62]

Hi you would need to supply some more info regarding the problem solving you have done so far. 
Did you try and deploy the report onto a corresponding jasperserver version ? To see if it take same time to produce. When generating report on server yo ucan user browser dev tools to track time of server response, js scripts etc. 
Also you can enable slq logging on the jasper server level that would generate the acutal sql request with values being sent to the db server. Also will give you time on how long it took. 

Obviously if the db request is fast then, either must be a report design issue.

When you run the query with parameters in a db editor, check how many rows are you getting back in the query.  
Then when you run the query in jasperstudio, check that you are getting same amount of rows back or are they being duplicated.  

Also when you run the query with the where values hard coded, in the jasperstudio on report design level, do you get the same time response.  
If hard code query is faster with the sum of rows corresponding with the query result run on against the db, then might be something wrong with your parameter setup or data. 

Also how much memory did you allocate to jasperstudio, maybe increase if to low. 

Also you can add a return limit or > rownum maybe 50 to query to test faster. I have slight suspicioun that your report duplication data and thus the page mem count is exceedingly increasing. 

Hope all this helps a bit.  

 
 

[bernlad]

Hi Joseng,

Thx for your response. I didnt try deploy this int the server but I can try and see any sql logging.

The sql only return 10-20 rows back and its the sameamount rows no mater I use db editor, hardcoding or query in jasperstudio. Its much faster if I hard code so I believe its not relate to the number of records.

[bernlad]

Hi Joseng,

Thx for your response. I didnt try deploy this int the server but I can try and see any sql logging.

The sql only return 10-20 rows back and its the sameamount rows no mater I use db editor, hardcoding or query in jasperstudio. Its much faster if I hard code so I believe its not relate to the number of records.

[joseng62]

Ok so if you deployed to server and report is working on server, you can see the sql being produced by report in the following way. 
On the server under tab "Manage" ,  select "Server Settings". 
Then under the setting sub vertical menu, there is a tab "Log Settings" , this should be your default screen when going to Server Settings. 

In this tab you will see some log settings. 
1st columns is the name of the logger. 

So look for :

- SQL query executer ;
- net.sf.jasperreports.engine.query.JRJdbcQueryExecuter ; 
- Set the tab to debug level, not errror. (no need to restart server) 

Go run the report in the server again. 

After running report, go to JasperInstallDirectorywebappsjasperserverWEB-INFlogsjasperserver.log. 
All sql report uses will be logged to this file. 
You can enable more logging properties or add more if you need. 

Did you try all the other steps I recommended ? 

Also in the summary band in your're report, maybe add the parameters P1, P2 and P3 text elements to confirm that the correct values are being passed over 
2nd is the property of the logger. 
3rd is the log level.