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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s