[FIXED] HIbernate SQL show_sql uses lots of left joins vs individual queries

Issue

We are upgrading a legacy application from using xml files to annotations with Hibernate 5 (5.6.9 - I'm not sure what the old version of Hibernate was). The new application is running much slower than the old. I logged the sql queries between the old and new and the old application would make more queries to single tables while the new application is making fewer queries with lots of left joins. From what I can tell the annotations for the relationships seem to be equivalent to the old xml settings.

Is there a way to control how Hibernate writes its sql statements?

Solution

The explanation for what we were seeing was the fetch mode (vs fetch type: eager vs lazy). It seems the default fetch mode of the old version of Hibernate was SELECT but the default for Eager joins in Hibernate 5 is JOIN. To change you can add a @FETCH annotation. This didn't fix all of our slowness issues but it explained the difference in generated sql.

@Fetch(FetchMode.SELECT)

Some helpful links

• https://www.baeldung.com/hibernate-fetchmode

• https://docs.jboss.org/hibernate/stable/core.old/reference/en/html/performance.html#performance-fetching

• https://stackoverflow.com/a/43755971/814299

Answered By - jessieloo
Answer Checked By - Mildred Charles (JavaFixing Admin)