我正在尝试使用Hibernate(JPA)在5秒内在MYSQL表中插入100,000行。我尝试了hibernate提供的所有技巧,但仍然不能超过35秒。
第一次优化:我从IDENTITY序列生成器开始,这导致了60秒的插入时间。后来我放弃了序列生成器,开始@Id
自己阅读MAX(id)
并使用AtomicInteger.incrementAndGet()
分配字段来自己分配字段。这样将插入时间减少到35秒。
第二次优化:我通过添加启用批量插入
<prop key="hibernate.jdbc.batch_size">30</prop> <prop key="hibernate.order_inserts">true</prop> <prop key="hibernate.current_session_context_class">thread</prop> <prop key="hibernate.jdbc.batch_versioned_data">true</prop>
配置。令我震惊的是,批量插入对于减少插入时间没有任何作用。仍然是35秒!
现在,我正在考虑尝试使用多个线程进行插入。有人有指针吗?我应该选择MongoDB吗?
下面是我的配置:1.hibernate配置
<bean id="entityManagerFactoryBean" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan" value="com.progresssoft.manishkr" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
</property>
<property name="jpaProperties">
<props>
<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.jdbc.batch_size">30</prop>
<prop key="hibernate.order_inserts">true</prop>
<prop key="hibernate.current_session_context_class">thread</prop>
<prop key="hibernate.jdbc.batch_versioned_data">true</prop>
</props>
</property>
</bean>
<bean class="org.springframework.jdbc.datasource.DriverManagerDataSource"
id="dataSource">
<property name="driverClassName" value="${database.driver}"></property>
<property name="url" value="${database.url}"></property>
<property name="username" value="${database.username}"></property>
<property name="password" value="${database.password}"></property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactoryBean" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
`
`
@Entity
@Table(name = "myEntity")
public class MyEntity {
@Id
private Integer id;
@Column(name = "deal_id")
private String dealId;
....
....
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "timestamp")
private Date timestamp;
@Column(name = "amount")
private BigDecimal amount;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "source_file")
private MyFile sourceFile;
public Deal(Integer id,String dealId, ....., Timestamp timestamp, BigDecimal amount, SourceFile sourceFile) {
this.id = id;
this.dealId = dealId;
...
...
...
this.amount = amount;
this.sourceFile = sourceFile;
}
public String getDealId() {
return dealId;
}
public void setDealId(String dealId) {
this.dealId = dealId;
}
...
...
....
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
....
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
`
`
@Service
@Transactional
public class ServiceImpl implements MyService{
@Autowired
private MyDao dao;
....
`void foo(){
for(MyObject d : listOfObjects_100000){
dao.persist(d);
}
}
`4.道课:
`
@Repository
public class DaoImpl implements MyDao{
@PersistenceContext
private EntityManager em;
public void persist(Deal deal){
em.persist(deal);
}
}
`
日志:
DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:32.906 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:32.906 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:32.906 [http-nio-8080-exec-2]
……
DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.b.internal.AbstractBatchImpl - Reusing batch statement
18:26:34.002 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - insert into deal (amount, deal_id, timestamp, from_currency, source_file, to_currency, id) values (?, ?, ?, ?, ?, ?, ?)
18:26:34.002 [http-nio-8080-exec-2] DEBUG o.h.e.j.batch.internal.BatchingBatch - Executing batch size: 27
18:26:34.011 [http-nio-8080-exec-2] DEBUG org.hibernate.SQL - update deal_source_file set invalid_rows=?, source_file=?, valid_rows=? where id=?
18:26:34.015 [http-nio-8080-exec-2] DEBUG o.h.e.j.batch.internal.BatchingBatch - Executing batch size: 1
18:26:34.018 [http-nio-8080-exec-2] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - committed JDBC Connection
18:26:34.018 [http-nio-8080-exec-2] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - re-enabling autocommit
18:26:34.032 [http-nio-8080-exec-2] DEBUG o.s.orm.jpa.JpaTransactionManager - Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@2354fb09] after transaction
18:26:34.032 [http-nio-8080-exec-2] DEBUG o.s.o.jpa.EntityManagerFactoryUtils - Closing JPA EntityManager
18:26:34.032 [http-nio-8080-exec-2] DEBUG o.h.e.j.internal.JdbcCoordinatorImpl - HHH000420: Closing un-released batch
18:26:34.032 [http-nio-8080-exec-2] DEBUG o.h.e.j.i.LogicalConnectionImpl - Releasing JDBC connection
18:26:34.033 [http-nio-8080-exec-2] DEBUG o.h.e.j.i.LogicalConnectionImpl - Released JDBC connection
‘
在尝试了所有可能的解决方案之后,我终于找到了一种在5秒内插入100,000行的解决方案!
我尝试过的事情:
1)使用AtomicInteger用自己生成的ID替换了hibernate/数据库的AUTOINCREMENT / GENERATED ID
2)以batch_size = 50启用batch_inserts
3)在每“ batch_size”个persist()调用之后刷新缓存
4)多线程(没有尝试过这个)
最后,有效的方法是使用 本机多插入查询, 并在一个sql插入查询中插入1000行,而不是在每个实体上使用 persist()
。为了插入100,000个实体,我创建了这样的本机查询"INSERT into MyTable VALUES (x,x,x),(x,x,x).......(x,x,x)"
[在一个sql插入查询中插入1000行]
现在,插入100,000条记录大约需要3秒钟!因此,瓶颈是orm本身!对于批量插入,似乎唯一起作用的是本机插入查询!
问题内容: 使用休眠时,当我尝试使用以下方式启用批量插入时 我得到以下输出: 然后这个: 基本上没有。 我是否缺少设置? 问题答案: 原来这种情况下缺少的是: 参考:https : //forum.hibernate.org/viewtopic.php ? p =2374413,https : //stackoverflow.com/a/5240930/32453 或可能是hibernate.or
问题内容: 我正在浏览hibernate文档,并遇到了逆属性的概念。我是Hibernate的新手,所以我很难正确理解该概念。 http://docs.jboss.org/hibernate/orm/3.3/reference/zh- CN/html/collections.html#collections- bidirectional 从上面的代码中,应用于类别,因此我知道类别是相反的一端。 但是
问题内容: 我知道POSIX 函数会使程序hibernatex秒。在C ++中是否有使程序hibernatex 毫秒 的功能? 问题答案: 请注意,毫秒级没有标准的C API,因此(在Unix上)您必须满足,它接受微秒:
问题内容: 如何将sql查询写入休眠条件? 问题答案: 认为我找到了答案。您必须使用Projection.sqlProjection()而不是Projections.sum()。例:
问题内容: 我们遇到了遗留代码问题。有一个用于“用户名”字段的验证集,用于验证其长度并确保其至少包含一个字母: 我们面临的问题是,一些现有的旧数据不适合这些验证,我正在尝试找到一种方法,使旧数据(旧用户)可以忽略这些验证,同时仍将这些验证应用于新创建的用户。 我正在考虑将验证移至方法(因此将仅在实际更改时验证值),但这导致了异常: 我还确保将实体设置为,但这无济于事,因为hibernate会验证所
问题内容: 有没有办法使用Hibernate全局设置属性(因此它将是所有实体的默认设置)? 问题答案: 在NHibernate中是 我不知道hibernate中的确切语法。