Interview Questions

JDBC Handling Huge ResultSet

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.

/**
 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS''
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 * TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS
 * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */

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();
                        }
                }
        }
}

Have a look at the sample output.

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.

For Further Study
Oracle In Clause Limitation
Spring JDBC Configuration
Spring JDBC
Spring AOP