Java , add half a million objects to ArrayList from sql Query

Question

I have a query with a resultset of half a million records, with each record I'm creating an object and trying to add it into an ArrayList.

How can I optimize this operation to avoid memory issues as I'm getting out of heap space error.

This is a fragment o code :

while (rs.next()) {
            lista.add(sd.loadSabanaDatos_ResumenLlamadaIntervalo(rs));
}  


    public SabanaDatos loadSabanaDatos_ResumenLlamadaIntervalo(ResultSet rs)
    {
    SabanaDatos sabanaDatos = new SabanaDatos();
    try {

        sabanaDatos.setId(rs.getInt("id"));
        sabanaDatos.setHora(rs.getString("hora"));
        sabanaDatos.setDuracion(rs.getInt("duracion"));     
        sabanaDatos.setNavegautenticado(rs.getInt("navegautenticado"));
        sabanaDatos.setIndicadorasesor(rs.getInt("indicadorasesor"));
        sabanaDatos.setLlamadaexitosa(rs.getInt("llamadaexitosa"));
        sabanaDatos.setLlamadanoexitosa(rs.getInt("llamadanoexitosa"));
        sabanaDatos.setTipocliente(rs.getString("tipocliente"));

    } catch (SQLException e) {
        logger.info("dip.sabana.SabanaDatos SQLException : "+ e);
        e.printStackTrace();
    }

    return sabanaDatos;
}

NOTE: The reason of using list is that this is a critic system, and I just can make a call every 2 hours to the bd. I don't have permission to do more calls to the bd in short times, but I need to show data every 10 minutes. Example : first query 10 rows, I show 1 rows each minute after the sql query.

I dont't have permission to create local database, write file or other ... Just acces to memory.

Why do you need a list? What do you do with it afterwards? Try to do the further processing directly in the result set iteration. — daniu
– daniu, Commented Jan 10, 2018 at 13:54
You didn't show us enough code for us to be able to optimise anything. Why do you need (or think you need) all these objects in an ArrayList, at the same time? Adding that many objects to an ArrayList almost definitely undermines the purpose of using a database. — Bernhard Barker
– Bernhard Barker, Commented Jan 10, 2018 at 13:54
If the issue is you can only query the database once every two hours, but would like to query it whenever, the solution may be to make your own database locally and mirror the real one — phflack
– phflack, Commented Jan 10, 2018 at 14:30
@Adelin Yea that makes more sense ... if size is predicatable capacity can be specefied beforehand .. — Nithin
– Nithin, Commented Jan 10, 2018 at 15:56

Ashish Shetkar · Accepted Answer · 2018-01-10 16:25:38Z

4

First Of All - It is not a good practice to read half million objects

You can think of breaking down the number of records to be read into small chunks

As a solution to this you can think of following options

1 - use of CachedRowSetImpl - it is same resultSet , it is a bad practice to keep resultSet open (as it is a Database connection property) If you use ArrayList - then you are again performing operations and utilizing the memory

For more info on cachedRowSet you can go to

https://docs.oracle.com/javase/tutorial/jdbc/basics/cachedrowset.html

2 - you can think of using an In-Memory Database, such as HSQLDB or H2. They are very lightweight and fast, provide the JDBC interface you can run the SQL queries as well

For HSQLDB implementation you can check

https://www.tutorialspoint.com/hsqldb/

answered Jan 10, 2018 at 16:25

Ashish Shetkar

1,4773 gold badges23 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ivalberto Over a year ago

thanks, i think that CachedRowSetImpl can be a option, question CachedRowSetImpl is include into java 6 ?

Ashish Shetkar Over a year ago

yes it is - you can find it here docs.oracle.com/javase/6/docs/api/javax/sql/rowset/…

Ashish Shetkar Over a year ago

Hi @ivan , did you try it , did it work ..? And if it worked - how is the performance ...?

Ivalberto Over a year ago

, yes , i tried. It's works, But i make a few changes more memory, in code use column number for loop.

Joop Eggen · Accepted Answer · 2018-01-10 14:58:32Z

1

It might help to have Strings interned, have for two occurrences of the same string just one single object.

public class StringCache {
    private Map<String, String> identityMap = new Map<>();

    public String cached(String s) {
         if (s == null) {
             return null;
         }
         String t = identityMap.get(s);
         if (t == null) {
             t = s;
             identityMap.put(t, t);
         }
         return t;
    }
}

StringCache horaMap = new StringCache();
StringCache tipoclienteMap = new StringCache();

    sabanaDatos.setHora(horaMap.cached(rs.getString("hora")));
    sabanaDatos.setTipocliente(tipoclienteMap .cached(rs.getString("tipocliente")));

Increasing memory is already said.

A speed-up is possible by using column numbers; if needed gotten from the column name once before the loop (rs.getMetaData()).

answered Jan 10, 2018 at 14:58

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

2 Comments

Ivalberto Over a year ago

With this am I not incrementing the numbers of objects to three * n rows?

Joop Eggen Over a year ago

Depending on the repetition of the data; do SELECT DISTINCT(hora) or such to count. Ideally 100+n+10 objects or such. For long (non-repetitive texts) you could consider a lookup later,

Jon Ander · Accepted Answer · 2018-01-10 13:59:18Z

0

Option1:
If you need all the items in the list at the same time you need to increase the heap space of the JVM, adding the argument -Xmx2G for example when you launch the app (java -Xmx2G -jar yourApp.jar).

Option2:
Divide the sql in more than one call

answered Jan 10, 2018 at 13:59

Jon Ander

7921 gold badge16 silver badges25 bronze badges

2 Comments

YyYo Over a year ago

dividing the sql won't help here. he received heap exception because he tries to save half a million objects in list

Jon Ander Over a year ago

the idea of dividing in more than one sql was to obtain less results per select, treat them (example save in other table, comunicate with other app) and continue with the next select. But maybe is not possible in the business logic.

DodgyCodeException · Accepted Answer · 2018-01-10 16:37:36Z

Some of your options:

Use a local database, such as SQLite. That's a very lightweight database management system which is easy to install – you don't need any special privileges to do so – its data is held in a single file in a directory of your choice (such as the directory that holds your Java application) and can be used as an alternative to a large Java data structure such as a List.
If you really must use an ArrayList, make sure you take up as little space as possible. Try the following:

a. If you know the approximate number of rows, then construct your ArrayList with an appropriate initialCapacity to avoid reallocations. Estimate the maximum number of rows your database will grow to, and add another few hundred to your initialCapacity just in case.

b. Make sure your SabanaDatos objects are as small as they can be. For example, make sure the id field is an int and not an Integer. If the hora field is just a time of day, it can be more efficiently held in a short than a String. Similarly for other fields, e.g. duracion - perhaps it can even fit into a byte, if its range allows it to? If you have several flag/Boolean fields, they can be packed into a single byte or short as bits. If you have String fields that have a lot of repetitions, you can intern them as per Joop's suggestion.

c. If you still get out-of-memory errors, increase your heap space using the JVM flags -Xms and -Xmx.

Collectives™ on Stack Overflow

Java , add half a million objects to ArrayList from sql Query

4 Answers 4

4 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related