Indexing columns in the database to speed up domain-based reports

Sometimes a domain-based report runs really slowly, for no apparent reason. One possible improvement would be to index some fields in the underlying database query. The way to proceed would be:

• Set the logging level to DEBUG for SQL queries - that will allow you to see the underlying query that the domain-based report generates that is then passed on to the database.
• Then run that query directly on the database to check for performance. You can then tune the database by indexing the field in the query that would have the best impact on report execution

Regarding materialized views: they are just SQL statements--you have to put indexes on the tables that pertain to the materialized view queries to get better performance from them.

You should actually get better performance on the switch from derived to actual tables, if you are using derived tables, because derived tables are sub-queries/sub-selects and are intensive performance-wise--much more so than a direct table access with some security level filters applied.

It's all about how the SQL ends up being generated by the domain as well as how the optimizer optimizes that SQL--coupled with the table indexes involved in the joins.

Other Performance Tips:

• Measure and Test
  • Test DB Queries with DBTester
  • Do Performance Testing with JMeter
  • Measure Ad Hoc Performance
• Ad Hoc
  • Set query limits on the "Ad Hoc Options" page
  • Modify the Ad Hoc caching values if your data does not update constantly
• Tomcat
  • Compress HTTP responses at the Tomcat level
  • Increase the maximum thread count at the Tomcat level
  • Set up a cache filter for Tomcat or JBoss
  • Disable automatic deployment on production servers using Tomcat
• Database
  • Set the fetch size in the jasperreports.properties file to modify the number of rows to fetch
• JasperReports Configuration Items
  • Modify Virtualizer Settings for Very Large Reports
  • Modify the jasperreports.properties file to disable multi-line data processing

• Back to JasperReports Server Performance Tips and Tricks