JDBC ResultSet method next() is taking 30 seconds to fetch 12000 rows

Question

I'm fetching data using CallableStatement with stored proc, ResultSet has 12000 rows and it is taking 30 seconds to process. I'd tried setting fetch size (rs.setFetchSize(500) but still, it is taking the same time to fetch whole data (30 sec).

callableStatement = conn.prepareCall(myProc);
callableStatement.setString("myParam", xyz);
callableStatement.registeroutputParameter("REVTAL", OracleTypes.CURSOR);
callableStatement .setFetchSize(1000); // I have tried with different values
rs = (ResultSet) callableStatement.getObject("REVTAL");

while(rs.next()) {`
    myMethodToSetResultIntoList();
}

Is there any possible way other than fetch Size to optimize it?

I have tried this:

while(rs.next()) {`
        logger(); // do nothing
    }

It processed for 12000 rows in approx 3 seconds, so time is getting consumed in fetching the data from resultSet.

Also, It is confirmed that setfetchSize() is working properly as without setting fetch Size it is showing "fetchedRowCount" as 10 and with setFetchSize() as x, it is showing "fetchRowCount" as x on debug mode.

Actual while loop is like this:

while(rs.next()) {
    Myclass mc = new Myclass();
    mc.setA(rs.getString("parameterA");
    mc.setB(String.valueOf(rs.getLong("parameterB");
    //4 more string parameters same
    mc.setG(myMethodToConvertDate(rs.getDate("Date");
    myList.add(mc);
}

So it is taking approx 25 seconds to retrieve the data from resultSet and adding that to myList. There must be a solution for this.

Thanks in advance

I think you have only one returned value, that is a `CURSOR`. So the `setFetchSize()` will not have any effect, as it loads only one row... Try something like `SELECT * FROM "myProc"(?)` — Usagi Miyamoto, Aug 18 '17 at 14:14
Possible duplicate of [Iterating a ResultSet using the JDBC for Oracle takes a lot of time about 16s?](https://stackoverflow.com/questions/17744090/iterating-a-resultset-using-the-jdbc-for-oracle-takes-a-lot-of-time-about-16s) — phaze0, Aug 18 '17 at 14:19
I think setFetchSize() is working as expected, I have tried debugging my code and in rowCount it is actually showing the number which I set in setFetchSize(). — Farhaz Malik, Aug 18 '17 at 14:22
I have tried solution provided on mentioned thread, the solution provided was setFetchSize(). — Farhaz Malik, Aug 18 '17 at 14:25
What is the type of parameterA? Could it be a CLOB? If so have you tried to return the data as a Varchar instead? — Jean de Lavarene, Aug 22 '17 at 10:38

score 0 · Answer 1 · answered Aug 25 '17 at 18:20

A little clarification with regard to fetchSize. In your code there is a CallableStatement with an out parameter and a CURSOR. SetFetchSize has no impact on the CallableStatement as it only returns one set of results, the out parameters. In this case there is only one out parameter, the CURSOR. The getCursor call returns a ResultSet.

Best I can recall there is nothing in the JDBC spec that says what the fetchSize of the ResultSet created from a CURSOR should be. (My memory is fallible.) The Oracle Database JDBC drivers set the fetchSize of the CURSOR ResultSet to be the same as the Statement that created it.

So in this case setting the fetchSize of the CallableStatement does nothing to the result retrieved by the CallableStatement but it does set the fetchSize of the ResultSet created from the CURSOR. So the comment by Usagi Miyamoto is correct at least with respect to the CallableStatement but your comment that you are seeing the expected behavior from setting fetchSize is also correct.

Iterating over the CURSOR ResultSet fetches all the data from the database. You state that this takes about 3 seconds. When you process the rows it takes about 20 seconds. Setting fetchSize does not speed up (or slow) down row processing, only database fetch. So within limits setting fetchSize will not speed things up. (Those limits are that the fetchSize has to be big enough to minimize the number of database round trips and small enough to keep the memory footprint reasonable. 100 is usually a good number. Rarely is anything much larger significantly better.)

But with fetchSize of 1000 your code is fetching all the rows in 3 seconds. 100 might let your app run faster overall as it uses less memory, but that is secondary to your question. Your real question is why does it take ~17 seconds to process 12,000 rows? Not fetch them, but process them. That is a function of the number and types of the columns and how they are processed. You have not provided any information in that regard.

JDBC ResultSet method next() is taking 30 seconds to fetch 12000 rows

1 Answers1