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

cjavanetoracle

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.

Best Answer

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.