Greetings, Hibernators!
I have been trying to solve this issue for a few days and have so far been met with little success, so I figured maybe someone could help me here.
I am running PostgreSQL 9.2 with the latest JDBC4 v9.2-1003 drivers. I also have the latest Hibernate 4.2.4 package via Maven.
My hibernate configuration:
Code:
<hibernate-configuration>
<session-factory name="DatabaseSessionFactory">
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres</property>
<property name="hibernate.connection.username">postgres</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.show_sql">true</property>
<property name="hibernate.jdbc.batch_size">50</property>
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.order_inserts">true</property>
<property name="hibernate.order_updates">true</property>
<!--mapping resource="my class hibernate mapping here" /-->
</session-factory>
</hibernate-configuration>
My class Hibernate mapping (the relevant part):
Code:
<hibernate-mapping>
<class name="Foo" table="Foo">
<id name="id" type="int">
<column name="id" />
<generator class="org.hibernate.id.enhanced.SequenceStyleGenerator">
<param name="sequence_name">Foo_Id</param>
<param name="increment_size">50</param>
</generator>
</id>
</class>
<!-- other properties here -->
</hibernate-mapping>
Relevant code:
Code:
Session session = HibernateUtil.getSessionFactory().openSession();
for (Foo foo : foos) session.save(foo);
session.getTransaction().commit();
Originally my table had a SERIAL column (auto-incrementing implicit sequence), with an "identity" generator type. That was quite slow, and every insert was its own individual statement. I have also found through some Google searches that apparently "identity" generator causes Hibernate not to use the batch_size setting.
I then switched to an explicit sequence with increments and cache of 50 values. This cut down on new sequence fetches dramatically, as Hibernate then grabs a new starting number every 50 Ids instead of every one Id. The total time to insert 50k records about halved from this switch. Sadly, the inserts are still done one record at a time.
I have found that apparently MySQL has a JDBC-level switch to batch inserts together, but it seems no other database has that. I have written some code that will generate SQL inserts for me via JPA annotations and reflection, and group records by my defined batch size, but it really feels like reinventing the wheel. The time to insert the same amount of records via this method is approximately 5-7 times less than the time via Hibernate.
Essentially, what I want Hibernate to do is group multiple inserts into my batch-sized chunks:
Code:
insert into Foo (val1, val2) values (1, "one"), (2, "two"), (3, "three")
instead of
Code:
insert into Foo (val1, val2) values (1, "one")
insert into Foo (val1, val2) values (2, "two")
insert into Foo (val1, val2) values (3, "three)
Sadly, though, I cannot find a way to wrangle it to do so. Is there a solution to my problem? Should I just not bother and stick to pure SQL? Any thoughts? On a related note, is there a way to sanitize strings via Hibernate? I need that if I am going to stick with pure SQL as doing my own sanitization is a minefield of mistakes and missed security holes.
Thank you for any advice,
Hibernicus
Login
FAQ
Search