java.lang.OutOfMemory error when fetching records from Database

Question

Hi All,

When I try to fetch around 20,000 records and return to ArrayList then it throws java heap space error.

   JdbcTemplate select = new JdbcTemplate(dataSource);
            String SQL_SELECT_XML_IRP_ADDRESS = " SELECT * FROM "+ SCHEMA +".XML_ADDRESS "+
                                                    " WHERE FILE_NAME = ? ";
            Object[] parameters=new Object[] {xmlFileName};
            return (ArrayList<XmlAddressDto> ) select.query(SQL_SELECT_XML_ADDRESS,
                    parameters,new XmAddressMapExt());

Our database is Oracle and using oracle thin driver.
Is there any solution for this ? How should i process this effectively ?

Answer 1

An answer is difficult without knowing the details of your system, but there are a few options:

• Increase memory for the JVM (see many other questions on SO)
• avoid processing all records at once; try grabbing a subset of your query, process that, then repeat, rather than slurping in everything at once
• try to reduce the amount of data you read: Do you really need to do a SELECT *?

Answer 2

Do you need to hold all fields of all 20,000 records at once?

Presumably you need to process them and produce something off of this data. Then build your algorithm such that it reads N records at at time (let's say 100), processes them, and then moves on to the next bunch.

You may also want to take a look at Spring's SqlQuery class which may help you in performing a computation over the result set returned by an SQL query.

Answer 3

Do you really need all 20000 result to be hold in memory?

If you just want to process all 20000 results, you should use a RowCallbackHandler doing processing for each row and setFetchSize() to avoid a full select to memory.

Answer 4

The core issue here is that you probably do not have enough memory to store 20,000 copies of the XmlAddressDto object in memory. A couple options are:

1. Add more memory (or a bigger heap, use -Xmx when starting the JVM)
2. Work with a subset in memory - either lazily initialize a collection (e.g. cache only the last 200, or so entries, hit through the db for others). You could even wrap this collection in the List interface, which might accomplish what you're trying to do - though you'd have to be careful about access, or things like iterating through it's members
3. Figure how to make the XML object smaller - perhaps store a binary or String representation if the DTO is a full tree object, and unpack it if you need to use it - especially the case if you're just going to pass the full XML to some other code to work with.