Batch和Fetch两个特性非常重要。
Batch相当于JDBC的写缓冲,Fetch相当于读缓冲。
JDBC插入比较,相差25倍
不带Batch 28189ms
Batch 1108ms
JDBC读取比较,相差4倍
不带Fetch 3120ms
Fetch 764ms
如果把JDBC类比为JAVA IO的话,
不使用Fetch和Batch相当于直接使用FileInputStream和FileOutputStream
而设置了Fetch和Batch相当于使用BufferedInputStream和BufferedOutputStream
Batch相当于JDBC的写缓冲,Fetch相当于读缓冲。
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
-
- public class Test {
- private static int _1W = 10000;
- private static List<String> list = new ArrayList<String>(100 * _1W);
- static {
- for (int i = 0; i < 10 * _1W; i++) {
- list.add(String.valueOf(i));
- }
- }
-
- public static void main(String[] args) throws ClassNotFoundException, SQLException {
- long start = System.currentTimeMillis();
- fetchRead();
- long end = System.currentTimeMillis();
- System.out.println((end - start) + "ms");
- }
-
- public static void batchWrite() throws SQLException, ClassNotFoundException {
- // 1108ms
- Class.forName("oracle.jdbc.OracleDriver");
- Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
- connection.setAutoCommit(false);
- PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
- for (int i = 0; i < list.size(); i++) {
- cmd.setString(1, list.get(i));
- cmd.addBatch();
- if (i % _1W == 0) {
- cmd.executeBatch();
- }
- }
- cmd.executeBatch();
- connection.commit();
- }
-
- public static void jdbcWrite() throws ClassNotFoundException, SQLException {
- // 28189ms
- Class.forName("oracle.jdbc.OracleDriver");
- Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
- connection.setAutoCommit(false);
- PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
- for (String s : list) {
- cmd.setString(1, s);
- cmd.execute();
- }
- connection.commit();
- }
-
- public static void jdbcRead() throws ClassNotFoundException, SQLException {
- // 3120ms
- Class.forName("oracle.jdbc.OracleDriver");
- Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
- connection.setAutoCommit(false);
- PreparedStatement cmd = connection.prepareStatement("select * from t");
- ResultSet rs = cmd.executeQuery();
- int i = 0;
- while (rs.next()) {
- rs.getString(1);
- i = i + 1;
- }
- System.out.println("count:" + i);
- }
-
- public static void fetchRead() throws ClassNotFoundException, SQLException {
- //764ms
- Class.forName("oracle.jdbc.OracleDriver");
- Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
- connection.setAutoCommit(false);
- PreparedStatement cmd = connection.prepareStatement("select * from t");
- cmd.setFetchSize(_1W);
- ResultSet rs = cmd.executeQuery();
- int i = 0;
- while (rs.next()) {
- rs.getString(1);
- i = i + 1;
- }
- System.out.println("count:" + i);
- }
- }
不带Batch 28189ms
Batch 1108ms
JDBC读取比较,相差4倍
不带Fetch 3120ms
Fetch 764ms
如果把JDBC类比为JAVA IO的话,
不使用Fetch和Batch相当于直接使用FileInputStream和FileOutputStream
而设置了Fetch和Batch相当于使用BufferedInputStream和BufferedOutputStream
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1063033/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1063033/