Hi, i have to export a huge amount of datasets. My whole app works with hibernate and spring.
I have to export 300.000 to 3.000.000 Datasets...

At the Moment i do it that way

here i get some problems with the memory... i cant load all data and than write it...

should i work with an iterator or
query.setFirstResult(..);
query.setMaxResults(...);

or whats the best way in sucha case?

thanks for you help...

We are using a StatlessSession and Query.scroll() with successful result. We are currently exporting well over 10 million rows with almost no memory overhead and as fast as the db can deliver the result.

* A StatelessSession doesn't have a first-level cache as the Session does. It may work with a Session if you call Session.clear() at regular intervals. If you use a StatelessSession make sure that many-to-one references are either proxied of fetch joined or the result is (many) additional queries.

* Query.scroll() is needed because Query.iterate() only selects the id:d in the first query and then uses an additional select to load each item. This can take a very long time.

* Query.setFirstResult() and Query.setMaxResult() together with Query.list() also works, but since you need to repeat the query multiple times with an increasing value for the Query.setFirstResult() it becomes slower and slower the more rows you need to export.

* Make sure that your database setup support server-side cursors. We ran into this problem with MySQL were the JDBC driver tried to load the entire result into memory. Before this was solved (by MySQL a couple of years ago) we were forced to use the Query.setFirstResult()/Query.setMaxResult() approach.

very helpfull, thank you.

i tried Query.iterate() at the beginning, it was realy slow, .. hibernate produced a select for every row...

i'm using Query.setFirstResult()/Query.setMaxResult() at the moment, i have no performance problems, 300k rows in 30 sec, at the moment thats ok

i'll try the Query.scroll() next week