查看完整内容

插入股票分钟线数据，大约6000度条，速度要优化，现在大概需要100秒左右，有什么建议

public void updateSZIndexStockMinute(SzSourceBean bean) {
		try{
		boolean updateFlag = false;
		long a=System.currentTimeMillis();
		
		int minuteCount = DatesUtil.getMinuteCount(bean.getMDTime());
		int lastMinuteCount = minuteCount - 1;
		int k = 0;
		
		if (isTradeMinute(lastMinuteCount)) {
			Sql sql = Sqls.create("INSERT INTO ADMINISTRATOR.STKMINUTEDATA_SZ(CODE,DATE,MINUTE,NOW,AVERAGE,NOWVOL)" + 
					" SELECT @CODE, @DATE,@MINUTE, @NOW, (sum_now+@NOW)/(count_now+1), @NOWVOL" +
					" FROM (SELECT case  when sum(now) is null then 0 else sum(now) end as sum_now," +
					" count(now) as count_now" +
					" FROM ADMINISTRATOR.STKMINUTEDATA_SZ WHERE code=@CODE and date=@DATE) aa");
			for (int i = lastMinuteCount; i > 0; i-- ) {
				Map<String, SzIndexMinKey> minKeys = HqConstants.SZ_MIN_KEY.get(DatesUtil.getDateStr(bean.getMDTime())+"-" + lastMinuteCount);
				if (null != minKeys) {
					// 如果找到上一分钟的记录
					if (k == 0) {
						Iterator<String> it = minKeys.keySet().iterator();
						while (it.hasNext()) {
							SzIndexMinKey key = minKeys.get(it.next());
							if (null != key.getCode()) {
								sql.params().set("CODE", key.getCode()).set("DATE", key.getDate())
								.set("MINUTE", key.getMin()).set("NOW", key.getNow()).set("NOWVOL", key.getNowvol());
								sql.addBatch();
								updateFlag = true;
							}
						}
					} else {
						Iterator<String> it = minKeys.keySet().iterator();
						while (it.hasNext()) {
							SzIndexMinKey key = minKeys.get(it.next());
							if (null != key.getCode()) {
								sql.params().set("CODE", key.getCode()).set("DATE", key.getDate())
								.set("MINUTE", key.getMin()).set("NOW", key.getNow()).set("NOWVOL", key.getNowvol()/(k+1));
								sql.addBatch();
								updateFlag = true;
							}
						}
					}
					
					break;
				}
				k++;
			}
			
			if (updateFlag) {
				dao2().execute(sql);
				String dateStr = "SZ-" + DatesUtil.getFormatDate(bean.getMDTime());
				log(Constants.LOG_PARSE, "解析深证 [" + dateStr + "] :插入 [STKMINUTEDATA_SZ] ["+minuteCount+"]分 数据  执行时间  [" + ((System.currentTimeMillis()-a)/1000f) + "] 秒");
			}
		}
		} catch(Exception e) {
			log.error(e);
		}
	}


数据源配置
dataSource2 : {
		type : "com.alibaba.druid.pool.DruidDataSource",
		events : {
			create : "init",
			depose : 'close'
		},
		fields : {
			driverClassName : {
				java : "$conf.get('db2.driverClassName')"
		
			},
			url : {
				java : "$conf.get('db2.url')"
			},
			username : {
				java : "$conf.get('db2.username')"
			},
			password : {
				java : "$conf.get('db2.password')"
			},
			initialSize:20,
			minIdle:20,
			maxActive : 200,
			timeBetweenEvictionRunsMillis:15000,
            maxWait: 15000, // 若不配置此项,如果数据库未启动,druid会一直等可用连接,卡住启动过程,
            defaultAutoCommit : true, // 提高fastInsert的性能
            poolPreparedStatements: true,
            maxPoolPreparedStatementPerConnectionSize : 100,
            testWhileIdle : true,
            validationQuery : {
            	java : "$conf.get('db2.validationQuery')"
            }