Query from JasperServer report is slow!

[irfa]

Hi

Im using jr-server 5.5.0 with MySQL on Linux.

When I execute query in db its working fast. But when i put same query in report, db take much more time to process it and return to jr server.

Query returns 7084 rows in more then 700 pages.

My tomcat config:

/opt/jasperreports-server-cp-5.5.0a/java/bin/java -Djava.util.logging.config.file=/opt/jasperreports-server-cp-5.5.0a/apache-tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Xms1024m -Xmx2048m -XX:PermSize=32m -XX:MaxPermSize=512m -Xss2m -XX:+UseConcMarkSweepGC -XX:+CMSClassUnloadingEnabled -Djava.awt.headless=true -Djava.endorsed.dirs=/opt/jasperreports-server-cp-5.5.0a/apache-tomcat/endorsed -classpath /opt/jasperreports-server-cp-5.5.0a/apache-tomcat/bin/bootstrap.jar:/opt/jasperreports-server-cp-5.5.0a/apache-tomcat/bin/tomcat-juli.jar -Dcatalina.base=/opt/jasperreports-server-cp-5.5.0a/apache-tomcat -Dcatalina.home=/opt/jasperreports-server-cp-5.5.0a/apache-tomcat -Djava.io.tmpdir=/opt/jasperreports-server-cp-5.5.0a/apache-tomcat/temp

I try to replace

<bean id="fileVirtualizerFactory" class="com.jaspersoft.jasperserver.api.engine.common.service.impl.FileVirtualizerFactory">
    <property name="maxSize" value="300"/>
    <property name="directory" value="${java.io.tmpdir}"/>
  </bean>

with

<bean id="fileVirtualizerFactory" class="com.jaspersoft.jasperserver.api.engine.common.service.impl.GZipVirtualizerFactory">
    <property name="maxSize" value="700"/>
  </bean>

and it wasn't better

[marianol]

Is the query fetch time or the report fill process that is taking most of the time? 

[irfa]

query taking most of the time. When i run report, Im looking in mysql with "show processlist" and take about 8 sec to execute. Same query directly in mysql execute in less than 1 sec

[marianol]

If is the query time in the MySQL backend Jasper has nothing to do there, and the changes you made to the report virtualizers will not help since those only affect the report fill time not the query time or fetch time. 

It may an issue with the JDBC driver for mySQL. JRS ships with the MariaDB JDBC driver for MySQL why don't you try changing it to the MySQL JConnect Driver? you can get it here http://dev.mysql.com/downloads/connector/j/5.1.html

Also think about the fetch time, once the query is executed the results (7K rows) have to be transmited to where the Jasper Server instace is so there are network/socket, try logging the queries @ the mysql side and also @ the jasper server side (for jasperserver log net.sf.jasperreports.engine.query.JRJdbcQueryExecuter=DEBUG see http://community.jaspersoft.com/wiki/log4jproperties-jasperreports-server)

[hozawa]

If you have the commercial version, try enabling the audit. Look at the Performance report.

[irfa]

Not regarding for this issue: Is MaxPermSize important for report fill time? Do I need to increase my PermSize for better performance? I have some reports with graphics taking more time to fill.

 

About this. Im already using JConnect Driver. It wasn't last version, but nothing changed with new. I try with MariaDB and its same. Ill try now with logging.

[irfa]

Hi

Again same problem. But now query return only 1 row.

It seams that query from report does not use index. I can find it in mysqld-slow.log file.

Row sent: 1 , Row_examined: more then 39M

In mysql workbench its fast, with "explain select..." i can see that use index.