Enhance defaults in Hibernate ORM

[yrodiere]

Description

In particular:

• Set hibernate.jdbc.batch_size (quarkus.hibernate-orm.jdbc.statement-batch-size) by default, e.g. to 200
• Enable parameter padding by default
• More?

See also https://hibernate.zulipchat.com/#narrow/stream/132094-hibernate-orm-dev/topic/JDBC.20statement.20batching

Suggestions from comments:

• How about enabling insert and update ordering by default?

• With ORM 6.6 you should IMO also enable hibernate.unowned_association_transient_check by default.

Note: if this one is what I think it is, it breaks applications. We might want to wait for ORM 7 (which sets it by default, I think).

• Enabling hibernate.query.hql.portable_integer_division might also make sense.

Implementation ideas

No response

[quarkus-bot[bot]]

/cc @gsmet (hibernate-orm)

[beikov]

How about enabling insert and update ordering by default? With ORM 6.6 you should IMO also enable hibernate.unowned_association_transient_check by default. Enabling hibernate.query.hql.portable_integer_division might also make sense.

[gsmet]

Set hibernate.jdbc.batch_size (quarkus.hibernate-orm.jdbc.statement-batch-size) by default, e.g. to 200 Enable parameter padding by default

It might be a stupid question but if you combine the two, what happens when you get a list of 10 elements? You execute a query with 200 parameters? Or there's something clever going on?

Asking because we would need to make sure that these queries are not suboptimal from a query plan POV.

[yrodiere]

How about enabling insert and update ordering by default?

order_updates is already enabled by default, but IIRC we had to avoid order_inserts due to bugs, I need to find the reports...

With ORM 6.6 you should IMO also enable hibernate.unowned_association_transient_check by default.

That one is going to be a lot of fun... I wonder if we'd want to wait for 7 instead :/

Enabling hibernate.query.hql.portable_integer_division might also make sense.

Ok let's look into that.

[Sanne]

How about enabling insert and update ordering by default?

+1 Bear in mind order_inserts was enabled by default originally but had to be disabled because of some unresolved issues in ORM. order_updates is already enabled.

[yrodiere]

Set hibernate.jdbc.batch_size (quarkus.hibernate-orm.jdbc.statement-batch-size) by default, e.g. to 200 Enable parameter padding by default

It might be a stupid question but if you combine the two, what happens when you get a list of 10 elements? You execute a query with 200 parameters? Or there's something clever going on?

Asking because we would need to make sure that these queries are not suboptimal from a query plan POV.

I don't understand how these two options could interact... ? Nor what the relation is with a list of 10 elements.

Statement batch size is about using a single JDBC Statement for multiple SQL statements that are identical except for their parameters. It essentially sends multiple statements in a single network packet.

"in clause" parameter padding is... something I'd expect to be used mostly in queries? I'd expect it to affect only generated SQL for a given statement.

[gsmet]

I don't understand how these two options could interact... ? Nor what the relation is with a list of 10 elements.

Ah, yes, you're right, long day...

As for padding, I don't remember the specifics but I remember at some point, we had some negative effects with suboptimal query plans. But that might be long gone. Typically, if you pad with null, I remember some databases weren't exactly happy and were using suboptimal plans.

But that's several years old memory so it might not be an issue anymore (either because we adjusted the impl or because databases evolved).

[yrodiere]

How about enabling insert and update ordering by default?

order_updates is already enabled by default, but IIRC we had to avoid order_inserts due to bugs, I need to find the reports...

We'll need to dig more, but here's one lead: https://github.com/quarkusio/quarkus/issues/19129#issuecomment-891088427 . No source to back the claims though :/