I am creating an adhoc view based on a domain. The query without filter brings in large amount of data and we are fine with the time it takes to load data. But while creating an adhoc view, the editor runs the query multiple times for adding columns, for grouping by a column, for adding a filter on a column. It takes really long time to create a filter and to group by a column even with sample data (vs. Full data) as the editor adds a group by clause. Using "No Data" is an option, but our users would probably end up trying the default option - "Sample Data". Building a adhoc view becomes a frustrating experience for users. Does anybody have similar issues? if so what are you doing about it? Is it possible to plugin our own query to list the values for filter list instead of having the adhoc editor run the dafault query that takes a long time?
3 Answers:
Few things to do here:
- The best one of course is a faster database - take a look at columnar database technology, this will give you a very fast boost! MySQL, SQL Server and now Postgres have columnar datastores available for them.
- There's the possiblity to work "in memory" instead of in-database as is the default mode. See the "Optimize Queries for Domain-based Reports" options documented here http://community.jaspersoft.com/wiki/configuring-ad-hoc-cache
- You might consider the "no data mode" by default - I'm sure that toggling this requires some change in javascript or jsp somewhere in the server (not sure where!)
Facing the same problem. Looking into using in-memory database and RAM drives (not SSD) if the data fits in memory.
There's also the problem with cache not being updated when underlying data changes but this is another issue. :-(
EDIT: Tried using columnar database without too much difference in performance. Performance problem is with Table - Group and with Crosstab - Columns. Filtering is also slow. The problem seems to be with the JasperReports Server software architecture because I can process over 2,400,000 statement details to generate over 120,000 pdf files with just souped up JasperReports library in under 10 minutes.
Database is returning result set in under 10 seconds but the JasperReports Server is just too slow processing the result and displaying.
I can confirm this issue.
When fetching data from the DB the fetch time is quick = Execution time 0.971 secs
Row amount = 87358
When opening ad hoc report with only 1 Measure and 2 fields selected with data set on sample the visualisation finish time on screen is 1.5 min
When changing data set to Full then the report re generates and takes another 3.1 min.
So fetching the data from DB is quick but visualizing on screen takes some time. When dropping or removing a field or measure, then again same time.
Tested with cache enabled an cache disable, even tested with a staged (topic) and still same issue.
Also when selecting a large data set then a lot of memory is being consumed. With my 87 358 and playing around with fields and measure my mem peak around 4G. That is for 1 user?
@Hozawa : "There's also the problem with cache not being updated when underlying data changes but this is another issue. :-("
You can disable the cache, which in my experience works, but generates another error regarding ehcache Jasper V 6.1
Or you can reduce cache refresh time by editing this file: adhoc-ehcache.xml
Edit the below option, which are time indication in seconds.
<cache name="adhocCache"
timeToIdleSeconds="180"
timeToLiveSeconds="30"
I implemented in one project a cache refresh of 10 and it works fine
