I'm facing some performance issues when batch inserting millions of records into Oracle.

So, I run some tests trying to understanding the best method to create the entities.

I'm curious why it is faster using hibernate generator than not using.

The scenarios:

1. (auditAuto): With envers and Hibernate Generator (uuid2)
2. (auditRandom): With envers and UUID.randomUUID().toString() as id
3. (auditRandomOptLock): With envers and UUID.randomUUID().toString() as id, and Spring Boot @Version for optimistic lock
4. (noAuditAuto): Without envers and Hibernate Generator (uuid2)
5. (noAuditRandom): Without envers and UUID.randomUUID().toString() as id
6. (noAuditRandomOptLock): Without envers and UUID.randomUUID().toString() as id, and Spring Boot @Version for optimistic lock

All entities are simples entities with two columns.

entity: id varchar2(255) primary key, name varchar2(255)

The results for 1.000.000 records:

Creating the collections:

1. AuditAuto :: 192ms
2. AuditRandom :: 1125ms
3. AuditRandomOptLock :: 646ms
4. NoAuditAuto :: 16ms
5. NoAuditRandom :: 1694ms
6. NoAuditRandomOptLock :: 841ms

Here, everything was as expected: Creating the entities collections was slower when using UUID.randomUUID() than not using for obvious reasons.

Saving the collections (each one in their own transaction)

1. auditAutoRepository :: 216847ms
2. auditRandomRepository :: 338461ms
3. auditRandomOptLockRepository :: 370750ms
4. noAuditAutoRepository :: 88616ms
5. noAuditRandomRepository :: 155202ms
6. noAuditRandomOptLockRepository :: 176575ms

Here is what I don't get. Ok... using envers is slower than not using (that was expected) (1, 2 and 3). But I don't get why using hibernate generator is faster at save (and commit).

As the id was already set at entities when using UUID.randomUUID() and, with generator, was not, I was expecting that the scenarios 5 and 6 to be faster than 4. But was not. Maybe because the amount of data transfered?

I run the scearios many times and I got consistent results.

I would like to read your opnion about it.

(Guys, the question is not about business rules if I need envers or not. It's a techinal curiosity about how things are handled in batch inserts).

Thank you.

Environment: Oracle 12c | Oracle JDK 1.8 | Spring Boot 1.5.4 | Hibernate 5.4

CodePudding user response：

The answers I found:

Depending the way you do the batch insert, hibernate will not only do the insert, but execute others SQL.

Regarding my original question about me expecting 5 and 6 to be faster. I was right and wrong.

The scenario 5 was not supose to be faster, because it does a SELECT before the insert.

The scenario 6 was supose to be faster because it simple execute 1 insert and nothing else. Then problema was that, for the Optimistic Lock I was using @Version from spring and this one does not tell hibernate if the entity is or isn't persited. So, as scenario 5, it does execute a SELECT before.

The @Version to do it right should be imported from javax.persistence.

Bellow the queires hibernate executes for each scenarios and the right time spent (for 1 million records) using the correct @Version.

Thanks.

1. (auditAuto): With envers and Hibernate Generator (uuid2)

insert into teste.teste_audit_auto (name, id) values (?, ?)
select teste.hibernate_sequence.nextval from dual
insert into teste.version_control (timestamp, username, id) values (?, ?, ?)
insert into teste.teste_audit_auto_aud (revtype, name, id, rev) values (?, ?, ?, ?)

2. (auditRandom): With envers and UUID.randomUUID().toString() as id

select auditrando0_.id as id1_2_0_, auditrando0_.name as name2_2_0_ from teste.teste_audit_random auditrando0_ where auditrando0_.id=?
insert into teste.teste_audit_random (name, id) values (?, ?)
select teste.hibernate_sequence.nextval from dual
insert into teste.version_control (timestamp, username, id) values (?, ?, ?)
insert into teste.teste_audit_random_aud (revtype, name, id, rev) values (?, ?, ?, ?)

3. (auditRandomOptLock): With envers and UUID.randomUUID().toString() as id, and Spring Boot @Version for optimistic lock

insert into teste.teste_audit_random_opt_lock (name, opt_lock, id) values (?, ?, ?)
select teste.hibernate_sequence.nextval from dual
insert into teste.version_control (timestamp, username, id) values (?, ?, ?)
insert into teste.teste_audit_random_opt_lock_aud (revtype, name, id, rev) values (?, ?, ?, ?)

4. (noAuditAuto): Without envers and Hibernate Generator (uuid2)

insert into teste.teste_noaudit_auto (name, id) values (?, ?)

5. (noAuditRandom): Without envers and UUID.randomUUID().toString() as id

select noauditran0_.id as id1_7_0_, noauditran0_.name as name2_7_0_ from teste.teste_noaudit_random noauditran0_ where noauditran0_.id=?
insert into teste.teste_noaudit_random (name, id) values (?, ?)

6. (noAuditRandomOptLock): Without envers and UUID.randomUUID().toString() as id, and Spring Boot @Version for optimistic lock

insert into teste.teste_noaudit_random_opt_lock (name, opt_lock, id) values (?, ?, ?)

Creating the collections:

1. AuditAuto :: 174ms
2. AuditRandom :: 902ms
3. AuditRandomOptLock :: 778ms
4. NoAuditAuto :: 18ms
5. NoAuditRandom :: 1584ms
6. NoAuditRandomOptLock :: 808ms

Saving the collections (each one in their own transaction)

1. auditAuto :: 355222ms
2. auditRandom :: 350674ms
3. auditRandomOptLock :: 385234ms
4. noAuditAuto :: 114813ms
5. noAuditRandom :: 119246ms
6. noAuditRandomOptLock :: 74858ms