Eclipse Community Forums: BIRT » How to avoid useless queries to be executed

Home » Archived » BIRT » How to avoid useless queries to be executed(In case of conditionally hidden elements, how to prevent query execution)

Show: Today's Messages :: Show Polls :: Message Navigator

How to avoid useless queries to be executed [message #1283594]

Thu, 03 April 2014 15:48

ing omini

Messages: 26
Registered: March 2012

Junior Member

Hi BIRT developers!

I've designed a parametric report in which each section can be shown or not depending on parameters configuration.

Example:

loop over states...
chart "population by city" --> only to be shown if param_city = true
chart "population by gender" --> only to be shown if param_gender = true

I'm using "visibility" properties to hide report section when not necessary, example when the corresponding parameter is false.
Anyway, my problem is that visibility only affects the rendering output, while the underlying dataset query is executed in any case, thus increasing the load on database and the report generation time.

As a workaround, I tried to optimize the queries by putting conditions like

select ...
from ...
where ...
and ? = 'true'

where the ? is replaced by the parameter which enables the section.
This is reducing the query time a lot, but not removing the query execution overhead completely (so the query is parsed, parameters are replaced, the execution is made, the fetch retrieves no rows, and so on...)

For reports looping over a lot of items, this useless overhead time cannot be ignored.

So my question is... is there any way of "skipping" a report element (rather than hiding it)?

Thanks in advance!

Report message to a moderator

Re: How to avoid useless queries to be executed [message #1284319 is a reply to message #1283594]

Fri, 04 April 2014 10:36

donino donino

Messages: 183
Registered: July 2011

Senior Member

We can do this by dropping on-the-fly useless elements. See this topic

Report message to a moderator

Re: How to avoid useless queries to be executed [message #1284395 is a reply to message #1284319]

Fri, 04 April 2014 12:20

ing omini

Messages: 26
Registered: March 2012

Junior Member

Great!
Simple yet powerful solution...
This solves my problem of skipping the generation of "unwanted" report sections.

Just one further step...
Is there a similar approach to hide a dynamic element (in my case an element dynamically created inside a list) after checking that all nested data elements provide no data.

In my previous example:

loop over states...
 grid
 chart "population by city" --> only to be shown if param_city = true
 chart "population by gender" --> only to be shown if param_gender = true
 end grid
end loop

I would like to hide the grid of a "state" in case I realize that the contained charts for that state are all providing no data.

Thanks a lot!

Report message to a moderator