Java Jdbc减少交互提升批量处理性能，到底该如何优化才好？

不拾掇Java有好几年了(N>3吧)，之所以写这篇文章其实是纯粹是为了给开发人员一些好的使用jdbc真正去减少交互和提升批量处理batch update性能的例子；如果你是DBA，那么工作之余你可以把这篇文章推荐给开发看一下，也许这些例子他已经知道了，倘若他不知道，那么也算一种福利了。

能考虑到在应用程序client和数据库服务器DB server间减少交互时间，批量更新处理的绝对是有助于重构和优化代码的好同志；但这种优化一定要注意方法，如果是自行去重新发明一种轮子的话，效果往往是不如人意的。

例如Tom Kytes曾在他的著作里提到这样2个例子，他去协助开发的2家企业的在研发应用的过程中，分别通过应用程序自己去在Oracle中实现了user profile和advanced queue的功能，有一定经验的朋友肯定会知道这2样功能其实Oracle Enterprise Edition企业版软件都是原生态支持的，而自己在DB中去实现它们，最终结果自然是项目的失败。

类似的有朋友在开发过程中，为了优化Oracle JDBC中的批量更新update操作，想到了这样的方式，例如要插入INSERT 15000行数据，则在JAVA层面将15000条INSERT语句拼接在一个PL/SQL block里，这15000条SQL涉及到的变量仍使用PreparedStatement.setXXX方法带入，其在JAVA层面的SQL STRING，如：

begin 
  --我是一个拼接起来的SQL匿名块 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
 insert into insertit values(?,?,?,?); 
.....................
  commit ; end;

如上15000个INSERT拼接成一个PL/SQL block，一次性PreparedStatement.execute()提交给DB，通过这样来减少Jdbc Thin Client与DB Server之间的交互。先不说别的，光在JAVA里循环控制拼接SQL的写法多少是要花点时间的。

这种写法和 JDBC里PreparedStatement.setExecuteBatch、或者PreparedStatement+addBatch()+executeBatch()的执行效率究竟如何呢？

我们在一个简单的JAVA程序里测试这三者写法的实际性能，并窥探其在DB中的表现，以下为JAVA代码(多年不写，就勿纠结代码风格)：

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apptest;

import oracle.jdbc.*;
import java.sql.*;

/**
 *
 * @author xiangbli
 */
public class Apptest {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws SQLException {
        // TODO code application logic here

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }catch(Exception e){}

        Connection cnn1=DriverManager.getConnection("jdbc:oracle:thin:@192.168.56.101:1521:cdb1", "c##maclean", "oracle");
        Statement stat1=cnn1.createStatement();
        cnn1.setAutoCommit(false);

   ResultSet rst1=stat1.executeQuery("select * from v$version");
   while(rst1.next())
   {
       System.out.println(rst1.getString(1));

   }
        long startTime = System.currentTimeMillis();
             long stopTime = System.currentTimeMillis();

   String str="begin \n  --我是一个拼接起来的SQL匿名块 \n";
   int i;
     for(i=0;i<=15000; i++)

     {

     str= str.concat(" insert into insertit values(?,?,?,?); \n");

     }  
     str=str.concat("  commit ; end; ");
    System.out.print(str);

    cnn1.createStatement().execute("alter system flush shared_pool");

    System.out.print("\n alter system flush shared_pool 已刷新共享池，避免SQL游标缓存 影响第一次测试 \n");

     PreparedStatement  pstmt = cnn1.prepareStatement(str);
     int j;
     for (j=0;j<=15000;j++)
     {

         pstmt.setInt(1+j*4, 1);
         pstmt.setInt(2+j*4, 1);
         pstmt.setInt(3+j*4, 1);
         pstmt.setInt(4+j*4, 1);

     }

  //  System.out.println (" Statement Execute Batch Value " +((OraclePreparedStatement)pstmt).getExecuteBatch());

startTime = System.currentTimeMillis();
     pstmt.execute();
 stopTime = System.currentTimeMillis();
     System.out.println("拼接15000条INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

     startTime = System.currentTimeMillis();
     pstmt.execute();
     stopTime = System.currentTimeMillis();
     System.out.println("拼接15000条INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

    cnn1.createStatement().execute("alter system flush shared_pool");

    System.out.print("\n alter system flush shared_pool 已刷新共享池，避免SQL游标缓存 影响第二次测试 \n");

        startTime = System.currentTimeMillis();

        int batch=1000;

       PreparedStatement  pstmt2 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");
      ((OraclePreparedStatement)pstmt2).setExecuteBatch(batch);
       for (int z=0;z<=15000;z++)
       {
       pstmt2.setInt(1, z);
       pstmt2.setInt(2, z);
       pstmt2.setInt(3, z);
       pstmt2.setInt(4, z);

       pstmt2.executeUpdate();

       }
       ((OraclePreparedStatement)pstmt2).sendBatch();
     cnn1.commit();

     stopTime = System.currentTimeMillis();

      System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

      startTime = System.currentTimeMillis();

       PreparedStatement  pstmt3 = cnn1.prepareStatement("insert into insertit values(?,?,?,?)");
      ((OraclePreparedStatement)pstmt3).setExecuteBatch(batch);

       for (int z=0;z<=15000;z++)
       {
       pstmt3.setInt(1, z);
       pstmt3.setInt(2, z);
       pstmt3.setInt(3, z);
       pstmt3.setInt(4, z);

       pstmt3.executeUpdate();

       }
     ((OraclePreparedStatement)pstmt3).sendBatch();

     cnn1.commit();

     stopTime = System.currentTimeMillis();

      System.out.println("batch size= "+batch+" 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

      String insert = "insert into insertit values (?,?,?,?)";

      PreparedStatement  pstmt4 = cnn1.prepareStatement(insert);

       startTime = System.currentTimeMillis();

      for (int u=0;u<=15000;u++)

      {
          pstmt4.setInt(1, u);
          pstmt4.setInt(2, u);
          pstmt4.setInt(3, u);
          pstmt4.setInt(4, u);
          pstmt4.addBatch();
      }

      pstmt4.executeBatch();
       cnn1.commit();

     stopTime = System.currentTimeMillis();

       System.out.println(" BATCH update 第一次运行的耗时 Elapsed time was " + (stopTime - startTime) + " miliseconds.");

     }

    }

拼接SQL和PreparedStatement.setExecuteBatch均执行2次，第一次没有游标缓存，第二次有游标缓存。PreparedStatement+addBatch()+executeBatch()只执行一次。

以下为JAVA程序端的测试结果：

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第一次测试
拼接15000条INSERT SQL 第一次运行的耗时 Elapsed time was 441299 miliseconds.
拼接15000条INSERT SQL 第二次运行的耗时 Elapsed time was 5938 miliseconds.

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1000 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 322 miliseconds.
batch size= 1000 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 131 miliseconds.
BATCH update 第一次运行的耗时 Elapsed time was 80 miliseconds.

以下为DB SERVER端 10046 trace的结果：

begin
  --我是一个拼接起来的SQL匿名块
 insert into insertit values(:1 ,:2 ,:3 ,:4 );
 insert into insertit values(:5 ,:6 ,:7 ,:8 );
 insert into insertit values(:9 ,:10 ,:11 ,:12 );
 insert into insertit values(:13 ,:14 ,:15 ,:16 );
 insert into insertit values(:17 ,:18 ,:19 ,:20 );
 insert into insertit values(:21 ,:22 ,:23 ,:24 );
 insert into insertit values(:25 ,:26 ,:27 ,:28 );
 insert into insertit values(:29 ,:30 ,:31 ,:32 );
 insert into insertit values(:33 ,:34 ,:35 ,:36 );
 insert into insertit values(:37 ,:38 ,:39 ,:40 );
 insert into insertit values(:41 ,:42 ,:43 ,:44 );
 insert into insertit values(:45 ,:46 ,:47 ,:48 );
 insert into insertit values(:49 ,:50 ,:51 ,:52 );
 insert into insertit values(:53 ,:54 ,:55 ,:56 );
 insert into insertit values(:57 ,:58 ,:59 ,:60 );
 insert into insertit values(:61 ,:62 ,:63 ,:64 );
 insert into insertit values(:65 ,:66 ,:67 ,:68 );
 insert into insertit values(:69 ,:70 ,:71 ,:72 );
...................................
 insert into insertit values(:59989 ,:59990 ,:59991 ,:59992 );
 insert into insertit values(:59993 ,:59994 ,:59995 ,:59996 );
 insert into insertit values(:59997 ,:59998 ,:59999 ,:60000 );
 insert into insertit values(:60001 ,:60002 ,:60003 ,:60004 );
  commit ; end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1    176.10     179.33          0         97          0           0
Execute      2    150.51     155.37          2          4          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    326.61     334.71          2        101          0           2

===>这是拼接SQL 在DB SERVER端总耗时334秒， CPU时间 326秒

insert into insertit
values
(:1 ,:2 ,:3 ,:4 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute     32      0.09       0.11          4        823       1000       30002
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       34      0.09       0.11          4        823       1000       30002

==》这是　使用PreparedStatement.setExecuteBatch的结果，　耗时0.11秒，cpu时间 0.09秒,
因为batch size是1000，所以实际是每1000次INSERT执行一次，所以总的执行次数约为30次

insert into insertit
values
 (:1 ,:2 ,:3 ,:4 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.03       0.04          1         93        475       15001
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.04          1         93        475       15001

==>这是使用addBatch()+executeBatch()， execute和parse均只为一次，即15000条数据仅解析一次 执行一次

类似于PL/SQL中bulk collect INSERT的效果

以上可以看到拼接SQL的写法不管是对比 setExecuteBatch 还是 executeBatch都要满几百倍。

拼接15000条INSERT语句到一个Pl/SQL block中的慢主要体现在：

他是一个太长的PL/SQL block，在第一次 Parse解析时Oracle 使用PL/SQL engine引擎要扫描整个block，从上面的tkprof结果可以看到光parse就消耗了179秒，即便不用setExecuteBatch 还是 executeBatch仅使用最普通的batch size=1的循环SQL也要比这个拼接SQL块。
它的执行需要在Pl/SQL引擎和SQL引擎之间不断切换，所以Execute也非常慢
它要分配60000个绑定变量，这对于PGA的压力太大了，很可能导致SWAP
由于变量和SQL过长，会引起一些莫名得小概率发生的BUG

实际上JDBC 原生态的 setExecuteBatch 、 executeBatch都是为了减少 client-server之间的交互。

setExecuteBatch 是等待可执行的SQL达到一定数目后，一次性提交给Oracle，一般推荐的Batch Size是20~50，我简单测试了不同batch size在本例中的情况：

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 4990 miliseconds.
batch size= 1 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 3778 miliseconds.
成功构建 (总时间: 9 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 10 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 829 miliseconds.
batch size= 10 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 474 miliseconds.
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 20 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 694 miliseconds.
batch size= 20 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 366 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 30 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 516 miliseconds.
batch size= 30 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 204 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 40 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 481 miliseconds.
batch size= 40 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 188 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 50 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 456 miliseconds.
batch size= 50 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 171 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 100 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 450 miliseconds.
batch size= 100 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 123 miliseconds.
成功构建 (总时间: 1 秒)

alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 200 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 402 miliseconds.
batch size= 200 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 92 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 500 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 386 miliseconds.
batch size= 500 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 95 miliseconds.
成功构建 (总时间: 1 秒)
alter system flush shared_pool 已刷新共享池，避免SQL游标缓存影响第二次测试
batch size= 1000 常规循环 15000 次 INSERT SQL 第一次运行的耗时 Elapsed time was 369 miliseconds.
batch size= 1000 常规循环 15000 次 INSERT SQL 第二次运行的耗时 Elapsed time was 87 miliseconds.
成功构建 (总时间: 1 秒)

可以看到上述例子中200~500的Execute batch size获得的性能较好。

PreparedStatement+addBatch()+executeBatch()的方式很像PL/SQL中的bulk collect处理，将变量置入batch array中，执行时将array中的数据一次插入到表中，即解析一次、执行一次，虽然对PGA会有一些少量压力，但是从上例中也能看到，获得了最快的速度。

综上所述对于Java Jdbc减少交互提升批量处理性能，最好的办法是尽可能多用PreparedStatement setExecuteBatch 和 executeBatch。

对于软件开发，特别是上层软件的开发(OS、Database、programming language除外)，千万不要想当然去自己发明一种新的”轮子”出来，必要的基础工具包括 “扳手” “螺丝” “轮胎”，他们都是现成的，也是被久经考验高效可用的，自己去重新发明轮子，难免不费时费力而不讨好。

Comments

maclean says

2013/03/09 at 19:49

补充：

JDBC Performance Tips (Doc ID 60943.1) 给出了很多有益的例子

Batching for Multiple Inserts

Note: The prefetching and batch update extensions were designed prior to the announcement of the JDBC 2.0 standard. They do not match JDBC 2.0.

Standard JDBC makes a roundtrip to the database to execute a prepared statement whenever the statement’s executeUpdate method is executed. JDBC accumulates that many execution requests for the statement before passing the requests to the database for execution.

The following example illustrates the use of this feature; it assumes you have imported the classes oracle.jdbc.driver.* and java.sql.*:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection (“jdbc:oracle:oci8:@”, “scott”,”tiger”);

PreparedStatement ps = conn.prepareStatement (“insert into dept values (?, ?, ?)”);

//Change batch size for this statement to 3
((OraclePreparedStatement)ps).setExecuteBatch (3);

ps.setInt (1, 23);
ps.setString (2, “Sales”);
ps.setString (3, “USA”);
ps.executeUpdate (); //JDBC queues this for later execution

ps.setInt (1, 24);
ps.setString (2, “Blue Sky”);
ps.setString (3, “Montana”);
ps.executeUpdate (); //JDBC queues this for later execution

ps.setInt (1, 25);
ps.setString (2, “Applications”);
ps.setString (3, “India”);
ps.executeUpdate (); //The queue size equals the batch value of 3
//JDBC sends the requests to the
// database

ps.setInt (1, 26);
ps.setString (2, “HR”);
ps.setString (3, “Mongolia”);
ps.executeUpdate (); //JDBC queues this for later execution

((OraclePreparedStatement)ps).sendBatch ();
//JDBC sends the queued request
ps.close();
conn.close ();

Prefetching Rows

Note: The prefetching and batch update extensions were designed prior to the announcement of the JDBC 2.0 standard. They do not match JDBC 2.0.

This method uses client-side buffers to replace expensive roundtrips with inexpensive local pointer manipulation for most rows returned by a query. The default row prefetch value is currently set to 10.

The following example illustrates the use of this feature; it assumes you have imported the classes oracle.jdbc.driver.* and java.sql.*:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection (“jdbc:oracle:oci8:@”,”scott”,”tiger”);

//Set the default row prefetch setting for this connection
((OracleConnection)conn).setDefaultRowPrefetch (7);

/* The following statement gets the default row prefetch value for
the connection, that is, 7.
*/
Statement stmt = conn.createStatement ();

/* Subsequent statements look the same, regardless of the row
prefetch value. Only execution time changes.
*/
ResultSet rset = stmt.executeQuery (“select ename from emp”);
System.out.println ( rset.next () );

while( rset.next () )
System.out.println ( rset.getString (1) );

//Override the default row prefetch setting for this statement
( (OracleStatement)stmt ).setRowPrefetch (2);

rset = stmt.executeQuery (“select ename from emp”);
System.out.println ( rset.next () );

while( rset.next () )
System.out.println ( rset.getString (1) );

stmt.close ();
conn.close ();

回复
familyme says

2015/06/15 at 11:37

批量插入 PreparedStatement + addBatch +executeBatch

回复

Java Jdbc减少交互提升批量处理性能，到底该如何优化才好？

Comments

Comment 取消回复