Retrieve large ResultSet - Real's Java How-to

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.

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);
        Statement stmt = conn.createStatement();
        ExecutionTimer t = new ExecutionTimer(); // see this HowTo
        String sql = "SELECT * FROM PROD.DOCUMENTS";
        t.start();
        Statement stmt = conn.createStatement();
        stmt.setFetchSize(200);
        ResultSet rset = stmt.executeQuery(sql);
        FileWriter fw = new FileWriter("d:/temp/o.out");
        while (rset2.next()) {
            fw.write(rset2.getString(1) + " " + rset2.getString(2) + "\n");
        }
        t.end();
        System.out.println("Timer : " + t.duration());
        stmt.close();
        rset.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  

 
*/