Java statement.setFetchSize() works well when ResultSet size == Fetch size

Question

I have a problem with retrieving data from Oracle database. I use ojdbc8 and my code is:

Statement stmt = conn.createStatement();
stmt.setFetchSize(20000);
ResultSet rs = stmt.execute(sql);

while(rs.next()) {
    for(int i = 0; i < columnCounter; i++) {
        logger.info(rs.getString(i) + " ");
    }
}

What I don't understand here is that when my query returns let say 53000 rows all together then in while loop first 40000 rows are printed in console very quickly but then there is a huge 20-25 seconds break, nothing happens and after that the rest rows are printed. It is always like that. If my query returns 81000 rows then 80000 rows is printed very quickly then long brake and then missing 1000 rows.

So I don't know why but it looks like when in ResultSet I have exactly 20000 rows which is a fetch size then it goes well, but if the ResultSet has less than number set in FetchSize then it slow downs. Can anyone explain what is going on here and how to fix it to get rid of this huge gap/brake??

Try setting the stmt.setFetchSize(1000); and conn.setAutoCommitMode(false); — Sahil Manchanda, Jan 20 '18 at 11:36
@SahilManchanda I tried different configurations like 1000, 10000, 20000 and some others. It works if result set has respectively exactly 1000, 10000, 20000 items, If RS has less than that then I have a huge brake and then the rest is loaded/printed — EdXX, Jan 20 '18 at 11:48
@SahilManchanda *I have a problem with retreving data from Oracle data base. I use ojdbc8* — JB Nizet, Jan 20 '18 at 12:13
does it help https://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv ? — Ravi, Jan 20 '18 at 16:52
@Ravi No, not really. My driver works and gives the data in chunks but there is a problem with the last chunk where the number of data is lower than set fetch size — EdXX, Jan 20 '18 at 20:13

Java statement.setFetchSize() works well when ResultSet size == Fetch size

0 Answers0