批量插入数据效率问题

Question

文本文件里面有40w行数据，通过jdbc的execbatch批量插入数据库，刚开始的20w数据数度还可以，大概没分钟插入1w条，但插入到20w后效率明显降低从4分钟1w，到6分钟1w了，都不知道为什么会这样，有没有提高效率的方法呢
while(!isNull(date)){

String doingcwhpfile = (String)session.getAttribute("doingcwhpfile");
if(isNull(doingcwhpfile)){
throw new Exception("session超时！");
}
    
s++;
sb = new StringBuffer();
   sb.append("insert into "+tableName+"(cwlsh,sfdx,sbbh,fkssq,fkzhkhhm,fkrzh,fkrmc,skzhkhhm,sheng,shi,skrzh,skrmc,je,zt,bz,yhjyrq,filename) values");
   
   sb.append(getValues(date.split("\\|"),fileName));    
     
   st.addBatch(sb.toString());
   if(s==5000){
   times++;
   star2=showTime();
  
   st.executeBatch();
   st.clearBatch();
   
   end=showTime();
   System.out.println(star2+"-while-s-"+end);
   
   s = 1;
   }
   
   if(times % 80000 == 0){
   conn.commit();
   }
   
   date = bf.readLine();
}
end=showTime();

st.executeBatch();
conn.commit();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
String sql ="delete from table1 where filename='"+fileName+"'";
conn.createStatement().execute(sql);
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
try {
conn.rollback();
String sql ="delete from table1 where filename='"+fileName+"'";
conn.createStatement().execute(sql);
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally{
try {
bf.close();
conn.setAutoCommit(true);
st.close();
conn.close();
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

}
连接则从连接池中获取 private Connection getConnection() {
/*Connection con=null;
    DbFactory dbFactory;
try {
dbFactory = (DbFactory) DbFactorys.get();
conn = dbFactory.getConnection();
} catch (Exception e) {
e.printStackTrace();
}*/

Context ctx = null;
Connection conn = null;
DataSource ds = null;
try {
ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/db");
conn = ds.getConnection();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}

Answer 1

例如：你每次循环都执行：sb = new StringBuffer()，意味着上一次循环所使用的那个对象被扔掉了，等待垃圾回收，但是随着每次循环，大数量的垃圾被扔掉等待回收，如果太大，也会直接影响到执行速度的。

Answer 2

sb.append("insert into "+tableName+"(cwlsh,sfdx,sbbh,fkssq,fkzhkhhm,fkrzh,fkrmc,skzhkhhm,sheng,shi,skrzh,skrmc,je,zt,bz,yhjyrq,filename) values");           
sb.append(getValues(date.split("\\|"),fileName));           每次循环都要用StringBuffer加一个insert into...这样去加sql语句？用参数的形式，预编译sql，每次循环设置参数，，可以减少一些消耗吧
String sql = "insert into Black_List values (?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
int k = 0; // 计数器
         //int l = 0;
for(String phone : array){
k++;
                        ps.setString(1, phone);
ps.setString(2, null);
ps.setString(3, null);
ps.setTimestamp(4, new Timestamp(new Date().getTime()));
ps.setTimestamp(5, new Timestamp(new Date(0).getTime()));
ps.addBatch();
if(k%500 == 0){
        ps.executeBatch();
k = 0;
}
}
ps.executeBatch();
conn.commit();
ps.close(); }

Answer 3

上面是我的批量插入方式。PreparedStatement方式预编译sql。。通过每次设置参数，，和你的每次加一个insert into...然后你用的Statement不太一样。。还有需要说明的是：计数器的大小，可以通过测试选择效率最高的值不一定越小效率越高，也不是越大越高。。

Answer 4

5000条1次 time++80000次time++ 做1次commit
相当于400000000 次或者 跳出循环才会commit？
那就是要session要存这么多的数据？？可能我理解错了

Answer 5

还有一个要注意的地方，你每次提交的时候或者量到一定的时候，清一下SESSION 二级缓存

Answer 6

每一次commit后都可以把原来的清空或者设置为null.