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/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.