Most efficient way to insert multiple rows in JPA

Question

I have a one-way parent / child relationship. When I look through the logs, I see that for each child row there is a separate insert request, the equivalent of let say:

insert into childTable(col1, col2) values(val1, val2); insert into childTable(col1, col2) values(val3, val4); 

Wouldn't it be more efficient to insert all rows in a single query? Sort of:

 insert into childTable(col1, col2) values(val1, val2), (val3, val4) 

Is there a way to get JPA to generate multi-line inserts instead of single-line inserts?

Edit: I am currently using cascading inserts, so I am inserting a parent, and child inserts are automatically generated. I would prefer to use this method instead of manually letting you create a huge SQL query, since I think cascading inserts create cleaner code.

I already regularly clean the session to control the size of the L1 cache, so running out of memory is not a problem.

Answer 1

Actually, it’s less efficient to insert all rows in a single query.

First, a few comments:

• The amount of data transferred from the client to the server is the same as one or more insert statements, where “amount of data” means the actual values ​​that you store.
• Hibernate supports batch request processing, so the number of return flights between the client and server can be approximately the same with either one or several insert statements.

Under the covers, Hibernate uses PreparedStatement for every request that it executes on your behalf, and they are cached and reused. And MySQL caches "compile" SQL queries. Without going into details, the underlying technologies are highly optimized for multiple execution of a relatively small number of requests.

If you insert as a single statement, then every time the number of inserted values ​​differs, the new SQL must be compiled and cached (perhaps pushing another request from the cache), which adds overhead. Avoid these overheads when you just use the same SQL every time.

For many reasons, you should use bind variables in your SQL, and Hibernate will do this automatically. If you are doing some custom queries to test the all-in-one time insertion method, you should also use bind variables.

Another consideration is how you generate identifiers. If it is in the identification column in the database, then Hibernate should get back the identifier for each column, which is usually possible only when creating one row. For this reason, a sequence-based identifier generator is preferred for efficiency, with client-side caching of sequence values.

I just noticed your edit: my experience is that Hibernate makes “extra” updates when working with parent type data insertion. I managed to get clean inserts by changing the mapping to have a join table (for example, you would see for many-to-many relationships), although I only had a one-to-one relationship. In my case, it was much faster to make significantly more inserts in three tables and fewer attachments plus updates to two tables. If you are concerned about performance, you should definitely plan some time to configure your Hibernate configuration.