DynamicInsert/Update: Group similar statements (per entity) in order to optimize bulk processing

Description

When enabling DynamicInserts/Update, Hibernate will generate different SQL statements according to the columns not null / not modified.

Whereas the hibernate.order_inserts property sorts the inserts/updates by entity, it does not group similar statement, thereby leading to performance decrease because of statement switching and causing increased amount og round trips to DB.

We suggest also having an option of ordering (grouping) the statements which, for one entity, affect the same columns, so they can be executed in bulk.

eg:
session.save( new Event( "1", new Date() ));
session.save( new Event( new Date() ));
session.save( new Event( "2", new Date() ));
session.save( new Event( new Date() ));
session.save( new Event( "3", new Date() ));
session.save( new Event( new Date() ));
session.save( new Event( "4", new Date() ));
session.save( new Event( new Date() ));

leads to
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)

which required 8 round trips to DB.

In case similar statements were ordered / grouped, eg as in:
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, title, id) values (?, ?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)
Hibernate: insert into EVENTS (EVENT_DATE, id) values (?, ?)

This would lead to only 2 round trips to DB as the statements can be executed in batch.

=> Same is true for the updates.

Environment

4.3.5 / H2 / Oracle 11G r2

Status

Assignee

Unassigned

Reporter

Birgen Geelen

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

4.3.5

Priority

Minor