Hi all,<br />
I haven't visited the forum for some time, but stumbled upon some Birt crosstab performance issues. I'd like to know your advice how to improve the situation.<br />
<br />
I use BIRT 2.3.2. I have a report with a crosstab and a chart. Report is connected to mysql database. I view report through web page (frameset/run). Test data return about 1600 crosstab cells.<br />
My report generation takes a few seconds (above 5) after choosing parameters. I have thought it was aggregation issue in database, so decided to put aggregated values in database as well. It didn't improve the situation much.<br />
Then I started sniffing mysql transaction and found out that:<br />
1. There are two identical queries sent to mysql, but they are calculated quite fast.<br />
2. After last response there's about 4s delay to "Request quit" from database, which reflects (more or less) report generation time.<br />
<br />
Then I tried switching from frameset to run (pagination on/off) - no improvement.<br />
<br />
Then I removed charts - no improvement.<br />
<br />
Then I removed conditional formatting on crosstab single cells. To my surprise it cut down 4s delay to 2.5s delay - quite significant improvement in percentage terms, but still quite slow.<br />
<br />
Then I removed some other rendering scripts, like:<br />
- setting and reading PGV<br />
- adding static text to column header<br />
- modifying cell content to show percentage change between cells<br />
And I removed hyperlinks created for every crosstab cell.<br />
<br />
Final result was 1s delay instead of 4s when I started debugging the issue. And viewing report became ... let's say, snappier.<br />
<br />
As all the functionalities are needed and they were removed only for debugging purposes, what can I do to get faster report generation? Switch to newer Birt version? Change reporting server to better machine? Anything else?<br />
I really didn't expect above changes to have so big effect on report generation time.<br />
<br />
Some code snippet:<br />
1. Setting PGV (onFetch):<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.setPersistentGlobalVariable(row["header"]+row["platform_id"], "");
reportContext.setPersistentGlobalVariable(row["header"]+row["platform_id"]+"2", "");</pre>
2. Conditional cell formatting (highlight conditions + set style):<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
temp = reportContext.getPersistentGlobalVariable(data["header"]+data["platform_id"]+"cell");
currentData = data["avg(samples_value)_Group1/platform_id_Group/id"]
if (temp == "" || temp == null) {
if (currentData == null) {
currentData = "";
}
reportContext.setPersistentGlobalVariable(data["header"]+data["platform_id"]+"cell", currentData.toString());
false;
} else {
if (currentData == null || currentData == "") {
false;
} else {
reportContext.setPersistentGlobalVariable(data["header"]+data["platform_id"]+"cell", currentData.toString());
temp = parseFloat(temp);
if (!isNaN(temp)) {
fivepercent = temp * 0.05;
if (data["ascending"] == 1) {
diff = currentData - temp;
} else {
diff = temp - currentData;
}
if (temp != 0) {
percentage = (diff) * 100/temp;
} else {
percentage = (diff) * 100/0.01;
}
reportContext.setPersistentGlobalVariable(data["header"]+data["platform_id"]+data["id"], percentage.toString());
if (diff > fivepercent){
true;
} else {
false;
}
}
}
}
</pre>
3. Changing cell text (cell onRender):<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
<method name="onRender"><![CDATA[percent = reportContext.getPersistentGlobalVariable(data["header"]+data["platform_id"]+data["id"]);
if (percent != null) {
numberPercent = new Number(percent);
if (numberPercent > -100000 && numberPercent < 100000) {
var num=parseFloat(this.getValue());
if (isNaN(num)) {
this.setDisplayValue("");
this.action.setDrillThrough(this.action.createDrillThrough());
} else {
displayValue=0;
if (num > 10000 || num < -10000) {
this.setDisplayValue(num.toFixed(0) + " (" + numberPercent.toFixed(2) + "%)");
} else if (num > 100 || num < -100) {
this.setDisplayValue(num.toFixed(1) + " (" + numberPercent.toFixed(2) + "%)");
} else {
this.setDisplayValue(num.toFixed(2) + " (" + numberPercent.toFixed(2) + "%)");
}
}
}
} else {
var num=parseFloat(this.getValue());
if (isNaN(num)) {
this.setDisplayValue("");
this.action.setDrillThrough(this.action.createDrillThrough());
} else {
displayValue=0;
if (num > 10000 || num < -10000) {
this.setDisplayValue(num.toFixed(0));
} else if (num > 100 || num < -100) {
this.setDisplayValue(num.toFixed(1));
} else {
this.setDisplayValue(num.toFixed(2));
}
}
}
this.getStyle().backgroundColor = this.getParent().backgroundColor
]]></method>
</pre>
<br />
<br />
Crosstab structure:<br />
- columns - 2 level grouping (a/B)<br />
- rows - 3 level grouping (c/d/e)<br />
<br />
Best regards,<br />
Robert
