最近比较忙!在赶好多东西!
在海南的哪个项目中,需要对两百多万的数据进行批量插入、删除等操作,同事写的第一个方法需要运行30多个小时才能运行完,后经过他的优化可以缩小到17小时,这已经优化了许多,但客户那边还是不能接受这个速度,后再优化,也就是把一个230万行的文件拆分成N个10万行/个的文件,然后再用他的程序去读,后也还是很慢,也就是开始会很快,但后来还是会越来越慢,在邻近春节之际,他回家过年了,把这个优化交到了俺手里,想着吧,挑战一下吧!
在开始优化之前,我看了他写的代码,他用的是Statement对象,上网看了下,分别有1.使用Statement对象
2.预编译PreparedStatement
3.使用PreparedStatement + 批处理
的使用对比介绍,同事所用的是Statement对象,优缺点如下:
使用范围:当执行相似SQL(结构相同,具体值不同)语句的次数比较少
优点:语法简单
缺点:采用硬编码效率低,安全性较差。
原理:硬编码,每次执行时相似SQL都会进行编译
2.预编译PreparedStatement
使用范围:当执行相似sql语句的次数比较多(例如用户登陆,对表频繁操作..)语句一样,只是具体的值不一样,被称为动态SQL
优点:语句只编译一次,减少编译次数。提高了安全性(阻止了SQL注入)
缺点: 执行非相似SQL语句时,速度较慢。
原理:相似SQL只编译一次,减少编译次数
名词解释:
SQL注入:select * from user where username="张三" and password="123" or 1=1;
前面这条语句红色部分就是利用sql注入,使得这条词句使终都会返回一条记录,从而降低了安全性。
3.使用PreparedStatement + 批处理
使用范围:一次需要更新数据库表多条记录
优点:减少和SQL引擎交互的次数,再次提高效率,相似语句只编译一次,减少编译次数。提高了安全性(阻止了SQL注入)
缺点:
原理:
批处理: 减少和SQL引擎交互的次数,一次传递给SQL引擎多条SQL。
名词解释:
PL/SQL引擎:在oracle中执行pl/sql代码的引擎,在执行中发现标准的sql会交给sql引擎进行处理。
SQL引擎:执行标准sql的引擎。
看了下对比,明显是第三种方式快嘛,所以俺就改了一下程序,用了第三种方法去批量操作数据,结果速度相当快!这几百万行数据只用了20分钟就读完并插入数据库了!
俺参考的例子如下:
public void exec3(Connection conn){
try {
conn.setAutoCommit(false);
Long beginTime = System.currentTimeMillis();
PreparedStatement pst = conn.prepareStatement("insert into t1(id) values (?)");
for(int i=1;i<=10000;i++){
pst.setInt(1, i);
pst.addBatch();//加入批处理,进行打包
if(i%1000==0){//可以设置不同的大小;如50,100,500,1000等等
pst.executeBatch();
conn.commit();
pst.clearBatch();
}
}
pst.executeBatch();
Long endTime = System.currentTimeMillis();
System.out.println("pst+batch用时:"+(endTime-beginTime)+"毫秒");
pst.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
优化完之后,我发到海南那边测试,结果他那边也只是用了30分钟左右就把数据读完了,还挺高兴的,起码优化了这么多啊!
后来等发正式的系统给那边的时候,结果却没有了之前那么快了!也用了几个小时才搞定,这也奇怪了,在俺电脑上跑了几次,都是20分钟就完事的,但在那边怎么那么久呢?最后想想,可能是dblink的问题吧,当然现在还只是猜测,集体的原因还得等到过完年后再去探究了!
顶啊!
转载于:https://blog.51cto.com/johnny84/767001