批量导入真的可以提高效率吗，JDBC批量导入和普通循环插入数据的比较

Question

一直没试过批量导入，今天看看效果，首选说明下我的环境，数据库SQL Server2000，tomcat，都是在同一台机进行，我做了导入10W条记录的比较：两段代码如下：
1。普通循环插入的代码：
        out.println("循环插入数据...<br/>");
long startTime = System.currentTimeMillis();
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=yiqianmi";
//test你的数据库的 
String user = "sa"; 
String password = "ok";
Connection conn = DriverManager.getConnection(url,user,password);
conn.setAutoCommit(false);
PreparedStatement pst = (PreparedStatement)conn.prepareStatement("insert into batch_index values (?,?,?,?)"); 
int addNum = 100000;
int startPos = 0;
int endPos = addNum+startPos;
out.println("插入"+addNum+"条数据<br>");
java.util.Date curDate = new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm");
String curDateStr = sdf.format(curDate);
System.out.println("循环插入"+addNum+"条数据...");
for(int i=startPos+1; i<=endPos; i++){
pst.setInt(1,i);
pst.setInt(2,i+100);
pst.setString(3,new String(("第"+i+"班名称").getBytes("GBK"),"iso8859-1"));
pst.setString(4, curDateStr);
pst.execute();
}
conn.commit();
System.out.println("================================================");
System.out.println("插入成功");
conn.close();
long endTime = System.currentTimeMillis();
out.println("消耗时间："+(endTime-startTime)+"ms"); 2。批量导入代码：
       out.println("批量插入数据...<br/>");
long startTime = System.currentTimeMillis();
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;SelectMethod=cursor;DatabaseName=yiqianmi";
//test你的数据库的 
String user = "sa"; 
String password = "ok";
Connection conn = DriverManager.getConnection(url,user,password);
conn.setAutoCommit(false);
PreparedStatement pst = (PreparedStatement)conn.prepareStatement("insert into batch_index values (?,?,?,?)"); 
int addNum = 100000;
int startPos = 0;
int endPos = addNum+startPos;
out.println("插入"+addNum+"条数据<br>");
java.util.Date curDate = new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm");
String curDateStr = sdf.format(curDate);
System.out.println("批量插入"+addNum+"条数据...");
for(int i=startPos+1; i<=endPos; i++) {
pst.setInt(1,i);
pst.setInt(2,i+100);
pst.setString(3,new String(("第"+i+"班名称").getBytes("GBK"),"iso8859-1"));
pst.setString(4,curDateStr);
pst.addBatch();
if(i % 100 == 0){
pst.executeBatch();
}
}
pst.executeBatch();
conn.commit();
System.out.println("================================================");
System.out.println("插入成功");
conn.close();
long endTime = System.currentTimeMillis();
out.println("消耗时间："+(endTime-startTime)+"ms");
两个测试结果是：循环插入数据...
插入100000条数据
消耗时间：14156ms 
批量插入数据...
插入100000条数据
消耗时间：13734ms 
在运行期间CPU咱占用率都很正常，请大家有时间看一下，两个结果的消耗时间都差不多，不知道批量导入到底效率高在哪？

Answer 1

我的解释：1、对于insert语句，速度瓶颈主要是在磁盘IO上，所以，批量提交带来的优化效果有限。
2、（这只是猜测）因为是SQL Server，你的数据库空间分配了多大，如果数据库空间过小，Sql Server一般动态扩张数据文件，会占用较多的事件，
应交替多做几次测试（每次测试要清空数据库），看平均。
3、将start时间放到for循环之前，得到的结果会更精确一写。
4、将批次设置的更大一写，如：1000试试。

Answer 2

还有，把endTime放到commit之前，再对比一下看看有什么不同？

Answer 3

你只是在sql server 上测试，建议你在不同数据库间进行测试，看看，这个程序相关，也跟驱动相关。其实批量导入导出不建议在java程序中进行，至少我开发的项目中不建议。一般大批量的数据库操作尽量用存储过程或者直接调用工具执行。

Answer 4

谢谢楼上两位
我不熟数据库空间分配和设置缓存
试过了jinxfei说过的方法，都是一样的结果。。另外在MySQL上也测试了下，结果是一样的批量插入数据MySQL...
插入100000条数据
消耗时间：18172ms 批量插入数据MySQL...
插入100000条数据
消耗时间：17390ms 

Answer 5

对于在java中批量操作，需要考虑到的是java的对象回收和内存溢出问题。对于数据库批量插入的效率，这些与数据库的优化器，驱动包，表结构，与索引的设计有关系。
10万条记录还是太少，你可以尝试着100万，就让他在那里跑。吃个饭回来再看看效率。
建立逐步建立索引再测试再跑对于你说的数据库的空间分配与缓存不太明白你想问什么。对于一条语句执行后效率是否高，还要看你的sql是否满足数据库优化器的条件。关系数据库中是否打破了关系。都有关系。

Answer 6

不考虑索引问题的
再说下我的操作步骤：
1。首先新建一个表batch_index(classId int, personNum int, className varchar, createTime)，表中没有任何数据
2。执行普通循环插入的JSP
3。清除原来的数据
4。执行批量插入的JSP
5。将两个结果进行比较就这样，很简单的操作
对上面的疑问：
如何进行数据库控件分配和设置缓存呢？
还要你说的对象回收，要怎么做才尽可能性能更好点?

Answer 7

刚刚执行完了100W数据的插入，结果如下：循环插入数据...
插入1000000条数据
消耗时间：138640ms 
批量插入数据...
插入1000000条数据
消耗时间：143640ms 结果，批量还比循环多点了？倒...

Answer 8

不知道你的测试环境是怎么样的，第一个测试结束得到运行时间后 重新启动服务器，然后测试第二个。如果你中间没有重新启动的话测试结果不会准，因为先后顺序后有对象回收和内存争用问题。导致二个方法测试与第一测试环境不一致，从而出现误差。至于防止内存溢出如何解决，这就要看编辑的经验和技巧，还有对GC的理解。两个方法测试你最后的结果都是运行成功，其实还不是最终的结果，你应该不断的加大数量例如200万，500万，知道测试有一个方法退出舞台而另一个方法还健在的结果，然后再观察gc的情况，在这个器件gc执行了多次回收，利用了多少时间去回收，最后老年期内存占用多少，各是多少。这么数据都列出来。这样的测试才算一段落而已。

Answer 9

LZ有空试下这个（没调试过）：
out.println("批量插入数据...<br/>");
    long startTime = System.currentTimeMillis();
    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
    String url = "jdbc:microsoft:sqlserver://127.0.0.1:1433;SelectMethod=cursor;DatabaseName=yiqianmi";
    //test你的数据库的 
    String user = "sa"; 
    String password = "ok";
    Connection conn = DriverManager.getConnection(url,user,password);
    conn.setAutoCommit(false);
    PreparedStatement pst = (PreparedStatement)conn.prepareStatement("insert into batch_index values (?,?,?,?)"); 
    int addNum = 100000;
    int startPos = 0;
    int endPos = addNum+startPos;
    int refPos = endPos/100;
    out.println("插入"+addNum+"条数据<br>");
    java.util.Date curDate = new java.util.Date();
    java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm");
    String curDateStr = sdf.format(curDate);
    System.out.println("批量插入"+addNum+"条数据...");
    for(int i=startPos+1; i<=refPos; i++) {
       for(int j=0; j<100; j++){
        pst.setInt(1,i+j);
        pst.setInt(2,i+100);
        pst.setString(3,new String(("第"+(i+j)+"班名称").getBytes("GBK"),"iso8859-1"));
        pst.setString(4,curDateStr);
        pst.addBatch();
}
pst.executeBatch();
    }
    pst.executeBatch();
    conn.commit();
    System.out.println("================================================");
    System.out.println("插入成功");
    conn.close();
    long endTime = System.currentTimeMillis();
    out.println("消耗时间："+(endTime-startTime)+"ms");

Answer 10

差别还是很大的，我用oracle测试了下。
插了三个字段
循环插入用时 44500ms批量插入用时 3407ms

Answer 11

感觉是数据库的问题耶，我在本机的sqlserver和mysql上建索引也不起效果..

Answer 12

Java 深度探索者 QQ群：65670864

Answer 13

批量操作比自己循环效率高是毋庸置疑的，光对比数据库执行效率是没用的，这得看具体数据库优化策略而定。这种大批量的操作往往是异步调用，让后台进程完成的。LZ完全可以跟踪一下堆栈轨迹，就能有明显的对比。自己循环所创建的内存空间远远多于批量操作，即使使用完的引用全部置null也没用的，先不谈分配内存的开销，java的垃圾回收机制并不是空闲的内存立即回收的，LZ可以深入学习一下垃圾回收算法。100W的数据不算什么，当出现千万级以上的数据瞬间创建那么多没必要的内存会不会outofmemory呢？