JDBC Handling Huge ResultSet

I need to get all users stored in Oracle and restore them to orientdb. But using jdbcTemplate.query(sql, rowmapper) to iterate the result set which contains 520,000 records is too slow. I wander how to speed up the iterating. I find one article which resolved my problem.

In this article lets discuss about querying huge database tables using JDBC api.

Consider a scenario where we have a huge database table with (say 1 million records), and the requirement is to display those records in your application UI.

Generally we will use the JDBC api in java to query the database table, iterate the ResultSet object and display the results in UI.

But for querying DB tables with huge set of records it will take more time to iterate the records from DB. This is because the default fetch size for a result set is usually 10. It will be fine if the number of records in the DB is less (say 500). For more records it will take more time to retrieve.

One simple solution is to set the fetchSize attribute in ResultSet to an appropriate value.

For example to query 1 million records, we can set the fetchSize as 100.

You can easily observe the difference by executing the following program in two ways with setting fetch Size and without setting the fetchSize in ResultSet.

code:

package in.techdive.java.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class HugeResultSet
{

        public HugeResultSet()
        {
        }

        public static void main(String[] args) throws Exception
        {
                String dbUrl = "jdbc:oracle:thin:@<host_name>:<port_number>:<DB_Name>";
                Connection con = null;
                ResultSet rs = null;
                PreparedStatement psIns = null;
                PreparedStatement psSel = null;

                try
                {
                        Class.forName("<Driver_Name>");

                        con = DriverManager.getConnection(dbUrl, "<userName>", "<passWord>");

                        psIns = con
                        .prepareStatement("INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,AGE) VALUES (?,?,?)");

                        psSel = con.prepareStatement("SELECT * FROM EMPLOYEE ");

                        for (int i = 0; i < 10000; i++)
                        {
                                psIns.setInt(1, i + 100);
                                psIns.setString(2, "Test" + (i + 1));
                                psIns.setInt(3, (int) (Math.random() * 80));

                                psIns.executeUpdate();
                        }

                        rs = psSel.executeQuery();

                        System.out.println("Case 1: Without Setting Fetch Size ");

                        long t1 = System.currentTimeMillis();
                        System.out.println(System.currentTimeMillis());

                        while (rs.next())
                        {
                                // System.out.println(rs.getString(2));
                        }

                        System.out.println("Time to iterate ResultSet -> " + (System.currentTimeMillis() - t1));

                        int fetchSize = 100;

                        System.out.println("Case 2: With Setting Fetch Size value ->" + fetchSize);

                        rs = psSel.executeQuery();
                        // rs.setFetchSize(100);

                        t1 = System.currentTimeMillis();

                        System.out.println(System.currentTimeMillis());

                        rs.setFetchSize(fetchSize);

                        while (rs.next())
                        {
                                // System.out.println(rs.getString(2));
                        }

                        System.out.println("Time to iterate ResultSet -> " + (System.currentTimeMillis() - t1));

                }
                catch (Exception e)
                {
                        e.printStackTrace();
                }
                finally
                {
                        if (rs != null)
                        {
                                rs.close();
                        }

                        if (con != null)
                        {
                                con.close();
                        }

                        if (con != null)
                        {
                                con.close();
                        }

                        if (psIns != null)
                        {
                                psIns.close();
                        }

                        if (psSel != null)
                        {
                                psSel.close();
                        }
                }
        }
}

Output:

Case 1: Without Setting Fetch Size 
1280653337037
Time to iterate ResultSet -> 1283
Case 2: With Setting Fetch Size value ->100
1280653338321
Time to iterate ResultSet -> 143

Its clear that when the fetch size is more, records are retrieved more quickly.
At the same time setting the fetchSize to a larger value will increase the memory and at sometimes cause out of memory errors. So, while setting fetch Size test your code before using it in the production environment.

I find out that it's a little different with my code, I'm using the springframework JdbcTemplate, while the author uses preparedStatement. JdbcTemplate also has a founction setFetchsize() which is the same with the one of preparedStatement. So I set fetchsize to 1000, it really becomes faster than before.