RFE: query element

[2006 IR Open Discussion]

By: Carlos Costa e Silva - carloscs

RFE: query element

2003-12-11 09:19

Problem:

 

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.

 

Example:

 

Table order_lines:

product_id,

discount_id,

vat_id,

...

 

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

 

etc.

 

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>

 

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

....<variableExpression>

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

....</variableExpression>

</variable>

 

 

 

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>

</query>

 

 

Carlos

 

 

 

 

 

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.