JPA and handling large data sets

This is not a rellay ObjectDB problem, rather a JPA problem. Our web-based DB supports Hibernate or JPA (ElicpseLink/ObjectDB). We implemented a export/import class to export the whole database or single datasets (domains) in a persistence layer/database independent format. This class works fine - DB exports form a Hibernate/MySQL base via the independent formt to ObjectDB is possible. But exporting large binary data sets is very slow. To avoid memory problems the binary data type is read step by step during the export with the  query/setFirstResult() & setMaxResults(). But for large data sets the way is slow. We have an own entity for storing binary data (pictures, documents).

Hint: The export enumerates all entities(=table) for exporting data.

An overview of this problem can be found here.
http://stackoverflow.com/questions/2761543/how-to-handle-large-dataset-with-jpa-or-at-least-with-hibernate

It seems we must rework our code using non standard features.

for Hibernate  - StatelessSession/ScrollableResults
for EclipseLink - ScrollableCursor

But which tricky way can we use to traverse all objects of an entity?

Peter

Using query result range (setFirstResult, setMaxResults) for this purpose is indeed inefficient, because some query processing is required also for all the result objects before the first result (in order to count them).

Much more efficient way is to replace setFirstResult with a constraint on the result order value.

For example, if your query returns results ordered by the primary key, use the value of the primary key of the last object in one batch as a constraint in the next query execution, which will retrieve only objects with larger primary key as the next set of objects.

Usually the problem is only with setFirstResult and using setMaxResults may be fine. If not check if you can retrieve ranges of objects by setting both lower and upper value.