达梦数据库批量插入方法性能比较

最近碰到一个sqoop从hadoop导入到达梦的性能问题，挺有意思，和大家分享一下：

1.先来做一个纯SQL的批量插入性能测试：

create table test_insert(id int,name varchar(100),addr varchar(200));

declare
 sqlstr varchar;
begin
 for i in 1..10000 loop
  sqlstr:='insert into test_insert values ';
   for j in 1..100 loop
     sqlstr=sqlstr || '(' ||j|| ',''test'',''testestewt''),';
   end loop;
   sqlstr=rtrim(sqlstr,',') ;
   execute immediate sqlstr;
  end loop;
  commit;
end;

--执行成功, 执行耗时3秒 546毫秒. 执行号:8609

select count(*) from test_insert;

truncate table test_insert;

declare
sqlstr varchar;
begin
 for i in 1..1000000 loop
 insert into test_insert values (i,'test','testestewt');
 if mod(i,5000)=0 then
   commit;
 end if;
 end loop;
 commit;
end;
--执行成功, 执行耗时5秒 56毫秒. 执行号:8631

select count(*) from test_insert;

truncate table test_insert;


declare
sqlstr varchar;
_id Int;
_name varchar;
_addr varchar;
begin
 sqlstr:='insert into test_insert values(?,?,?)';
for i in 1..1000000 loop
 _id=i;
 _name:='test';
 _addr:='testestewt';
  execute immediate sqlstr using _id,_name,_addr;
 if mod(i,5000)=0 then
   commit;
 end if;end loop;
end;

--执行成功, 执行耗时9秒 0毫秒. 执行号:8634

从这个测试可以看出一次插入多行的insert方法似乎效率最高，但是和批量有一些关系，

下面再看看不同批量的影响：

-- 批量50：执行成功, 执行耗时3秒 379毫秒. 执行号:8651
-- 批量100：执行成功, 执行耗时3秒 358毫秒. 执行号:8648
-- 批量200：执行成功, 执行耗时3秒 408毫秒. 执行号:8645
-- 批量500：执行成功, 执行耗时3秒 851毫秒. 执行号:8642
-- 批量1000：执行成功, 执行耗时4秒 761毫秒. 执行号:8637

可以看出采用存储过程方式，这个批量不宜过大，50到200之间会比较好。

2. 下面再来看看使用jdbc批量插入的效果：

//采用一次插入多行的方式，每次插入2000行，共6000个参数
public void testBatchInsert1(){
		 
	  String sqlstr="";   
	     
		 PreparedStatement ps = null;
		 sqlstr = "insert into test_insert values (?,?,?)";
		  for (int j = 1; j < 2000; j++) {

		   sqlstr = sqlstr + ",( ?,?,?) ";
		  }
	 	 
	 	 System.out.println(sqlstr); 
	 	 try{
		       	 
	 	 ps = conn.prepareStatement(sqlstr); 
	 	long startTime=System.currentTimeMillis();   //获取开始时间
	    for (int i = 0; i <500; i++) {

	        for (int j = 0; j <2000; j++) {
	        	    ps.setInt(3*j+1, i*100+j);
	        	    ps.setString(3*j+2,"test" );
	        	    ps.setString(3*j+3, "testestewt");
	        }
	        ps.addBatch();
	        ps.executeBatch();
	        conn.commit();
	        ps.clearBatch();
	        
	    }

	    conn.commit();
	    long endTime=System.currentTimeMillis(); //获取结束时间  
	    System.out.println("插入耗时： "+(endTime-startTime)+"ms"); 
	    conn.close();
	}catch(Exception e){
		        e.printStackTrace();
	  }finally{
		        
		      }
	} 

-- 一次插入100行，300个参数
--第一次执行时间：11919ms
--第二次执行时间：9536ms
--第三次执行时间： 9463ms

-- 一次插入500行，1500个参数
--第一次执行时间： 5833ms
--第二次执行时间：6022ms
--第三次执行时间： 5804ms

-- 一次插入2000行，6000个参数
--第一次执行时间：4935ms
--第二次执行时间：4597ms
--第三次执行时间：4631ms

-- 一次插入5000行，15000个参数
--第一次执行时间：3965ms
--第二次执行时间：3953ms
--第三次执行时间：4008ms

//采用批量绑定参数的方式，一次绑定5000批参数，5000条提交一次
public void testBatchInsert2(){
		 
	     String sqlstr="";   
	     
		 PreparedStatement ps = null;
		 sqlstr = "insert into test_insert values (?,?,?)";
	 	 try{
	 	 conn.setAutoCommit(false);      	 
	 	 ps = conn.prepareStatement(sqlstr); 
	 	long startTime=System.currentTimeMillis();   //获取开始时间
	    for (int i = 1; i <=1000000; i++) {

	        
	        	    ps.setInt(1, i);
	        	    ps.setString(2,"test" );
	        	    ps.setString(3, "testestewt");
	        	    ps.addBatch();
	        	    if(i%5000==0){
	        	    	ps.executeBatch();
	        	    	conn.commit();
	        	    	ps.clearBatch();
	        	    	}
	    }

	    conn.commit();
	    long endTime=System.currentTimeMillis(); //获取结束时间  
	    System.out.println("插入耗时： "+(endTime-startTime)+"ms"); 
	    conn.close();
	}catch(Exception e){
		        e.printStackTrace();
	  }finally{
		        
		      }
	} 

--批量绑定方式，100个参数一批，100提交一次
--第一次执行时间：25872ms
--第二次执行时间：21281ms
--第三次执行时间：21416ms

--批量绑定方式，1000个参数一批，1000提交一次
--第一次执行时间：7805ms
--第二次执行时间：7872ms
--第三次执行时间：7608ms

--批量绑定方式，5000个参数一批，5000提交一次
--第一次执行时间：5940ms
--第二次执行时间：8051ms
--第三次执行时间：6125ms

可以看出jdbc批量插入方式，增加批量减少commit的次数能显著提升性能。

一次插入多行参数绑定的方式，也要加大批量，减少网络交互的次数，能提升性能

3.回到文章开头提到的sqoop导入性能的优化问题，发现sqoop导入到达梦默认采用testBatchInsert1的一次插入多行参数绑定的方式，但是每批参数设置过小，导致出现插入性能问题，但是这个每批参数多少个似乎没有地方可以设置（不太了解sqoop，有知道的朋友欢迎讨论）。后来在 sqoop的配置里面加入了 --batch参数，sqoop采用了testBatchInsert2，批量绑定参数的方式，性能得到显著提升。