Monthly Archives: October 2015

Streaming/Scrollable ResultSet: for when you want results quickly and without taking up too much memory

I’m sure you’re already familiar with how to write a SELECT statement and execute it in java.

Connection conn = ...
Statement stmt = conn.createStatement();
Resultset rs = stmt.executeQuery("...");

But once in awhile, you might run into a very, very, VERY large table. By default, your mysql driver will read and buffer all the rows and then return it to you.

There are two problems you will encounter

  1. The reading takes a long time, and your stmt.executeQuery() method will hang for awhile before getting results. Other things could time-out during that time.
  2. The buffering of all the rows in-memory will consume a lot of heap and you may not have enough

So what if you want to get results quickly. ie. as soon as the first row is fetched, return it so you can process it.
Also, suppose you don’t want to hold all billion rows in memory. You just want to read one row, process it, and trash it.

Then you should create what’s called a streaming or scrollable result set like so

    String selectSQL =
        "SELECT * FROM verylargetable";
    ResultSet selectRs = null;
    try (
        Connection conn = BoneCPSQLUtil.getNewConnection();
        Statement  stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                               java.sql.ResultSet.CONCUR_READ_ONLY);

       ) {
      stmt.setFetchSize(Integer.MIN_VALUE);
      selectRs = stmt.executeQuery(selectSQL);

      while(selectRs.next()) {
        ...
      }
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      try {
        selectRs.close();
      } catch(Exception e2) { }

    }

You’ll find that results return right away. (Note: the overall throughput is lower because it’s faster to read everything first and give it to you, but the response time is faster here because you get each row right away).

You’ll also find that you don’t need much memory because as soon as you’re done with the row, it throws it away, and then you can fetch the next row.

Advertisements
Tagged ,