JDBC優化之Batch、Fetch

壹頁書發表於2013-12-11
JDBC優化BAT
Batch和Fetch兩個特性非常重要。
Batch相當於JDBC的寫緩衝，Fetch相當於讀緩衝。

  1. import java.sql.Connection;
  2. import java.sql.DriverManager;
  3. import java.sql.PreparedStatement;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.util.ArrayList;
  7. import java.util.List;

  9. public class Test {
  10.     private static int _1W = 10000;
  11.     private static List<String> list = new ArrayList<String>(100 * _1W);
  12.     static {
  13.         for (int i = 0; i < 10 * _1W; i++) {
  14.             list.add(String.valueOf(i));
  15.         }
  16.     }

  18.     public static void main(String[] args) throws ClassNotFoundException, SQLException {
  19.         long start = System.currentTimeMillis();
  20.         fetchRead();
  21.         long end = System.currentTimeMillis();
  22.         System.out.println((end - start) + "ms");
  23.     }

  25.     public static void batchWrite() throws SQLException, ClassNotFoundException {
  26.         // 1108ms
  27.         Class.forName("oracle.jdbc.OracleDriver");
  28.         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
  29.         connection.setAutoCommit(false);
  30.         PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
  31.         for (int i = 0; i < list.size(); i++) {
  32.             cmd.setString(1, list.get(i));
  33.             cmd.addBatch();
  34.             if (i % _1W == 0) {
  35.                 cmd.executeBatch();
  36.             }
  37.         }
  38.         cmd.executeBatch();
  39.         connection.commit();
  40.     }

  42.     public static void jdbcWrite() throws ClassNotFoundException, SQLException {
  43.         // 28189ms
  44.         Class.forName("oracle.jdbc.OracleDriver");
  45.         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
  46.         connection.setAutoCommit(false);
  47.         PreparedStatement cmd = connection.prepareStatement("insert into t values(?)");
  48.         for (String s : list) {
  49.             cmd.setString(1, s);
  50.             cmd.execute();
  51.         }
  52.         connection.commit();
  53.     }

  55.     public static void jdbcRead() throws ClassNotFoundException, SQLException {
  56.         // 3120ms
  57.         Class.forName("oracle.jdbc.OracleDriver");
  58.         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
  59.         connection.setAutoCommit(false);
  60.         PreparedStatement cmd = connection.prepareStatement("select * from t");
  61.         ResultSet rs = cmd.executeQuery();
  62.         int i = 0;
  63.         while (rs.next()) {
  64.             rs.getString(1);
  65.             i = i + 1;
  66.         }
  67.         System.out.println("count:" + i);
  68.     }

  70.     public static void fetchRead() throws ClassNotFoundException, SQLException {
  71.         //764ms
  72.         Class.forName("oracle.jdbc.OracleDriver");
  73.         Connection connection = DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl", "xxx", "xxx");
  74.         connection.setAutoCommit(false);
  75.         PreparedStatement cmd = connection.prepareStatement("select * from t");
  76.         cmd.setFetchSize(_1W);
  77.         ResultSet rs = cmd.executeQuery();
  78.         int i = 0;
  79.         while (rs.next()) {
  80.             rs.getString(1);
  81.             i = i + 1;
  82.         }
  83.         System.out.println("count:" + i);
  84.     }
  85. }
JDBC插入比較，相差25倍
    不帶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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章