Reading data from Oracle DB using .Net is 10 times faster than using Java

Question

I'm reading 1 mln records from Oracle DB using .Net and Java. In .Net I'm using ODP.Net, in Java ojdbc6 thin client. In .Net reading data takes about 10 seconds, and in Java it takes nearly 2 minutes. Why there is such huge difference ?

Here is a code:

.Net:

      try
        {
            DateTime dt1 = DateTime.Now;

            OracleConnection con = new OracleConnection();
            con.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myService)));User Id=myId;Password=myPass;";

            con.Open();
            string cmdQuery = "SELECT * FROM DROPME";

            OracleCommand cmd = new OracleCommand(cmdQuery);
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;

            int i = 0;
            OracleDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Object o1 = reader.GetValue(0);
                Object o2 = reader.GetValue(1);
                Object o3 = reader.GetValue(2);
                Object o4 = reader.GetValue(3);
                Object o5 = reader.GetValue(4);
                Object o6 = reader.GetValue(5);
                Object o7 = reader.GetValue(6);                    
                i++;
            }

            DateTime dt2 = DateTime.Now;

            double elapsed = (dt2 - dt1).TotalSeconds;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

Java:

    try
    {
        long t0 = System.currentTimeMillis();
        oracleDataSource = new OracleDataSource();
        oracleDataSource.setURL("jdbc:oracle:thin:myId/myPass@myHost:myPort:myService");
        Connection connection = oracleDataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT * FROM DROPME");
        ResultSet result = statement.executeQuery();
        int i = 0;
        while (result.next())
        {
            result.getObject(1);
            result.getObject(2);
            result.getObject(3);
            result.getObject(4);
            result.getObject(5);
            result.getObject(6);
            result.getObject(7);
            i++;
        }
        long t1 = System.currentTimeMillis();
        long elapsed = (t1 - t0)/1000;
        int t = 0;
    }
    catch (Exception ex)
    {
        ex.printStackTrace();
    }

EDIT: setFetchSize() did the job, thanks.

Answer 1

In Java by default, ResultSets are completely retrieved and stored in memory. This is not good for queries with large ResultSets. To use a streamed result u must use:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

I've not compared the time taken, but i guess this will be much faster.

Answer 2

In my experience, the Oracle JDBC driver is poorly configured out-of-the-box for mass transfer. By default, it only transfers 10 records across the network at a time. Therefore, if you have 1,000,000 records, the driver will incur 100,000 network accesses.

You can tell the ResultSet how many records to fetch at a time with this code:

result.setFetchSize(1000);

Feel free to experiment with different sizes. It has dramatically decreased processing time (from minutes to seconds) in at least one application I have worked on.