CachedRowSet slower than ResultSet?

ben_muc picture ben_muc · Oct 6, 2011 · Viewed 7.3k times · Source

In my java code, I access an oracle database table with an select statement. I receive a lot of rows (about 50.000 rows), so the rs.next() needs some time to process all of the rows.

using ResultSet, the processing of all rows (rs.next) takes about 30 secs

My goal is to speed up this process, so I changed the code and now using a CachedRowSet:

using CachedRowSet, the processing of all rows takes about 35 secs

I don't understand why the CachedRowSet is slower than the normal ResultSet, because the CachedRowSet retrieves all data at once, while the ResultSet retrieves the data every time the rs.next is called.

Here is a part of the code:

try {
    stmt = masterCon.prepareStatement(sql);
    rs = stmt.executeQuery();

    CachedRowSet crset = new CachedRowSetImpl();
    crset.populate(rs);

    while (rs.next()) {
        int countStar = iterRs.getInt("COUNT");
        ...
    }
} finally {
    //cleanup
}

Answer

MasterCassim picture MasterCassim · Oct 6, 2011

CachedRowSet caches the results in memory i.e. that you don't need the connection anymore. Therefore it it "slower" in the first place.

A CachedRowSet object is a container for rows of data that caches its rows in memory, which makes it possible to operate without always being connected to its data source.

-> http://download.oracle.com/javase/1,5.0/docs/api/javax/sql/rowset/CachedRowSet.html