Optimizing data retrieval in BIRT

Hello Everyone,

I had a question regarding BIRT data retrieval.

As per my understanding , whenever we send a sql query to the BIRT engine, it will process the query, gather the data, render the report and finally perform pagination. This works fine if the data that I am retrieving is relatively small.

But with large amount of data (I have couple millions of records returned by my query right now) performance is a problem. So i am wondering right now if there is a way in BIRT to limit the number of records returned for each iteration (lets say 100) of DB , and when ever the end of page is reached (100 record have been displayed to the user) , it would retrieve the next set of records from DB (next 100) and so on.

Is this possible with BIRT,or is there a work around ?

Thanks

Find more posts tagged with

Comments

johnw

Not easily. You basically have the right idea, where you will generate a RPTDocument file that contains all of your data, and the viewer makes calls to the render task to generate each page. If your integrating BIRT yourself, you can do what is called Progressive Rendering, where the pages can be called as they are ready while in the background the report engine is still retrieve the data. So, when rows 1-100 have been retrieved, you can call that page, while in the background rows 101-200 are being generated. This takes some programming. But it is available out of the box in Actuate 11.

You also have the option of parameterizing your query range depending on your database backend. I know off hand that in MySQL and Postgres, you can use a LIMIT statement. So in your BIRT query, you'd essentially have something like:
select * from table LIMIT ?, ?

where the first parameter will be the start position, and the second one will be the number of rows returned. Then you can do some scripting on your parameters to let the user navigate rows using a drill through and hyperlink. In terms of best performance, thats your best bet, and it doesn't require engine api programming to accomplish. Not sure how this could be done in oracle, I seem to recall using the row number variable, but they might have added a range in later versions of Oracle.

John

pawasarmol

Thanks John for a detailed answer , I kind off liked the suggestion of parameterizing. 
 
I have integrated the Web Viewer of BIRT in to my J2EE application , It would be great if you can pass on some pointer regarding how can I identify if we are on last page of the report and extract the next set of records from DB. As with the Web Viewer, the pagination and all is already taken care by the Web Viewer and and I am not sure how can I control the next-previous and also identify what page I am on right now. 
 
Any inputs on this will be really helpful as Web Viewer as the amount of code which I have to write with it is really negligible I would love to use it , rather than writing everything from my side. 
 
Thanks. 
 
<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="71738" data-time="1293489268" data-date="27 December 2010 - 03:34 PM">
Not easily. You basically have the right idea, where you will generate a RPTDocument file that contains all of your data, and the viewer makes calls to the render task to generate each page. If your integrating BIRT yourself, you can do what is called Progressive Rendering, where the pages can be called as they are ready while in the background the report engine is still retrieve the data. So, when rows 1-100 have been retrieved, you can call that page, while in the background rows 101-200 are being generated. This takes some programming. But it is available out of the box in Actuate 11. 
 
You also have the option of parameterizing your query range depending on your database backend. I know off hand that in MySQL and Postgres, you can use a LIMIT statement. So in your BIRT query, you'd essentially have something like: 
select * from table LIMIT ?, ? 
 
where the first parameter will be the start position, and the second one will be the number of rows returned. Then you can do some scripting on your parameters to let the user navigate rows using a drill through and hyperlink. In terms of best performance, thats your best bet, and it doesn't require engine api programming to accomplish. Not sure how this could be done in oracle, I seem to recall using the row number variable, but they might have added a range in later versions of Oracle. 
 
John </blockquote>

johnw

Well, unfortunately you wont be able to use the pagination in the web viewer, you would need to customize the viewer to work in this scenario. But the overall logic would be drop a hyperlink onto your report, with a parameter of the page number to retrieve. then your lower bound would be set by (page number * elements per page), and your upper bound would be set by (lower bound + elements per page). If your result set returns less than elements per page, you have reached the last page.

John

pawasarmol

Thanks a lot John. Appreciate all the help. 
 
<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="71808" data-time="1293776748" data-date="30 December 2010 - 11:25 PM">
Well, unfortunately you wont be able to use the pagination in the web viewer, you would need to customize the viewer to work in this scenario. But the overall logic would be drop a hyperlink onto your report, with a parameter of the page number to retrieve. then your lower bound would be set by (page number * elements per page), and your upper bound would be set by (lower bound + elements per page). If your result set returns less than elements per page, you have reached the last page. 
 
John </blockquote>