basic performance comparison and questions

[dan_s]

Hi everyone,

While testing some basic reports with iReport (3.0 and 3.6) I am trying to figure out if it's my lack of experience or it's something that needs to be improved/adjusted in the 'filling' phase of the report generation.

Basically, my case is about a simple SQL script which I run in both SQL Developer and iReport on my laptop. The script returns about 5300 rows or 670 rows from an Oracle 11g database, depending what value its parameter gets.

While running in SQL Developer (which is a Java based client tool), the result comes out in 7.5s-9.5s or 3.0s respectively.

However, when using iReport to run the same script, 'Filling report' takes 45s-56s (5x-7x longer than SQL Dev) or 8.5s-9.5s (3x longer than SQL Dev) respectively. Since the results are very similar with either iReport 3.0 or 3.6, I am guessing that data retrieval part of iReport is about the same. Also the timings are practically unchanged, regardless what format I choose for the report (text, pdf, html, csv, and rtf have all been tested with various viewers).

I went a bit further and I checked the database for the execution plan used by each client: it is the same plan, so the slower response doesn't seem to come from the database. For the 9.5s response time of the SQL Developer, the execution plan indicates 9.3s, and therefore, the communication delay is only 0.2s.

Here are my questions: 1. Does anyone know about some tunable settings in iReport to shorten the 'Filling report' phase? 2. Has anybody got into a similar issue, and knows how it can be solved? 3. Is there a reasonable explanation as why the 'Filling report' phase is getting much slower when SQL Developer data retrieval is actually getting faster (more rows/sec)?

Any kind of feedback would be greatly appreciated,

Dan

Post Edited by dan_s at 12/23/2009 20:17

Post Edited by dan_s at 12/23/2009 20:20

[hussainian]

There is another question that I want to ask if a report fetches 10 pages from database, are these all pages loaded at the start or records are fetched from database page by page i.e. when the NEXT PAGE is clicked?

Thanks.

AsadULLAH.

[lucianc]

A report fill is not a mere SQL statement execution.  JasperReports produces pixel perfect reports, and for that it has to measure each text and determine how much space it takes.  This text measurement is an expensive operation (as a note, JR doesn't implement it itself but uses the AWT classes).  That's where the fill process spends most of its time.

Regarding the data retrieval, what you can try is to set an explicit fetch size via the net.sf.jasperreports.jdbc.fetch.size property.  Btw, I assume that when you tested the query in SQL Developer you instructed it to fetch all the data.

Regards,

Lucian

[dan_s]

I really appreciate the insight of your response, and yes, SQL Developer buffer limit was large enough to accommodate both data sets, 670 and 5300 rows.

Based on the details from your answer:

1. It only makes sense to tune up the SQL part when the report is short (a few pages) and the queried data set is large (SQL command takes far longer than 'fill report' phase)

2. Tuning the 'fill report' phase is not an option under current conditions.

3. The lengthy 'fill report' phase is likely to persist in future JR releases due to AWT classes, unless text-only reports (no graphs) dumped using Courier font (so alignment is not an issue) can be added as a desirable outcome when response time is a success factor. How easy would it be to add such output feature for plain text reports? Are there other 'fast classes' available so slow AWT processes can be avoided for plain text reports?

4. Since I'm more a report developer coming from a database developer background, should I become more involved with Java programming in order to get better results out of JR?

 

Thank you for your time, and Happy New Year!

Dan

Post Edited by dan_s at 12/30/2009 15:41

[lucianc]

JR doesn't include such a "fast" text measurer to be used for text reports.  It's something that we have occasionally thought about, but implementing it might be tricky due to what attributes measured texts are expected to have.  In any case, it would make sense to have something like that.  Log a feature request for it.

Java knowledge would allow you to dig into the JR sources and see whether you have some ideas on how to improve its performance.

Regards,

Lucian

[lucianc]

hussainian
Wrote:

There is another question that I want to ask if a report fetches 10 pages from database, are these all pages loaded at the start or records are fetched from database page by page i.e. when the NEXT PAGE is clicked?

JR reports need to be fully generated before being displayed in a viewer.  So the data will be entirely fetched by the time you see the first report page.

Regards,

Lucian

PS:  If your query is not directly related to the original thread topic, please post it as a new thread.