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