Needless Left Outer Join Created on FK's using JPA/Hibernate

Question

I have an Entity such as:

@Entity
class Brand {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(nullable = false, columnDefinition = "BIGINT")
  private Long id;

  @NotNull(message = NOT_NULL_CODE)
  @Size(min = 5, max = 150, message = SIZE_CODE)
  @Column(nullable = false, length = 150)
  private String name;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "organization_id", nullable = false)
  @JsonBackReference("organization-brands")
  private Organization organization;

  // getters and setters
}

My repository has the following defined:

Page<Brand> findAllByOrganization(Organization organization, Pageable pageable);

This results in the following query:

select
        brand0_.id as id1_1_,
        brand0_.name as name2_1_,
        brand0_.organization_id as organiza3_1_
    from
        brand brand0_ 
    left outer join
        organization organizati1_ 
            on brand0_.organization_id=organizati1_.id 
    where
        organizati1_.id=? limit ?

I'm wondering why there is a left outer join created? I thought maybe it was because the organization_id was part of the select so I created a DTO Projection that only included the name and id columns, but that still included the left outer join. I found this bug report filed but there's been zero movement on it.

The reason this is concerning for me is because the left outer join adds, on average, 200ms to the query. I haven't done any benchmarking to determine if this grows exponentially with more rows of data or not. But with my current dataset, it's 200ms. Yikes.

Probably because Hibernate/JPA need the reference to get the Organization object. Cause your Brand object has a Organization object not a long fk — Marcos Vasconcelos, Dec 01 '17 at 17:52
This is also the case for us, and we are using QueryDSL library in order to have more control over jpa queries, especially when running queries returning small projections of DTOs and not full Entities. — lzagkaretos, Dec 01 '17 at 17:55
Possibly related to https://stackoverflow.com/a/17987718/1356423. Try `@ManyToOne(fetch = FetchType.LAZY, optional = false)`. Without that I would guess Hibernate has to check the table to see whether to set a proxy or null. — Alan Hay, Dec 01 '17 at 18:19
@MarcosVasconcelos I'm pretty sure that is probably the case for quering the Entity. But using the DTO Projection, I'm not even asking for the Organization. It's not in the select, and it is still adding that join. — Gregg, Dec 01 '17 at 18:19
@AlanHay `optional=false` did the trick! Post that and I'll accept the answer. And now to go read docs on that... — Gregg, Dec 01 '17 at 18:23

score 1 · Accepted Answer · answered Dec 01 '17 at 18:28

This is most likely related to:

http://stackoverflow.com/a/17987718/1356423

From the presence of your nullable = false on the column definition it looks like the relationship is non-optional so try annotating as @ManyToOne(fetch = FetchType.LAZY, optional = false)

Without theoptional = false Hibernate has to check the association table to see whether to set either a proxy or null for the associated Organisation: hence the join.

score 0 · Answer 2 · answered Mar 15 '18 at 01:03

0

assuming Organization class PK is named as id then

Page<Brand> findAllByOrganizationId(String organizationId, Pageable pageable);

should do what you want

answered Mar 15 '18 at 01:03

diarmuid

123
7

Needless Left Outer Join Created on FK's using JPA/Hibernate

2 Answers2