Hibernate performance issue with OneToMany / nullable relationship

Question

We use @OneToMany for our Parent->Child->Child->Child DB relationship:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "THE_ID", nullable = false )
private List<ChildClass> children = new ArrayList<ChildClass>();

We have a scenario with a lot of data (100K inserts) where the performance is atrocious (actually times out) when inserting. With a small amount of data (1K inserts) we are however fine.

So for no good reason I have removed the nullable = false and changed the DB foreign key on the child tables to allow nulls and presto the performance is quite good. Can anyone explain this?

---

Update: Turned on debugging.. With nullable = false there appears to be a huge bottleneck generating the ids for the child tables. We timeout waiting for the ids to generate, with this in the log over and over:

[org.hibernate.event.def.AbstractSaveEventListener] [ ] generated identifier: <743088>, using strategy: org.hibernate.id.IncrementGenerator

We never even get to inserting the data to the DB. We are just stuck on the id gen. Currently we configure Hibernate to generate IDs by looking at the max id values currently in the child table:

@Id
@GeneratedValue(generator = "DummyString")
@GenericGenerator(name = "DummyString", strategy = "increment")
@Column(name = "THE_ID", nullable = false)
private Long id;

Prior to this we were using a DB sequence and saw the same issues.

When we omit nullable = false, we do see these these ID gen statements (108K of them), but they complete in 25 seconds. So why do these statements (literally) take forever with nullable = false?

Answer 1

Voodoo ? Find out how to perform Oracle tracing. Your DBA should help.

With a trace file, you can look at Oracle's wait events. This will tell you what the database was doing when 'performance is atrocious'. It could be waiting on a lock, it could be reading a table....

Once you know why it is slow, the solution often becomes obvious.

I recommend this whitepaper as an instruction to developers on working with databases.

Answer 2

Do you need the Cascade? Are all the child entities being updated on inserts?