2

what I'm trying to do is to read 1,048,575 records from Oracle DB. The number is not random, it's the max number of rows in Excel file reduced by 1 (I want to have all rows filled + there must be a title row).

My project is built on Spring 4. I'm using org.springframework.data.querydsl.QueryDslPredicateExecutor to get data from database, so basically paging, sorting and QueryDslPredicates are required.

When I tried to get all 1,048,575 records I got exception: java.lang.OutOfMemoryError: GC overhead limit exceeded. After googling it for a while (for example there) I haven't found any memory leaks. After splitting all records into 100k chunks, it crushed between 500k and 600k.

Project is run on Jetty 9.3 with the following arguments:

-Xms512m -Xms2048m -Xmx2048m

DB is configured well, it has proper structure and required indexes. Let's say it's immutable.

Having all those in mind, is there any possibility I can actually read that data without increasing heap size?

Community
  • 1
  • 1
MrBo
  • 19
  • 3
  • How do you write the excel file? Also what is the point of the excel file (I mean who need 1M lines in an excel file)? –  Dec 02 '15 at 17:12
  • Possible duplicate: http://stackoverflow.com/questions/7195048/writing-a-large-resultset-to-a-file –  Dec 02 '15 at 17:15
  • I'm writing the file using Apache POI. I must guarantee users they can download data in .xlsx format. – MrBo Dec 02 '15 at 17:16
  • see Ruan Rojas's answer in the duplicate –  Dec 02 '15 at 17:18
  • you mean the 1 accepted? Can't see Ruan Rojas anywhere – MrBo Dec 02 '15 at 20:26
  • @RC: in the duplicate (http://stackoverflow.com/questions/7195048/writing-a-large-resultset-to-a-file) the only answer was written by BalusC. Were you referring to another question? – Bob Jarvis - Слава Україні Dec 02 '15 at 20:31
  • as I understand, you are trying to read these rows from DB in order to create Excel file for your users. What do you think of using PL/SQL procedure while generating .csv file directly on DB site. It could avoid your problem. – Martina Dec 02 '15 at 22:14
  • Sorry, wrong link, see http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi?lq=1 –  Dec 03 '15 at 05:37
  • @RC. answer provided in that link is fine for further process - filling the excel file. The problem is, I can't read data from Oracle. – MrBo Dec 03 '15 at 08:25
  • @Mina I'll try it out, thanks – MrBo Dec 03 '15 at 08:26
  • reading data could be done by small increments, like 10000 lines a time, the only issue is the time it will take. –  Dec 03 '15 at 08:42
  • @Mina I have only jdbc connection, I have no access to files generated by DB. – MrBo Dec 04 '15 at 14:11
  • @RC. combining all your tips brought me the solution - 100k chunks a time and Ruan Rojas' answer were enough. Thanks! – MrBo Dec 04 '15 at 14:14
  • Good news, don't forget to upvote Ruan answer ;) –  Dec 04 '15 at 15:16

0 Answers0