java.lang.OutOfMemoryError: Java heap space in hibernate while picking 200 thousand rows

Question

I am using hibernate 4.3.10, java 1.8 and eclipse neon. I have been trying to execute the following statement in my code. But

java.lang.OutOfMemoryError: Java heap space

is thrown at the second line after a few minutes.

Query query = mySqlSession.createSQLQuery("select * from hops where processed is null");
List<Object[]> lis=query.list();

The problem is this query return about 200k results which is causing the problem. I have refered How to deal with "java.lang.OutOfMemoryError: Java heap space" error (64MB heap size)

after reading solutions from the above link, I changed the memory to 2 GB. Still there seems to be a problem. Any suggestions?

See https://stackoverflow.com/questions/33019211/do-query-loads-all-the-data-in-memory — rmlan, Aug 29 '17 at 12:40
Must change algorithm to use only current record (currently: get all records into RAM). BTW why use hibertate to classic queries? — Jacek Cz, Aug 29 '17 at 12:40
I need all those records to process at one time. Also i do not have a sequential identifier in the hops table where i could have picked say 10k first processed them and then the next 10k. — Tridev Chaudhary, Aug 29 '17 at 12:42

score 3 · Accepted Answer · answered Aug 29 '17 at 12:57

Depending what you're doing with the rows, the proper way to handle the situation is to use LIMIT to retrieve only a part of the results at a time.

You would then loop the query and process the resultset while rows are available.

SELECT * FROM hops WHERE processed IS NULL LIMIT 10000

When using non-native query, see @Stefano's answer.

score 1 · Answer 2 · answered Aug 29 '17 at 12:41

1

Actual Problem is returning such a big dataset. Even if you increase the process memory, you will run into this problem again when more data gets into the store. You may want to implement Paging here. That is get the finite and small set of data and then get next page on request.

answered Aug 29 '17 at 12:41

Sunil Singhal

593
3
11

Right. I had thought so but i do not have a field which can help me pick a small set and then another small set and so on – Tridev Chaudhary Aug 29 '17 at 12:44
It's like pick a batch of data where processed is null. – Sunil Singhal Aug 29 '17 at 12:51
Absolutely correct. I will set processed no null only once processed but as of now they all are null. – Tridev Chaudhary Aug 29 '17 at 12:54

score 1 · Answer 3 · answered Aug 29 '17 at 12:50

I think there are 2 ways:

1.Pagination ( select only N row per query )

Criteria c = session.createCriteria(Envelope.class);
List<Envelope> list = c.setMaxResults(10).list();

2.Scrollable Result ( similar to a JDBC ResultSet )

Criteria c = session.createCriteria(Envelope.class);
ScrollableResults scroll = c.scroll(ScrollMode.FORWARD_ONLY);
while (scroll.next()) {
    Envelope e = (Envelope) scroll.get(0);
    // do whatever
}

You can also use a StetelessSession

java.lang.OutOfMemoryError: Java heap space in hibernate while picking 200 thousand rows

3 Answers3