Out of memory when doing a big query?

Question

I received this error when trying to do big query.

java.lang.OutOfMemoryError: Java heap space

I've searched and found that applying setAutoCommit(false) and setFetchSize methods to my prepared statement might help handling big query. However, when I used it, I received this error.

java.sql.SQLException: Illegal value for setFetchDirection().

What is the proper and easy way to handle large query?

What is the proper way to use setFetchSize?

It would be easier to advise with sample code. The most common answer to this kind of thing though is to break the query into more managable chunks. Do you really need all this data at once? — Simon at The Access Group, Apr 26 '13 at 11:38
How many rows do you get while running the same query on `SQL` Console, also try increasing the `heap_size` for java — Akash, Apr 26 '13 at 12:08
possible duplicate of [Streaming large result sets with MySQL](http://stackoverflow.com/questions/2447324/streaming-large-result-sets-with-mysql) — RandomSeed, Apr 26 '13 at 12:25
@Simonatmso.net - I'm afraid that I cannot share that. But what I can tell is query string is not optimized and somewhat horrible. Too bad, it's not easy to modify it (it's already bad before I come to work on it ;P). — Atthapon Junpun-eak, Apr 26 '13 at 13:27
@Akash I've also tried "LIMIT 0,1" and it's still unable to query. I'll check heap_size. Sorry I forgot to mention that I use java, JDBC, and mysql. — Atthapon Junpun-eak, Apr 26 '13 at 13:28
@AtthaponJunpun-eak that makes it pretty hard to offer anything but generic advice then, such as that already offered by others. Specific advice to your problem generally requires an example of how you are trying to perform something, as there may be something in how you are performing something that someone else sees that is the cause for your problems. Up to you though really. — Simon at The Access Group, Apr 26 '13 at 14:12

score 4 · Answer 1 · edited Aug 05 '21 at 12:10

4

Assuming you are using the MySQL Connector/J driver provided by MySQL, I believe the solution is found in this manual page (notice parameter 1 of Connection::createStatement()):

If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

edited Aug 05 '21 at 12:10

Mark Rotteveel

100,966
191
140
197

answered Apr 26 '13 at 12:29

RandomSeed

29,301
6
52
87

Is it possible to use setFetchSize with prepared statement? – Atthapon Junpun-eak Apr 26 '13 at 13:22
Since `public interface PreparedStatement extends Statement`, I suppose it is. Likewise, I would consider using the method `Connection::prepareStatement(String sql, int resultSetType, int resultSetConcurrency)` to create such a `PreparedStatement`. – RandomSeed Apr 27 '13 at 15:30

score 0 · Answer 2 · answered Apr 26 '13 at 13:05

0

maybe do a lazy search, pull for example just ID's or something then when you want to use / display your data perfrom a query on just that one ID?

or maybe instead run it in a thread so it just goes off and does it in the background

answered Apr 26 '13 at 13:05

AngryDuck

4,358
13
57
91

Out of memory when doing a big query?

2 Answers2