Oracle批量提交
今天在项目中碰到一个问题,就是需要批量提交10000条语句,
刚开始使用的是statement.addBatch(),结果提交的时候发现需要26秒;
检查半天,发现批量提交的时候也要关掉自动提交(重要),不然极其影响性能。
改了一下程序,最终代码如下,执行时间为1秒钟:
1 // 获取一个连接 2 Connection conn = null; 3 PreparedStatement pstm = null; 4 try { 5 conn = DBConnectionManager.getInstance().getConnection( 6 ConfigDef.DS_UPDATE_KEY); 7 conn.setAutoCommit(false); 8 // statement = conn.createStatement(); 9 Map<String, String> resultMap = collectResult.get(0); 10 //获取第一个 11 StringBuilder keyParams = new StringBuilder(); 12 StringBuilder valueParams = new StringBuilder(); 13 for (Map.Entry<String, String> entry : resultMap.entrySet()) { 14 keyParams.append(", ").append(entry.getKey()); 15 valueParams.append(", ? "); 16 } 17 String sqlTemplate = MessageFormat.format(SqlConstant.INSERT_SQL_TEMPLATE, new Object[]{"TB_BMP_SXAUDIT_DATA_TEMP",keyParams,valueParams}); 18 logger.info("sqlTemplate:"+ sqlTemplate); 19 pstm = conn.prepareStatement(sqlTemplate); 20 21 int len = collectResult.size(); 22 String uid = auditObject.getAuditObjectId()+"_"+ taskSeq; 23 for (int i = 0; i < len; i++) { 24 25 // 对于每一个结果,都要生成往临时表插入一条SQL语句 26 resultMap = collectResult.get(i); 27 //第一个参数是UID 28 pstm.setString(1, uid); 29 // 第2个参数是TASK_ID 30 pstm.setInt(2, taskInfo.getPlanId()); 31 // 第3个参数是BATCH_NO 32 Date batchNO = new Date(format.parse(receiveTask.getTaskStartTime()).getTime()); 33 System.out.println(batchNO); 34 pstm.setDate(3, batchNO); 35 //第4个参数是CollectID 36 pstm.setLong(4,Long.valueOf(auditObject.getAuditObjectId())); 37 // 第5个参数是TARGET_DB 38 pstm.setString(5, auditObject.getTargetDb()); 39 // 第8个参数是AUDIT_TIMES 40 pstm.setInt(6,1); 41 int idx = 7; 42 for (Map.Entry<String, String> entry : resultMap.entrySet()) { 43 pstm.setString(idx++, entry.getValue()); 44 } 45 pstm.addBatch(); 46 } 47 logger.info("开始执行批量提交"); 48 pstm.executeBatch(); 49 conn.commit(); 50 //等待任务执行成功,调用稽核存过 51 logger.info("开始调用存过");