I misspoke a bit. The setFirstResult method that TopLink Essentials uses is ResultSet.absolute(int); Some drivers can effeciently ignore the results before the target index. Not the best solution I agree.
You are correct TopLink Essentials did not get the 'ROWNUM' enhancement that was developed in EclipseLink. If you are looking for that feature then I recommend moving to EclipseLink. Essentials was derrived from the same code base as EclipseLink and EclipseLink has the full TopLink functionality.
Having similar functionality in the database platforms that support it would be nice. Perhaps you could investigate the implemenation of the Oracle support and contribute support for other platforms? It should be as simple as extending the 'printSQLSelectStatement' method and migrating the 'shouldUseRownumFiltering' methods up the heirarchy.
--Gordon
-----Original Message-----
From: Jamey.Wood_at_sun.com [mailto:Jamey.Wood_at_sun.com]
Sent: Friday, February 22, 2008 3:33 PM
To: persistence_at_glassfish.dev.java.net
Subject: Re: Pagination Efficiency in TopLink Essentials
Hi Gordon,
Gordon Yorke wrote:
> TopLink passes these arguments directly to the JDBC statement when using Derby but there currently is no special processing for the derby platform.
I assume you mean that java.sql.Statement.setMaxRows(...) is being
called? There is no setFirstResult-like method in java.sql.Statement.
So I don't see how that argument could be passed to the statement. (And
thus, even if a JDBC driver were smart enough to implement this stuff
itself, I'm not seeing how it would have the necessary information.)
In any case... I think I'm looking for something different. As
<
http://forums.oracle.com/forums/thread.jspa?threadID=580901&tstart=0>
describes, it sounds like recent versions of Toplink and EclipseLink
have modified the OraclePlatform class so that it will generate SQL
queries that include appropriate references to Oracle's ROWNUM mechanism
so that the query only returns the request rows. Looking at the Toplink
Essential code in GlassFish, I see no such code. So I'm assuming it
does not implement a similar capability (for Oracle or any other DB).
Is that correct?
Is there any process available to request that this feature be ported
from Toplink to Toplink Essentials? Or do we need to do our own
implementation from scratch due to licensing issues?
> Can you propose how a specific solution would look when using Derby.
After a bit more investigation, I see that this probably isn't feasible
for current versions of Derby. It looks like they are working on a
ROW_NUMBER() feature which could probably be used to implement this in
the future, though. See:
https://issues.apache.org/jira/browse/DERBY-2998?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12565717#action_12565717
http://wiki.apache.org/db-derby/OLAPRowNumber
That being said, it would be very nice if Toplink Essentials would
implement this for any DBs which do provide a usable mechanism today.
Obviously, Oracle is one such DB (with its ROWNUM mechanism). I think
the "LIMIT" and "OFFSET" clauses could be used to similar effect in
MySQL and PostgreSQL.
Thanks,
Jamey
> -----Original Message-----
> From: Jamey.Wood_at_sun.com [mailto:Jamey.Wood_at_sun.com]
> Sent: Friday, February 22, 2008 1:05 PM
> To: persistence_at_glassfish.dev.java.net
> Subject: Pagination Efficiency in TopLink Essentials
>
>
> I have a JavaDB database table with several thousands of rows (each of
> which contains large blobs). When I perform a simple (non-native) JPA
> query against this table and use setFirstResult/setMaxResults calls so
> that only a few rows should be returned, the performance is very slow
> (~30 seconds). From a bit of web searching, I suspect this is because
> TLE's JavaDB back-end is not using the setFirstResult/setMaxResults
> values in the query itself (and is instead using them in its own
> in-memory post-processing after retrieving all rows from the DB). That
> appears to be what's described in this thread:
>
> http://forums.oracle.com/forums/thread.jspa?threadID=580901&tstart=0
>
> ...which says that "Both Oracle TopLink and TopLink Essentials now
> support ROWNUM range querying ... It requires the database platform to
> be configured for Oracle DB usage" (seemingly implying that similar
> mechanisms aren't supported for other DB types, such as Derby/JavaDB).
>
> Is this, in fact, the case? Oracle is the only backend DB for which TLE
> will perform this kind of efficient pagination?
>
> Thanks,
> Jamey
>
>
>