PreparedStament 批处理性能调优

     PreparedStament是大家在做单个数据库查询和更新中经常用到的一个API，也经常用来做一些批处理的插入操作，但对这个接口是如何运行和性能怎样才能达到最优方面了解较少。下面我们用例子和数据来说明这两个问题。

 

     首先我们需要准备好测试环境，我这里用的是数据MySql 5.0，DB2/AIX64 9.1.7，其中MySql 用来查询数据，查询出来的数据插入到DB2，实现方式JDBC。MySql 5.0中有三张表task，result,manual，DB2中只有一张表batch。查询出来的数据量6436条。

 

    数据库库连接，没有用数据源方式，查询数据代码省掉，主体的插入代码如下：

数据库连接代码

public class DbManager {
 private DbConfig dbcfg= null;
 
 public DbManager(DbConfig dbcfg){
  this.dbcfg = dbcfg;
 }
 
 public Connection getConnection(){
  try{
  Class.forName(dbcfg.getDriverClassName());
  Connection conn = DriverManager.getConnection(dbcfg.getUrl(), dbcfg.getUsername(), dbcfg.getPassword());
  return conn;
  }catch(Exception e){
   System.out.println("获取数据库联接失败");
   e.printStackTrace();
   return null;
  }
 }

}

插入batch表的Batch 类，24个字段

public class Batch implements java.io.Serializable {

 // Fields

 private Integer idx;
 private Integer taskid;
 private Integer channel;
 private Integer idtype;
 private String accno;
 private String amount;
 private String vchno;
 private String vchtype;
 private String vchdate;
 private String imageid;
 private Short imagetype;
 private Integer dpi;
 private Float calx;
 private Float caly;
 private Short taskstatus;
 private Short sysidres;
 private String sysstpres;
 private String manstpres;
 private Integer manidres;
 private Integer result;
 private String reqno;
 private String exchno;
 private String clerk;
 private Date optime;
 private Date batchdate;

}

 

public class BatchManage extends DbManager implements AddBatch{
 public BatchManage(DbConfig cfg){
  super(cfg);
 }
 
 public int insertBatch(List<Batch> data,int p) throws Exception{
  String sql = "insert into Batch(taskid,idtype,accno,amount,vchno,vchdate," +
    "imageid,imagetype,dpi,calx,caly,taskstatus,sysidres,sysstpres," +
    "manstpres,manidres,clerk,optime,batchdate,result,channel,reqno,exchno,vchtype) " +
    "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  Connection conn = null;
  PreparedStatement ps = null;
  try{
   conn = this.getConnection();
   conn.setAutoCommit(false);
   ps = conn.prepareStatement(sql);
   for(int i=0;i<data.size();i++){
    Batch batch = data.get(i);
    if(batch.getTaskid()==null)
     ps.setNull(1,Types.INTEGER);
    else
     ps.setInt(1,batch.getTaskid());
    if(batch.getIdtype()==null)
     ps.setNull(2,Types.INTEGER);
    else
     ps.setInt(2,batch.getIdtype());
    if(batch.getAccno()==null)
     ps.setNull(3,Types.VARCHAR);
    else
     ps.setString(3,batch.getAccno());
    if(batch.getAmount()==null)
     ps.setNull(4,Types.INTEGER);
    else
     ps.setString(4,batch.getAmount());
    if(batch.getVchno()==null)
     ps.setNull(5,Types.VARCHAR);
    else
     ps.setString(5,batch.getVchno());
    if(batch.getVchdate()==null)
     ps.setNull(6,Types.VARCHAR);
    else
     ps.setString(6,batch.getVchdate());
    if(batch.getImageid()==null)
     ps.setNull(7,Types.VARCHAR);
    else
     ps.setString(7,batch.getImageid());
    if(batch.getImagetype()==null)
     ps.setNull(8,Types.SMALLINT);
    else
     ps.setShort(8,batch.getImagetype());
    if(batch.getDpi()==null)
     ps.setNull(9,Types.INTEGER);
    else
     ps.setInt(9,batch.getDpi());
    if(batch.getCalx()==null)
     ps.setNull(10,Types.FLOAT);
    else
     ps.setFloat(10,batch.getCalx());
    if(batch.getCaly()==null)
     ps.setNull(10,Types.FLOAT);
    else
     ps.setFloat(10,batch.getCaly());
    if(batch.getTaskstatus()==null)
     ps.setNull(11,Types.SMALLINT);
    else
     ps.setShort(11,batch.getTaskstatus());
    if(batch.getSysidres()==null)
     ps.setNull(12,Types.SMALLINT);
    else
     ps.setShort(12,batch.getSysidres());
    if(batch.getSysidres()==null)
     ps.setNull(13,Types.SMALLINT);
    else
     ps.setShort(13,batch.getSysidres());
    
    if(batch.getSysstpres()==null)
     ps.setNull(14,Types.VARCHAR);
    else
     ps.setString(14,batch.getSysstpres());
    
    if(batch.getManstpres()==null)
     ps.setNull(15,Types.VARCHAR);
    else
     ps.setString(15,batch.getManstpres());
    if(batch.getManidres()==null)
     ps.setNull(16,Types.INTEGER);
    else
     ps.setInt(16,batch.getManidres());
    if(batch.getClerk()==null)
     ps.setNull(17, Types.VARCHAR);
    else
     ps.setString(17, batch.getClerk());
    if(batch.getOptime()==null)
     ps.setNull(18, Types.TIMESTAMP);
    else
     ps.setTimestamp(18, new Timestamp(batch.getOptime().getTime()));
    if(batch.getBatchdate()==null)
     ps.setNull(19, Types.TIMESTAMP);
    else
     ps.setTimestamp(19, new Timestamp(batch.getBatchdate().getTime()));
    if(batch.getResult()==null)
     ps.setNull(20,Types.INTEGER);
    else
     ps.setInt(20,batch.getResult());
    if(batch.getChannel()==null)
     ps.setNull(21,Types.INTEGER);
    else
     ps.setInt(21,batch.getChannel());
    if(batch.getReqno()==null)
     ps.setNull(22,Types.VARCHAR);
    else
     ps.setString(22,batch.getReqno());
    if(batch.getExchno()==null)
     ps.setNull(23,Types.VARCHAR);
    else
     ps.setString(23,batch.getExchno());
    if(batch.getVchtype()==null)
     ps.setNull(24,Types.VARCHAR);
    else
     ps.setString(24,batch.getVchtype());
    if(p==1){
     ps.executeUpdate();
    }else{
     ps.addBatch();
     if(i>0 && i%p==0)
      ps.executeBatch();
    }
   }
   ps.executeBatch();
   conn.commit(); 
   conn.setAutoCommit(true);  
   return data.size();
  }catch(SQLException e){
   System.out.println("插入验印运算结果数据失败");
   e.printStackTrace();
   conn.rollback();
   conn.setAutoCommit(true);
   return 0;
  }finally{
   this.closePreparedStatement(ps);
   this.closeConnection(conn);
  }
 }
}

 

我们需要在

  if(p==1){
     ps.executeUpdate();
    }else{
     ps.addBatch();
     if(i>0 && i%p==0)
      ps.executeBatch();
    }
   }
   ps.executeBatch();
   conn.commit(); 
这段代码的地方做一些变化，可将数据p置换为1,200,400,600...。

 

客户端测试代码如下：

//相当于初始化环境

Date d1 = new Date();
  task.excute(date,50);
  Date d2 = new Date();
  System.out.println("序号/t消耗时间/t批量数/t提交次数");

//测试每次执行ps.executeUpdate
  int j=0;
  Date d3 = new Date();
  task.excute(date,1);
  Date d4 = new Date();
  System.out.println(++j+"/t"+ (d4.getTime()-d3.getTime()) + "/t" +1+"/t"+6436);

//测试批量提交
  for(int i=200;i<=6600;i+=200){
    d3 = new Date();
   task.excute(date,i);
    d4 = new Date();
   System.out.println(++j+"/t"+ (d4.getTime()-d3.getTime()) + "/t" +i+"/t"+(6436/i));
  }

 

量外一种方式是最后提交，下面的测试数据有对应的。由于初始化环境需要消耗大量的时间，因此已经将它排除。这是我在测试中得到的数据，其中最后的提交批量数除第一个外，其他的都需要在提交批量数的基数上加1：

序号 消耗时间 提交批量数 提交次数
1 8875 1 6436  （每次执行ps.executeUpdate）
2 1688 200 32
3 1687 400 16
4 1594 600 10
5 1594 800 8
6 1547 1000 6
7 1656 1200 5
8 2141 1400 4
9 1734 1600 4
10 1828 1800 3
11 1938 2000 3
12 1812 2200 2
13 1766 2400 2
14 1828 2600 2
15 1828 2800 2
16 1719 3000 2
17 1750 3200 2
18 1750 3400 1
19 1734 3600 1
20 1719 3800 1
21 1734 4000 1
22 1719 4200 1
23 1750 4400 1
24 1750 4600 1
25 1703 4800 1
26 1734 5000 1
27 1797 5200 1
28 1891 5400 1
29 1859 5600 1
30 2266 5800 1
31 1766 6000 1
32 1859 6200 1
33 2156 6400 1
34 2203 6600 0 (最后执行ps.executeBatch(),中间不执行)

 

我们从上面的数据中选择出来一组性能最优的数据，他们花费的时间接近，从中任选一个都可以。

4 1594 600 10
5 1594 800 8
6 1547 1000 6

 

思考：

数据的测试结果的性能和到底和什么有关系呢？两台数据库和执行程序的机器都不相同，他们相互之间的数据通讯关系如下：

mysql server （a）<--1-->JDBC server(b)<---2-->db2 server(c)

 

1:是一次性将数据查询出来的，a-b之间的数据处理时间不会有很大变化。

2:b-c之间的通讯有整体提交，多次提交和每次提交三种选择的可能。

从测试数据判断性能顺序如下：多次提交>整体提交>每次提交。

 

两台机器在协同处理同一任务时，应该减少他们之间相互等待处理任务的时间。我们这里将PreparedStament批量处理任务分解为db2 sql预编译(conn.prepareStatement(sql))，JDBC 数据封装(ps.addBatch) ，JDBC 数据传输处理(相当于ps.excuteBatch())，db2数据持久化（conn.commit）四个步骤。如果我们能够在db2数据处理一批数据后，客户机适时地又将一批数据传输过来进行处理，就会使客户机和数据库一直处于不间断的运行状态，这就是我们所需的最佳性能。这个最佳性能就体现在选择每次提交的批量数据是多少上，即代码 if(i>0 && i%p==0)
      ps.executeBatch();
   }中的参数p上。

 

参数p受约束的条件包括网络传输速率，批量数据量的大小以及主客服务器性能，因此它不是一个固定的常量，是随环境而变化的。