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;
}
}