mysql、h2插入性能对比

2013-01-05 更新:导致循环调用save插入慢的问题是我循环调用了service方法,而不是在service内部循环,而事物是在service级别,所以相当于每次插入都提交事物,所以慢了。


同样是插入,差别咋就这大呢?


操作系统:wubi ubuntu 12.04 (precise) 32 位

内核: Linux 3.2.0-33-generic-pae

内存:4GB

cpu:Intel® Core™2 Duo CPU T6570 @ 2.10GHz × 2

mysql:版本5.5.28-0ubuntu0.12.04.2, innodb,本地服务

h2: 版本1.3.169, 嵌入式

插入数据:1656行,id自动增长

数据库持久层操作方式耗时(ms)
mysqlhibernate循环调用save插入127910
mysqlhibernatesaveOrUpdateAll插入4499
mysqljdbcTemplate循环调用update插入3045
mysqljdbcTemplate调用update一次插入全部value2295
h2hibernate循环调用save3529
h2hibernatesaveOrUpdateAll2037
h2jdbcTemplate循环调用update插入796
h2jdbcTemplate调用update一次插入全部value610



mysql hibernate loop insert| data size:1656, time:127910 ms
mysql hibernate saveOrUpdateAll| data size:1656, time:4499 ms


mysql jdbcTemplate loop insert| data size:1656, time:3045 ms
mysql jdbcTemplate multi values| data size:1656, time:2295 ms


h2 hibenate loop insert| data size:1656, time:3529 ms
h2 hibernate saveOrUpdateAll| data size:1656, time:2037 ms


h2 jdbcTemplate loop insert| data size:1656, time:796 ms
h2 jdbcTemplate multi values| data size:1656, time:610 ms



配置:

#hibernate.connection.driver_class = com.mysql.jdbc.Driver
#hibernate.connection.url = jdbc:mysql://127.0.0.1:3306/emds?characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true
#hibernate.connection.username = mysql
#hibernate.connection.password = mysql
#hibernate.dialect = org.hibernate.dialect.MySQLDialect

hibernate.connection.driver_class = org.h2.Driver
hibernate.connection.url = jdbc:h2:db/emds_db
hibernate.connection.username = h2
hibernate.connection.password = h2
hibernate.dialect = org.hibernate.dialect.H2Dialect

hibernate.show_sql = true
hibernate.format_sql = false
hibernate.batch_size = 20
hibernate.hbm2ddl.auto = update
hibernate.cglib.use_reflection_optimizer = true
hibernate.cache.use_second_level_cache = false
hibernate.cache.use_query_cache = true
hibernate.cache.provider_class = org.hibernate.cache.EhCacheProvider


model

package com.emds.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import com.emds.util.DateUtil;

@Entity
@Table(name="air_quality_data")
public class AirQualityData {
	
	public enum DataType{
		AllAirQuality((short)1);
		
		private short dataType;
		
		DataType(short dataType){
			this.dataType = dataType;
		}
		
		public short getDataType(){
			return dataType;
		}
	}

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private int id;
	
	@Column(name="area", length=20, nullable=false)
	private String area;
	
	@Column(name="type", nullable=false)
	private short type;
	
	@Column(name="time", columnDefinition="DATETIME")
	private Date time;
	
	@Column(name="index_name", length=20, nullable=false)
	private String indexName;
	
	@Column(name="index_value", length=20, nullable=false)
	private String indexValue;
	
	@Override
	public String toString(){
		return "{" + id + " " + area + "\t" + DateUtil.dateToStr_yyyy_MM_dd_HH_mm(time) + "\t" + indexName + "\t" + indexValue + "}";
	}
	
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public String getIndexName() {
		return indexName;
	}
	public void setIndexName(String indexName) {
		this.indexName = indexName;
	}
	public String getIndexValue() {
		return indexValue;
	}
	public void setIndexValue(String indexValue) {
		this.indexValue = indexValue;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getArea() {
		return area;
	}

	public void setArea(String area) {
		this.area = area;
	}

	public short getType() {
		return type;
	}

	public void setType(short type) {
		this.type = type;
	}
	
}



评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值