Community
Participate
Working Groups
Hi, I have a dataset which will run across one millon lines. Through that I am getting N number of aggregate measures and I am showing across N number of places in Report. So, Now the dataset query result is not caching, but running N number of times against datasource and taking too much time. What I require is running the data set query only one time and cache the results and display across N number of places. Thanks. Please give solution, what I needs to configure.
Hi, Can you attach the report design you use? You cannot define an aggregation measure in data set. The aggregations can only be defined in column bindings of a Table/List. Have you try to set the data set cache option in "Setting" page of data set editor? (If you are using an old version of BIRT the page is named "Cache Preference"). Thanks. Lin
Hi Lin, Thanks for prompt response. I have a single query which will retrieve the aggregate values like todaysum, yesterdaysum, thismonthsum, previousmonthsum, thisquartersum, previousquartersum, thisyearsum, previousyearsum, todayavg, yesterdayavg, thismonthavg, previousmonthavg, thisquarteravg, previousquarteravg, thisyearavg, previousyearavg etc for a given date parameter. When I am showing this dataresultset as table/list, it giving report immediately.(Say 1 min). When I am placing/spreading these values at different places as dataitems, it is taking too long time.(Nearly 10 mins). CPU consuming 99% to 100% resources for this time. I kept cache preferences / cache entiredataset is on. But I hope it will work only when previewing results. I am facing problem while viewing report in webviewer / HTML format / PDF format and also while previewing results. I hope there may be a configuration settings to cache dataset results while running report.. Waiting for positive feedback.. Regards, Lava Kumar
Hi Lava, I don't understand how you define a "single query" in the BIRT report designer.Do you mean define a data set with those XXsum, XXaverage as output columns using a SQL query?(The aggregation supported by BIRT only can be used in Table/List). Each item in BIRT with "Column Binding" or "data set binding" will be explained as a "query" and will lead to an query execution.If you have lots of such items in your report design then the execution of report would be lag. Why not just post a sample of your report and let I to investigate what we can do to improve the performance? Thanks. Lin
Hi Lin, It may be due to miscommunication. I have a query which will return all these required aggregate values. No aggregation will do in report. Just I will place each value in dataitem of report by using data set binding and filtering. (Ex: Total.first(dataSetRow["sumcol"], dataSetRow["daydate"] == "priorDaysum") ) You said that "Each item in BIRT with "Column Binding" or "data set binding" will be explained as a "query" and will lead to an query execution.If you have lots of such items in your report design then the execution of report would be lag." Once the dataset query is run against datasource and it will return the resultset. Suppose if I have nearly 50 such items in report design, the execution time of report will be more. Is it? Or, the dataset query will execute every time against each data item (In my case 50 times). Which one is correct assumption? If still needs, I will try to upload the report tomorrow. Thanks. Lava Kumar.
Hi Lava, As far as the data item has its own column binding,we will execute it as a data set query.So in your case we will execute this 50 times.This behavior will be enhanced in future, however not in this release(2.2.0). The reason that I ask you to attach the report design is to see whether some changes can be made to design so that improve the overall performance. As you know, in BIRT we have multiple approaches to achieve same goal,and different approaches produce different performance. Thanks. Lin
Hi Lin, Thanks for your response. I hope my query is answered in some way. I changed report design and combined all to show in same table. Time being, for me response time and load on server is important rather than report format. Regards Lava Kumar.
*** Bug 164799 has been marked as a duplicate of this bug. ***
Is there any way we can get the Severity of this bug bumped up and/or the milestone of 2.2.0 set? Having the query execute multiple times based on the number of data set bindings seems unnecessary to me and greatly degrades report performance.
Hi Todd, We explain data item with binding as a query, so define data item as so will result with query execution. Meanwhile, we are targeting for a new feature, which is called "report element data sharing", by using this feature you can share the data in a report item with other report items without re-execution of query.And by then you will be able to get what you want. I will update this bug as our new feature is ongoing. Thanks. Lin
The report item sharing feature has been deferred to post 2.2.0. So will this bug.
Lin: Do you have any idea when the milestone for this bug will be set? This is a very important feature that - when implemented - will significantly improve the execution time for many reports. Thanks, Todd
Hi Todd, The report item sharing feature is already available in BIRT 2.2.1. And we will have further enhancement to the problem in 2.3.0. Please refer to bug 205883 for details. Thanks. Lin
In 2.3 the data set query is only executed once per report design.
