Share this page 

Retrieve large ResultSetTag(s): JDBC


JDBC provides a way to give a hint about the size of the expected ResultSet.

Statement.setFetchSize() gives a hint about the number of rows that should be fetched from the database each time new rows are needed. The goal is to reduce unnecessary network round trip.

With Oracle, the default value is 10 (i.e. the Oracle driver will retrieve ResultSets in 10-row chunks)

A JDBC Compliant driver may choose to ignore the setFetchSize(). You should do some testing first to check if the result is optimal.

See (Sun) 5.1.9 Providing Performance Hints 

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PerfSelect {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException, IOException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@//oracle.server:1529/prod";
        Connection conn = DriverManager.getConnection(url, "scott", "tiger");
        conn.setAutoCommit(false);

        ExecutionTimer t = new ExecutionTimer(); // see this HowTo
        String sql = "SELECT * FROM PROD.DOCUMENTS";
        t.start();
        Statement stmt = conn.createStatement();
        stmt.setFetchSize(200);
        ResultSet rs = stmt.executeQuery(sql);
        FileWriter fw = new FileWriter("d:/temp/o.out");
        while (rs.next()) {
            fw.write(rs.getString(1) + " " + rs.getString(2) + "\n");
        }
        t.end();
        System.out.println("Timer : " + t.duration());
        stmt.close();
        rs.close();
        fw.flush();
        fw.close();
        System.out.println("Ok.");
    }
}
/*
348831 rows
without prefetch  (def == 10)
   48296 ms
   48045 ms

prefetch == 100
   14859
   14749

prefetch == 200
   13343
   13296

prefetch == 500
   13891
   13812

prefetch == 1000
   13078
   13078
*/
mail_outline
Send comment, question or suggestion to howto@rgagnon.com