Batch insert extremely slow [message #1446649] |
Fri, 17 October 2014 05:29 |
John Lewis Messages: 2 Registered: October 2014 |
Junior Member |
|
|
I'm trying to optimize my JPA implementation, using EclipseLink. I've added batch operations to it. But it is still taking A LOT of time to do 50 000 inserts. It takes more than 10 times the amount of time it takes to do the exact same insert using raw SQL with JDBC.
To make sure batch operations were in fact working I used Wireshark to check my packets and it is not using batch inserts.
Here's one of the insert packets:
It is not doing:
INSERT INTO ENTITYCLASSTEST (LASTNAME, NAME) VALUES ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0'), ('sfirosijfhgdoi 0', 'dsufius0')... and so on
I was expecting it to do as above but it is inserting one line per packet and not multiple lines per packet.
Here's is my Entity Class:
@Entity
public class EntityClassTest implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String lastname;
public EntityClassTest() {
}
public EntityClassTest(Long id, String name, String lastname) {
this.id = id;
this.name = name;
this.lastname = lastname;
}
public EntityClassTest(String name, String lastname) {
this.name = name;
this.lastname = lastname;
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public String getLastName() {
return lastname;
}
public void setId(Long id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setLastName(String lastname) {
this.lastname = lastname;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof EntityClassTest)) {
return false;
}
EntityClassTest other = (EntityClassTest) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "database.EntityClassTest [id=" + id + " ]";
}
}
And here is my persist method that receives a List and persists all the objects inside.
public void insertListToTable(final String persistenceUnit, final List list) throws SQLException {
final EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory(persistenceUnit);
final EntityManager entityManager = entityManagerFactory.createEntityManager();
final EntityTransaction transaction = entityManager.getTransaction();
try {
final int listSize = list.size();
transaction.begin();
for (int i = 0; i<listSize; i++) { //Object object : list) {
final Object object = list.get(i);
entityManager.persist(object);
if ( i % 500 == 0 ) { //500, same as the JDBC batch size defined in the persistence.xml
//flush a batch of inserts and release memory:
entityManager.flush();
entityManager.clear();
}
}
transaction.commit();
}
catch(Exception e) {
if (transaction != null) {
transaction.rollback();
}
throw new SQLException(e.getMessage());
}
finally {
entityManager.close();
}
}
And my persistence.xml, where I set 500 as the batch value, file is:
<persistence-unit name="ExternalServer" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<!-- List of Entity classes -->
<class>model.EntityClassTest</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://myServer:3306/testdb?zeroDateTimeBehavior=convertToNull"/>
<property name="javax.persistence.jdbc.user" value="testdbuser"/>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.password" value="myPassword"/>
<property name="javax.persistence.schema-generation.database.action" value="create"/>
<!-- Weaving -->
<property name="eclipselink.weaving" value="static"/>
<!-- SQL dialect / Database type -->
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="eclipselink.target-database" value="MySQL"/>
<!-- Tell the JPA provider to, by default, create the table if it does not exist. -->
<property name="javax.persistence.schema-generation.database.action" value="create"/>
<!-- No logging (For development change the value to "FINE") -->
<property name="eclipselink.logging.level" value="OFF"/>
<!-- Enable batch writing -->
<property name="eclipselink.jdbc.batch-writing" value="JDBC"/>
<!-- Batch size -->
<property name="eclipselink.jdbc.batch-writing.size" value="500"/>
</properties>
</persistence-unit>
</persistence>
So my question is, why is it not batch inserting? I believe I've EclipseLink well configured to do so from what I've been reading around on EclipseLink wiki website.
PS; I've made the same question at Stackoverflow and I'll update both places whenever I find the fix to my problem.
I can't post links while I've less than 5 posts so here is the url: stackoverflow.com/questions/26416847/eclipselink-batch-insert-very-very-slow
Thanks.
[Updated on: Fri, 17 October 2014 14:31] Report message to a moderator
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04419 seconds