cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ferenc Kiraly <>
Subject Re: limiting rowset size in SQLTransformer
Date Fri, 04 Apr 2003 10:57:22 GMT

Torsten Curdt wrote:
> Hi!
>> The SQLTransformer lacks an important feature that is present in
>> the ESQL logicsheet. ESQL allows limiting the number of rows
>> returned from a database using <esql:max-rows> and skips
>> some rows using the <esql:skip-rows> tag.
>> I have implemented something similar for the SQLTransformer. The
>> patch is very simple and I think doesn't break any of the current
>> funcionality.
> The major feature of esql in this particular case is that it already
> limits the result set on the *database*! Your patch only skips the
 > generation of SAX events. As a first steps this is nice but it is
 > probably a magnitute slower.

ESQL still uses JDBC for accessing the database, just as SQLTransformer
does. I am not aware of the ability of limiting the results set
returned by JDBC apart from using apropriate SQL. And all databases
do not support limiting the number of rows returned by SQL SELECTs.
All major databases, such as MySql, Postgresql and Oracle do, but some
minor ones such as MS SQL Server do not. To my knowledge. JDBC fetches
statement.getFetchSize() rows from the database, and gets more when
needed, so I would't say I am _just_ limiting the generation of SAX
events. Most rows in large tables are never fetched. I can probably
use statement.setFetchSize(limit) to make it a little more efficient.
(Just tried this and it does make a difference.)

That said, I first had my problem implemented with ESQL, and did not
notice a big difference in performance after redoing it with
SQLTRansformer. The transformer is slower, of course, but the the time
dependence is the same O(N), where N is the numer of rows in the SQL
querry! Regardless of <esql:max-rows>! This maybe isn't the case on
most databses, but I cannot choose which one to use.

> To share this feature with esql the SQLTransformer should better use
> the esql helper classes.
> There was a discussion to merge the codebase of esql and the
> SQLTransformer into a new new sql logicsheet and a corresponding
> sql transformer (see [1]). Same syntax, same feature set. Or at least
> as close as possible.

Nice. But I need a solution soon.

> That way we wouldn't have to maintain two tools for basically the
> same thing.
> [1]

I agree that having two tools is a waste, but ESQL cannot do everything.
In particular, I wanted to write a logicsheet, that processes a
<browseable-table> tag. The logicsheet template takes parameters, such as
<offset>, <limit> and <query>. The values for these parameters come from
the request or from the session. I was not able to pass these parameters
to the logicsheet _and_ use them in ESQL at the same time, even though I
spent days on the problem and asked for help on cocoon-users. So I decided
to break up the problem into two steps. My XSP logicsheet produces input
for the SQLTransformer and so on... But this is off-topic here.

Realizing that SQLTransformer is an evolutionary dead end, I guess I will
revisit my original problem. There go a few more days :-(

My first choice was ESQL. And I also noticed SQLTransformer is absent from
Cocoon 2.1.



View raw message