Impact of oraclejdbcfetchsize on Performance

?????? When data is retrieved through JDBC, the default value is 10 data records, that is, fetchsize is 10. Increasing this number can reduce the round-trip between the client and oracle and reduce the response time, it is recommended that this number not exceed 100 on the Internet. Otherwise, the middleware memory consumption will be high (no experiments have been conducted ). Packagecom. gg. test; importjava. SQL. Conn

?????? When data is retrieved through JDBC, the default value is 10 pieces of data, that is, the fetch size is 10. Increasing this number can reduce the round-trip between the client and oracle and reduce the response time, it is recommended that this number not exceed 100 on the Internet. Otherwise, the middleware memory consumption will be high (no experiments have been conducted ). Package com. gg. test; import java. SQL. Conn

?????? When data is retrieved through JDBC, the default value is 10 pieces of data, that is, the fetch size is 10. Increasing this number can reduce the round-trip between the client and oracle and reduce the response time, it is recommended that this number not exceed 100 on the Internet. Otherwise, the middleware memory consumption will be high (no experiments have been conducted ).

Package com. gg. test; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class FetchSize {static final String driver_class = "oracle. jdbc. driver. oracleDriver "; static final String connectionURL =" jdbc: oracle: thin: @ 10.10.29.150: 1522: ordb10 "; static final String userID =" test "; static final String userPassword = "test"; public void runTest (int fetchSize) {Connection con = null; Statement stmt = null; ResultSet rset = null; long startTime = System. currentTimeMillis (); String query_string = "SELECT * FROM test"; // test has 50 thousand records. try {Class. forName (driver_class ). newInstance (); con = DriverManager. getConnection (connectionURL, userID, userPassword); stmt = con. createStatement (); stmt. setFetchSize (fetchSize); rset = stmt.exe cuteQuery (query_string); while (rset. next () {rset. getString (1); rset. getString (2); rset. getString (3);} rset. close (); stmt. close (); long endTime = System. currentTimeMillis (); System. out. println ("fetchsize is" + fetchSize + "--- time consumed:" + (endTime-startTime);} catch (SQLException e) {e. printStackTrace ();} catch (Exception e) {e. printStackTrace () ;}} public static void main (String [] args) {FetchSize fetchSize = new FetchSize (); fetchSize. runTest (10); fetchSize. runTest (50); fetchSize. runTest (100); fetchSize. runTest (200); fetchSize. runTest (500); fetchSize. runTest (1000 );}}

Result:

Fetchsize is 10 ?? ? --- Consumed time: 22140 ms
Fetchsize is 50 ??? --- Consumed time: 13780 ms
Is fetchsize 100? --- Consumed time: 5110 ms
Is fetchsize 200? --- Consumed time: 3984 ms
Is fetchsize 500? --- Consumed time: 2625 ms
Fetchsize is 1000 --- the consumed time: 2875 ms

Author: guogang83 posted on 17:24:37 Original article link

Read: 7 Comments: 0 view comments

Original article address: Impact of oracle jdbc fetchsize on Performance.