Efficient SQL Insert with MySQL and OpenJPA

Some applications need a high INSERT throughput. I got this issue and found an interesting way to enhance without effort this parameter using OpenJPA (can be adapted with other providers) and MySQL (not sure about other databases).

First the trivial part: ask OpenJPA to “batch” queries. This can be easily done adding in your persistence.xml:

<property name="openjpa.jdbc.DBDictionary" value="mysql(batchLimit=-1)"/>

-1 means batch all queries you can in the current transaction (if you don’t use transaction maybe specify a constant size).

Now the magic part: MySQL has some optimizations for INSERTs. The idea is to send “batch INSERT” putting all values inline.

INSERT INTO Foo(id, value) VALUES (1, 'foo');
INSERT INTO Foo(id, value) VALUES (2, 'bar');

can be replaced by:

INSERT INTO Foo(id, value) VALUES(1, 'foo'), (2, 'bar');

OpenJPA doesn’t support it by default but a magic property in the MySQL jdbc url allows to activate it: rewriteBatchedStatements.

For instance:

jdbc:mysql://localhost:3306/mydb?rewriteBatchedStatements=true

With these two settings your INSERTs (in the same transaction) will be rewritten/inlined.

As usual check it enhances your performances but in my case it helped.

Advertisement