CODEWITHSUNDEEP
javamysqlhibernate
nickbusted
nickbusted

Reputation: 1109

Hibernate - Slow batch insert

The following is my code for batch inserting to MySQL remote database.

    Session session = db.setSession();
    Transaction tx = session.beginTransaction();

    for ( int i=0; i<e.getContents().size(); i++ ) {
        Content content = e.getContents().get(i);
        session.save(content);
        if ( i % 40 == 0 ) { 
            session.flush();
            session.clear();
        }
    }
    tx.commit();

The mapping is defined in the following way:

class name="Database.Content" table="..." schema="" catalog="...">
    <id name="id">
        <column name="id" sql-type="int" not-null="true"/>
        <generator class="identity"/>
    </id>
    <property name="week">
        <column name="week" sql-type="int"/>
    </property>
    <property name="type">
        <column name="type" sql-type="int" not-null="true"/>
    </property>
    <many-to-one name="group" class="Database.Group">
        <column name="`group`"/>
    </many-to-one>
    <many-to-one name="table" class="Database.Table">
        <column name="`table`" not-null="true"/>
    </many-to-one>
</class>

I also set up some properties in hibernate.cfg.xml:

<property name="hibernate.jdbc.batch_size">40</property>
<property name="hibernate.cache.use_second_level_cache">false</property>

Unfortunately, the insertion of 150 rows takes about 30 seconds, which is very slow. I have read that setting generator=identity may disable batch inserting completely. However, if I remove the generator line, I end up with getting Duplicate key error. I wonder if I could send null as my id, so MySQL will do the job.

What is the best way to optimise the query? Thanks.

Upvotes: 0

Views: 1733

Answers (1)

yozh
yozh

Reputation: 1223

As answered in this SO question identity will indeed not work for batch. For MySQL you end up either generating ids in an application (f.e. with generator assigned or uuid - Hibernate-specific) or employing JPA-compliant table generator.

Example of using table generator:

<table-generator name="EMP_GEN"
        table="GENERATOR_TABLE"
        pk-column-name="key"
        value-column-name="hi"
        pk-column-value="EMP"
        allocation-size="20"/>

See details on http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#mapping-declaration-id. There is also a description of what parameter mean on Java EE page.

Upvotes: 1

Related Questions