h2事务与mysql_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)

mysql

hibernate

循环调用save插入

127910

mysql

hibernate

saveOrUpdateAll插入

4499

mysql

jdbcTemplate

循环调用update插入

3045

mysql

jdbcTemplate

调用update一次插入全部value

2295

h2

hibernate

循环调用save

3529

h2

hibernate

saveOrUpdateAll

2037

h2

jdbcTemplate

循环调用update插入

796

h2

jdbcTemplate

调用update一次插入全部value

610

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;

}

}