Hibernate version:3.0

Name and version of the database you are using: HSQL in server mode

I am having a very large input data file of about 100,000 records and I need to operate for each record. I first tried the easiest method to call query.list() but at this line I was getting out of memory. So I changed my code to use pagination for fetching, initially it seemed to work fine but after completion of some runs it again gave me out of memory. So what I understood from this and using a profiler to see memory usage is that although the memory required did not increase in one go, but it did rose to the same level as using query.list(). My second level cache is off. I just want to know, is there any way to optimize memory usage. I have already allocated 256M of memory and can not afford to give more.

Are you clearing your session (eg. calling Session.clear()) at regular intervals?

You can try to use Query.iterate() or Query.scroll() instead of Query.list(). Query.iterate() combined with regular Session.clear() should consume very little memory at the Hibernate level at the cost of more queries to the database.

Another option (that can be combined with the above) is to use a StatelessSession instead of a regular Session. Since a StatelessSession doesn't have a first-level cache it will use a lot less memory and you don't have to call clear().

Thanks nordborg!! I was calling session.flush() but was not clearing it. Also using iterative instead of list also saved a lot of memory...
I could not try with statelesssesion as some required features of normal session were not present. Thank you for your quick response!!