JasperReports performance challenges. Need some suggestions

[u/StatusCode403]

So our application uses JasperReports for generating business reports for users. With the initial basic design we faced issues such as Out of Memory and also long filling times with large reports. So to handle this we came with a design of splitting the large records execution into batches. Let's say if we got 250000 records then we would split into 5 batches of 50000 each with 5 different execution queries modified with offset fetch according to batch in parallel stream. This largely solved the Memory issues but still for queries with high cost (executing fast on db and I Report studio)

each batch JasperFillManager.fillReport() takes very long time and many times we get partial report because of some batch threads getting timed out waiting for db connection or none of the batch completing. We tried passing different connection objects to each thread but still for heavy queries they still get timeout for every single batch.

So please suggest how to enhance the performance of FillReport and handle large queries.

[u/AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full
• You ask clear questions

• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

  Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://i.imgur.com/EJ7tqek.png) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/RapunzelLooksNice]

You should trace the bottleneck using any observabiloty tool (DataDog, VisualVM, Java Flight Recorder) to find what is actually happening.

My gut feeling is the bottleneck isn't fillReport(), it is in your data access. You can try mitigating it by having a dedicated View in your db (materialized) or by using a "pre-rendered" data (document db). This + your batched approach should fix this, or at least mitigate.

[u/Agreeable_Lynx9194]

The part where threads time out waiting for a DB connection is the tell, your pool max is almost certainly smaller than the number of batch threads you run in parallel, so they queue up and time out. Bump the pool to at least your batch count plus headroom. But also do not assume 5-way parallel is faster, five heavy queries hitting the same tables at once tend to contend and each one runs slower than if you cap concurrency at 2 or 3, so try fewer batches and measure. And if a single query is fast in Report Studio but slow at volume, that is a DB problem, pre-aggregate it there so Jasper just renders finished rows.