1

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.

Ivan Fontalvo
  • 433
  • 4
  • 21
  • For better memory mangement use **LinkedList** – Nithin Jan 10 '18 at 13:54
  • What are you planning to do with this list? – OldCurmudgeon Jan 10 '18 at 13:54
  • 4
    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 Jan 10 '18 at 13:54
  • 4
    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 Jan 10 '18 at 13:54
  • To receive right answer to your question firstly we should have answer for: "What are you planing to do with this list after fetching ResultSet?" – vssk Jan 10 '18 at 13:57
  • While this doesn't optimise your code at all, you can [Increase heap size in Java](https://stackoverflow.com/q/1565388) – Bernhard Barker Jan 10 '18 at 14:06
  • Edited the question with the reason of the using of list. – Ivan Fontalvo Jan 10 '18 at 14:10
  • 1
    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 Jan 10 '18 at 14:30
  • @Nithin really ? Why is that can you explain ? – Adelin Jan 10 '18 at 14:54
  • @Adelin For data structure other than Linked list, after adding elements upto a certain size, a new collection object with higher capacity will be created and all the objects will be transferred (O(n)). For small number of elements this wont be a problem. But for millions of objects this might become a problem. In case of linked list, while adding object the list , the list grows – Nithin Jan 10 '18 at 15:31
  • @Nithin I know, but not if you specify the capacity beforehand ;) – Adelin Jan 10 '18 at 15:46
  • 1
    @Adelin Yea that makes more sense ... if size is predicatable capacity can be specefied beforehand .. – Nithin Jan 10 '18 at 15:56

4 Answers4

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/

Ashish Shetkar
  • 1,414
  • 2
  • 18
  • 35
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()).

Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • With this am I not incrementing the numbers of objects to three * n rows? – Ivan Fontalvo Jan 10 '18 at 15:15
  • 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, – Joop Eggen Jan 10 '18 at 15:17
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

Jon Ander
  • 740
  • 1
  • 14
  • 23
  • dividing the sql won't help here. he received heap exception because he tries to save **half a million** objects in list – YyYo Jan 10 '18 at 14:15
  • 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. – Jon Ander Jan 10 '18 at 14:47
0

Some of your options:

  1. 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.

  2. 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.

DodgyCodeException
  • 5,963
  • 3
  • 21
  • 42