0

I have two kotlin Entities, currently with the N+1 query problem for the OneToMany annotation.

@Entity
@NamedEntityGraph(
    name = "ParentEntity.children",
    attributeNodes = [NamedAttributeNode("children")]
)
class ParentEntity(
    @Id
    @GeneratedValue(generator = "uuid2")
    val id: UUID? = null,

    val name: String

): Serializable {
    @Column(name = "secondaryId", columnDefinition = "bigserial", insertable = false, updatable = false)
    @Generated(GenerationTime.INSERT)
    var secondaryId: Long = null


    @OneToMany(mappedBy = "parent", cascade = [CascadeType.ALL], fetch = FetchType.EAGER)
    var children: MutableList<Child> = mutableListOf()
}

Child Entity:

@Entity
class Child(
    @Id
    @GeneratedValue(generator = "uuid2")
    val id: UUID? = null
) : Serializable {

    @ManyToOne()
    @JoinColumn(name = "myForeignKey", referencedColumnName = "secondaryId")
    lateinit var parent: Parent
}

This has the N+1 query problem.. so let's try @NamedGraphEntity!

The problem is, that the @NamedEntityGraph annotation doesn't work.. as the JoinColumn annotation doesn't reference the PrimaryKey of the Parent class.

@Repository
interface ParentRepository : JpaRepository<Parent, UUID> {

    // Has the N+1 Query Problem.
    @EntityGraph("ParentEntity.children", type = EntityGraph.EntityGraphType.LOAD)
    fun findByName(name: String): List<Parent>
}

Question: How do I get the EntityGraph query to work when the JoinColumn is specifying a non-primary column?

What I've tried:

  • Tried referencing the primary id, and it fixes the issue .. but migrating existing foreign keys is a heavy lift to get this optimization.
TheJeff
  • 3,665
  • 34
  • 52
  • Is the N+1 issue occurring on building the Child or building the parent? Seems more likely that it is building the child that is the problem, as there is no way to look up the parent instance in the cache using only the 'secondaryId' column value. If so, you might try making this relationship (Child.parent) lazy so it isn't fetched when building the child. You might need to mark the relationship as optional=false so hibernate can use a proxy there as well. – Chris Mar 18 '22 at 18:50
  • When building the list of parents.. I want it to eagerly fetch all children and do all as one query instead of N+1. Where do I set the (optional=false)? See the ParentRepository.findByName for example of where I'm getting N+1 – TheJeff Mar 18 '22 at 19:12
  • Ref in github/hibernate: https://github.com/spring-projects/spring-data-jpa/issues/2466 https://hibernate.atlassian.net/browse/HHH-15234 – TheJeff Apr 27 '22 at 16:44
  • I was asking if you turn SQL logging on and debug the jpa/ORM code, if it is executing the extra statements building your 'parent' instances, or if it is done building it and now building a referenced 'child' instance and looking up the child's parent (via the myForeignKey value). Referencing non-id fields is not allowed in the JPA spec, and I'm guessing you always had this issue - this is likely a result of having nothing indexed in the object cache it can use to give you an instance of 'parent' that matches the myForeignKey value without looking it up from the database. – Chris Apr 27 '22 at 17:41
  • ie its not an issue with entity graph, but with your model design that has always been there, you've just not noticed the problem until now. File a hibernate bug/feature to see if they can add support to index your parent's secondaryId value (if that is indeed the cause of the N queries), or have some other way to index it internally with this alternate identifier. – Chris Apr 27 '22 at 17:44
  • Chris I’m not sure where to start - the join column allows this.. and its always worked and is allowed.. not sure what to say beyond that. The refernecedColumnId is proof of this and wouldn’t be there if it was always referencing an annotated primary id field, because there would be only one and it wouldn’t need a distinguishing option. And yes, I’ve turned on sql logging and it occurs when you do the query - it is N+1 queries under the one circumstance and only 1 in the other circumstance. I’m not sure how else you think I’m verifying this - its eager not lazy loading. – TheJeff Apr 27 '22 at 18:50
  • 'allowed' means it doesn't throw an error and gives you the entity results you expect, sure. It doesn't mean it will function and operate the way you want internally. JPA does NOT allow this; it is undefined behaviour and Hibernate as a JPA implementation is just allowing through. But they seemed not to fully implement it either. Check what happens if you read in a parent in an EM but don't fetch children, and then fetch via a separate query in the same EM one of the children. Does it issue an SQL for the parent instance when building that child, or pull it from memory? – Chris Apr 27 '22 at 19:08
  • I’m not sure - I can check the resulting queries for the N+1 case if you like. Curious what difference it makes. I’m also still unclear why there would be a referencedColumnId in the JoinColumn annotation if JPA doesn’t support referencing a different column other than the @Id labelled column. Seems evidence of support to me.. and yes by supports - the code works and doesn’t explode I guess – TheJeff Apr 27 '22 at 19:13
  • this has nothing to do with entity graph - what you want is to tell Hibernate to use your "secondaryId" as something to index on, like EclipseLink's https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/a_cacheindex.htm CacheIndex annotation. I don't know if NaturalId works the same, but it might be worth a shot – Chris Apr 27 '22 at 19:19
  • So there is a NaturalId annotation I could put on this other references Id column that might force this to work? Am I reading this right? – TheJeff Apr 27 '22 at 19:20
  • From JPA 3.0, 11.1.25 "Support for referenced columns that are not primary key columns of the referenced table is optional.". As to why; speculation, but it has to do with the burden of caching and maintaining identity to figure out these references. Hibernate is using the DB to do so, which is the cause of your current issue - so it works, and is 'supported' as you get the required result, just not the desired performance. I do not know if NaturalId works and will fix this - but I know the EclipseLink annotation would. Worth a shot – Chris Apr 27 '22 at 19:24

0 Answers0