Iterating a ResultSet using the JDBC for Oracle takes a lot of time about 16s?

by

I have set up a table with 4000 rows and 10 columns with 10 characters each and made a simple performance test using the following approach (RealTimeCounter is a class which measures the real time between start() and stop()):

List<String> myResult = new ArrayList<>();
ResultSet rs = s.executeQuery("SELECT * FROM Performance");

RealTimeCounter rtc = new RealTimeCounter();
rtc.start();
while(rs.next()) {
    myResult.add(rs.getString(1));
}
rtc.stop();
System.out.println(rtc);

Results:

  • Default fetch size: execution time is approx. 20 sec
  • fetch size = 100: execution time is approx 2.2 sec
  • fetch size = 500: execution time is approx 450 msec
  • fetch size = 2000: execution time is approx 120 msec
  • fetch size = 4000: execution time is approx 50 msec
  • fetch size = 4001: execution time is approx 10 msec (!!)

So, the fetch size does have a significant impact on the execution speed.

Note that, on the other hand, the fetch size has some impact on memory consumption. Interestingly enough, a quick analysis using Runtime.getRuntime().freeMemory(); before and after the above code showed that the impact is much less than I would expect, though. The numbers I got are:

  • Default fetch size: 665k
  • fetch size = 100: 665k
  • fetch size = 500: 665k
  • fetch size = 2000: 743k
  • fetch size = 4000: 821k
  • fetch size = 4001: 861k

More Related Contents:

  1. having an exception when saving oracle type object in some entity
  2. Is java.sql.Timestamp timezone specific?
  3. java.sql.SQLException: – ORA-01000: maximum open cursors exceeded
  4. Using setDate in PreparedStatement
  5. How to connect to Oracle using Service Name instead of SID
  6. Where’s my invalid character (ORA-00911)
  7. Why do I get java.lang.AbstractMethodError when trying to load a blob in the db?
  8. How to call oracle stored procedure which include user-defined type in java?
  9. java.sql.SQLException: Exhausted Resultset
  10. How to enable logging for SQL statements when using JDBC
  11. Difference between Oracle jdbc driver classes?
  12. PLSQL JDBC: How to get last row ID?
  13. Oracle JDBC intermittent Connection Issue
  14. Getting output from dbms_output.get_lines using JDBC
  15. JDBC ResultSet get columns with table alias
  16. How to write / update Oracle blob in a reliable way?
  17. Setting Network Timeout for JDBC connection
  18. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  19. Java JDBC – How to connect to Oracle using tnsnames.ora
  20. Call an Oracle function from Java
  21. Cannot use a LIKE query in a JDBC PreparedStatement?
  22. ojdbc14.jar vs. ojdbc6.jar
  23. Connection timeout for DriverManager getConnection
  24. Oracle’s RETURNING INTO usage in Java (JDBC, Prepared Statement)
  25. Connection cannot be cast to oracle.jdbc.OracleConnection
  26. Oracle JDBC : invalid username/password (ora-01017)
  27. PreparedStatement and setTimestamp in oracle jdbc
  28. The Network Adapter could not establish the connection when connecting with Oracle DB
  29. Getting Database connection in pure JPA setup
  30. What is the most recommended way to store time in PostgreSQL using Java?

Leave a Comment