Jump to content
Changes to the Jaspersoft community edition download ×

RFE: query element

Recommended Posts

By: Carlos Costa e Silva - carloscs

RFE: query element

2003-12-11 09:19



Most times, the tables whith the data for the report only

have id's, not descriptions, while the report should print

a description, not the id.




Table order_lines:






If in the report I want to have the product/discount/vat

descriptions, the query string has to be something like:


select a.product_id, b.product_name

, a.discount_id, c.discount_description

, a.vat_id, d.vat_description

from order_lines a, products b, discounts c, vat_descriptions d

where a.product_id = b.product_id

and a.discount_id = c.discount_id

and a.vat_id = d.vat_id




And for each new id, the query gets even more complicated...



My workaround now:


I created a JasperSqlScriplet with this methods:


public Object sql(final String query);

public Object sql(final String query, final Object parameter)

public Object sql(final String query, final Object[] parameters)



And call with:


<variable name="ProductNameSql" class="java.lang.String">

....<variableExpression> "select product_name from products where product_id = ?" </variableExpression>



<variable name="ProductName" class="java.lang.String" resetType="None" calculation="Nothing">


........((JasperSqlScriplet) $P{REPORT_SCRIPTLET}).sql($V{ProductNameSql}, $F{product_id})






When a sql(...) method is called, the scriplet prepares and executes the sql.


For speed, the scriplet caches each query/parameter(s) results as it's

very probable that the result will be needed more than one time.




I'm thinking that it would be useful having a <query> element that would

automatically do this, as IMHO it's a frequently used.


Something like:


<query useCache='true'>

....<sql> select product_name from products where product_id = ? <sql/>

....<parameter> $F{product_id} </parameter>










By: Chuck Deal - cdeal

RE: RFE: query element

2003-12-11 11:01

But wouldn't this method be inefficient? I do see that caching the values would improve performance, but I can't imagine that this method (versus a single query that brings back all required data) would be very efficient.


Have you done any performance testing between the two methods?





By: Carlos Costa e Silva - carloscs

RE: RFE: query element

2003-12-11 14:23

Not noticeable.


There may even be a gain in performance:


1. For each query that you do this way, you have one less join in the main query. This greatly simplifies the resolver in the database.


2. If the database has to create a temp table for resolving the query the table will become much bigger if each row has several descriptions fields, there's more disk reads/writes for resolving the query.


3. A bottleneck while using databases is network speed between the database server and the report processing server. Reducing the data that has to travel through the net interface increases processing speed.


Also, first rule of performance programming : don't complicate things because you think performance will be better, most of the times there's no gain in performance and almost all of the time there's great loss in code readability.

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...