CODEWITHSUNDEEP
javapostgresql
user4969615
user4969615

Reputation:

Working with a large Resultset

I'm trying to write a java function that can work with large result sets.
The table has 1.2 billion rows which is 189 Gb of data.
Currently I query all the data and extract the information which I store in their respective objects.(using a million row sample db)

TreeMap <Long, Vessel> vessels = new TreeMap<Long, Vessel>(); //list for all vessels
try{
    Statement stmt = conn.createStatement(); 
    ResultSet rs = stmt.executeQuery("SELECT mmsi, report_timestamp, position_geom, ST_X(position_geom) AS Long, "
            + "ST_Y(position_geom) AS Lat FROM reports2 WHERE position_geom IS NOT NULL ORDER by report_timestamp ASC"); 

    while(rs.next()){

        long mmsi = rs.getLong("mmsi"); 
        java.util.Date time = rs.getTime("report_timestamp"); 
        double longitude = rs.getDouble("Long");
        double latitude = rs.getDouble("Lat");
        Coordinate coordinate = new Coordinate(longitude, latitude, time);
        Vessel vessel = new Vessel(mmsi); 

        if(!vessels.containsKey(mmsi)) { //if vessel is not present in vessels
            vessel.addCoor(coordinate);
            vessels.put(mmsi, vessel);
        }
        else{ //if vessel is already in vessels 
            vessels.get(mmsi).addCoor(coordinate);
        }
    }
}catch(Exception e){
    JOptionPane.showMessageDialog(null, e);
}

With 189 Gb of data, my computer's memory won't be able to hold the information
I've never touched a table with a billion+ rows and some of my methods involve having all the tables attributes

Upvotes: 2

Views: 2929

Answers (2)

guy_sensei
guy_sensei

Reputation: 523

If memory is an issue with the ResultSet you can set the fetch size, though you'll need to clear objects during fetch to ensure you don't run out of memory.
With Postgres you need to turn off Auto commit or fetch size will not occur. 

connection.setAutoCommit(false);
            Statement stmt = connection.createStatement(); 
            stmt.setFetchSize(fetchsize);

You can read more about buffering the Result set at https://jdbc.postgresql.org/documentation/94/query.html#query-with-cursor

Upvotes: 1

Giovanni
Giovanni

Reputation: 4015

From your code it seems that you are builing a java object that collects alla the coordinates with the same mmsi field. You did not provide information about this object (mmsi and it list of coordinates) usage. Given this information you can query the data sorting by mmsi and then timestamp (you order by clause is only by timestamp now), when in the resultset you find a different value of mmsi you collected all the data about than specific mmsi so you can use it wihout reading other data. I don't think you really need to get all the data in memory; you can rewrite the query in order to get only a fixed (a sliding window) number of Vessel objects; you must page the data (i.e. retrieve a block of 10 vessels starting from vessel at position x)

In order to provide a more detailed response you have to explain what you are doing with Vessels.

Upvotes: 1

Related Questions