日期:2014-05-20  浏览次数:20702 次

批量导入真的可以提高效率吗,JDBC批量导入和普通循环插入数据的比较
一直没试过批量导入,今天看看效果,首选说明下我的环境,数据库SQL Server2000,tomcat,都是在同一台机进行,我做了导入10W条记录的比较:

两段代码如下:
1。普通循环插入的代码:
 
Java code
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。批量导入代码:
 
Java code
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咱占用率都很正常,请大家有时间看一下,两个结果的消耗时间都差不多,不知道批量导入到底效率高在哪?

------解决方案--------------------