Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Batch insert extremely slow
icon4.gif  Batch insert extremely slow [message #1446649] Fri, 17 October 2014 05:29 Go to next message
John Lewis is currently offline John LewisFriend
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:

index.php/fa/19546/0/

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

Re: Batch insert extremely slow [message #1448821 is a reply to message #1446649] Mon, 20 October 2014 13:48 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
The problem here is that you are using the IDENTITY sequence strategy, which has the database populate the id value when the insert statement occurs and the JPA provider must then fetch it from the database. Unfortunately, the database only allows retrieving the generated ID from the last insert statement, so they can only be executed one at a time, negating the batch writing settings. If you wish to use batch writing, you will need to pick a generation strategy that allows you to obtain enough sequences for your batch upfront, such as a table sequencing or a SEQUENCE strategy. Remember to set your allocation size to be greater than the batch size you wish to use or you may end up with the provider having to interrupt the batch to use the connection to fetch more sequence values. You might also want to use a sequencing connection pool if using table sequencing, as described here: http://www.eclipse.org/eclipselink/documentation/2.4/concepts/data_access006.htm#CHDEFJHH

MySql may not support a Sequence strategy, so you might want to try table sequencing instead.
Re: Batch insert extremely slow [message #1449127 is a reply to message #1448821] Tue, 21 October 2014 00:00 Go to previous message
John Lewis is currently offline John LewisFriend
Messages: 2
Registered: October 2014
Junior Member
Chris Delahunt is right.

Plus I also forgot to add:
?rewriteBatchedStatements=true


to the persistence.xml file, I added a few days before to the JDBC and I was convinced that I also added it to the persistence.xml file.

I believe you are the same person that did help me out on stackoverflow.

Thank you very much for your time and patience Smile
Previous Topic:How can I generate ddl scripts without using Dali?
Next Topic:@CacheIndex with Extended Class
Goto Forum:
  


Current Time: Fri Mar 29 14:25:16 GMT 2024

Powered by FUDForum. Page generated in 0.05026 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top