Spring 批量insert 效率问题

2019独角兽企业重金招聘Python工程师标准>>>

背景

• 使用框架spring mvc
• dao层用到是的spring jdbc

namedParameterJdbcTemplate batchUpdate 批量

• 具体代码如下

	public void testBatch(int count){
		String sqlStr ="INSERT INTO C_DSS_BASE_INP_SETTLE (\n" +
				"	ORG_ID,\n" +
				"	PK_HIS,\n" +
				"	SETTLING_DATETIME,\n" +
				"	ORDERED_BY,\n" +
				"	PERFORMED_BY,\n" +
				"	DEPT_DISCHARGE_FROM,\n" +
				"	ITEM_CLASS,\n" +
				"	COSTS,\n" +
				"	CHARGES,\n" +
				"	CREATE_TIME,\n" +
				"	DATA_TIME\n" +
				")\n" +
				"VALUES\n" +
				"	(\n" +
				"		:ORG_ID,\n" +
				"		:PK_HIS,\n" +
				"		:SETTLING_DATETIME,\n"+
				"		:ORDERED_BY,\n" +
				"		:PERFORMED_BY,\n" +
				"		:DEPT_DISCHARGE_FROM,\n" +
				"		:ITEM_CLASS,\n" +
				"		:COSTS,\n" +
				"		:CHARGES,\n" +
				"		:CREATE_TIME,\n" +
				"		:DATA_TIME\n" +
				"	)";
		SqlParameterSource[] params = new MapSqlParameterSource[count];
		for (int i = 0; i < count; i++) {
			MapSqlParameterSource param = new MapSqlParameterSource();
			param.addValue("ORG_ID", "290001");
			param.addValue("PK_HIS", "2016-07-14 00:00:00");
			param.addValue("SETTLING_DATETIME", "2016-07-14 00:00:00");
			param.addValue("ORDERED_BY", "1000");
			param.addValue("PERFORMED_BY", "600A");
			param.addValue("DEPT_DISCHARGE_FROM", "3401");
			param.addValue("ITEM_CLASS", "3401");
			param.addValue("COSTS", "5.34");
			param.addValue("CHARGES", "5.34");
			param.addValue("CREATE_TIME", "2016-07-14 14:23:43");
			param.addValue("DATA_TIME", "2016-07-14 14:23:43");
			params[i] = param;
		}
		namedParameterJdbcTemplate.batchUpdate(sqlStr,params);
	}

• 10000条平均需要290s左右

使用拼接SQL方式方式批量操作

• 拼接方式如下

insert  test (....)  values(),(),().......();

• 具体代码如下

StringBuilder builder = new StringBuilder("INSERT INTO C_DSS_BASE_INP_SETTLE (\n" +
				"	ORG_ID,\n" +
				"	PK_HIS,\n" +
				"	SETTLING_DATETIME,\n" +
				"	ORDERED_BY,\n" +
				"	PERFORMED_BY,\n" +
				"	DEPT_DISCHARGE_FROM,\n" +
				"	ITEM_CLASS,\n" +
				"	COSTS,\n" +
				"	CHARGES,\n" +
				"	CREATE_TIME,\n" +
				"	DATA_TIME\n" +
				") VALUES  \n" );
		for (int i = 0; i <count; i++) {
			builder.append(
					" \n" +
					"	(\n" +
					"		'290001',\n" +
					"		'2016-07-14 00:00:00',\n" +
					"		'2016-07-14 00:00:00',\n" +
					"		'1000' ,\n" +
					"		'600A' ,\n" +
					"		'3401' ,\n" +
					"		'A',\n" +
					"		'5.34',\n" +
					"		'5.34',\n" +
					"		'2016-07-14 14:23:43',\n" +
					"		'2016-07-14 14:23:43'\n" +
					"	),");
		}
		String sqlStr = builder.toString();
		sqlStr = sqlStr.substring(0, sqlStr.length()-1);
		namedParameterJdbcTemplate.getJdbcOperations().update(sqlStr);

• 10000条平均需要1s左右

结论

两种方式差距相当大，故批量操作建议使用第二种，不过至于为第二种效率高，
本菜鸟还需要进一步研究下，如果有知道原因的大神请指教哈。

转载于:https://my.oschina.net/CPFspace/blog/808050