Java spring hibernate JPA @ManyToAll findAll() slow on large data, many subqueries created

Question

I have 2 classes, Aircraft and Operator, with a one to many relationship from Operator to Aircraft. A findAll() call on the Operator table, which has around 10k rows, from the JPA Repository runs quickly, in just a few seconds, but the findAll() call on the Aircraft table, which has around 65k rows, sometimes takes minutes to execute.

Also, I've noticed that the findAll() on the operators only generates 1 hibernate SELECT query in the console, while on the aircraft it generates 1 SELECT query on the aircraft table and many other SELECT queries on the operators with a where operator0_.id=? constraint, which I don't think is desired.

The findAll() is called from a GET request from a Controller class to the Service class and the result is used to map the Aircraft object to an AircraftDTO object, which also includes the operator name.

How do I fix this problem to make the findAll() query on the Aircraft run quicker?

Note: some class attributes were removed due to lack of relevance in this context

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class Aircraft implements Serializable {
    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "aircraft_sequence"
    )
    @SequenceGenerator(
            name = "aircraft_sequence",
            allocationSize = 1
    )
    @Column(nullable = false, updatable = false)
    private Long id;
    @ManyToOne
    @JoinColumn(name="operator_id", nullable=false)
    private Operator operator;
    private String registration;
}

@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class Operator implements Serializable {
    @Id
    @GeneratedValue(
            strategy = GenerationType.SEQUENCE,
            generator = "operator_sequence"
    )
    @SequenceGenerator(
            name = "operator_sequence",
            allocationSize = 1
    )
    @Column(nullable = false, updatable = false)
    private Long id;
    private String name;
    @OneToMany(fetch = FetchType.LAZY, mappedBy="operator")
    @JsonIgnore
    private Set<Aircraft> aircraft;
}

public interface AircraftRepository extends JpaRepository<Aircraft, Long> {
}

public interface OperatorRepository extends JpaRepository<Operator, Long> {
}

Hibernate: select aircraft0_.id as id1_0_, aircraft0_.operator_id as operator7_0_, aircraft0_.registration as registra5_0_ from aircraft aircraft0_
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
Hibernate: select operator0_.id as id1_2_0_, operator0_.name as name12_2_0_ from operator operator0_ where operator0_.id=?
...and so on

There used to be one, and making a get request would generate an infinite loop, but it was solved by adding `@JsonIgnore` — GoingAround, Sep 28 '22 at 16:55
Odd as I thought Hibernate automatically joins eager OneToOne relationships. What happens when you fetch a single Aircraft? There are multiple options to fix it depending on what you want or need as the results - simplest is to mark the relationship as lazy so it isn't fetched at all, or only when it is needed. An alternative is to fetch join it in your queries- I'm not sure how you are calling findAll. If it is lazy, Set the relationship to be ignored from both sides will prevent your JSON serialization from forcing it being fetched one by one. — Chris, Sep 28 '22 at 16:57
I don't know if it was solved fully, in situations like this one of the objects might need a to be sort of static object that does no joins, the @JsonIgnore is just to handle serializing not the hibernate end of things. — WPW, Sep 28 '22 at 16:59
The `findAll()` is called from a `GET` request from a Controller class to the Service class and the result is used to map the `Aircraft` object to an `AircraftDTO` object, which also includes the operator name — GoingAround, Sep 28 '22 at 17:01
JsonIgnore prevents serialization from accessing the lazy Operator->Aircraft relationship, but is missing from the Aircraft->Operator relationship, which means that for every aircraft, the Operator will need to be fetched AND serialized. Hibernate specifics with this mapping depend on optional settings, but you are better off controlling the query used to fetch Aircraft instances and force the join if you need that data (and put in limits as this will fail and take longer and longer as the data set and the model itself grows). — Chris, Sep 28 '22 at 17:04
This AircraftDTO object looks familiar, are you based in CT? — WPW, Sep 28 '22 at 17:05
@WPW nope, I'm not from the US, maybe it's just a coincidence — GoingAround, Sep 28 '22 at 17:09
Also @JsonIgnore is to ignore serializing the object to json with the jackson library, I still think there is circular reference happening with hibernate. — WPW, Sep 28 '22 at 17:10
@Chris would the force join also solve the infinite loop problem that appears without the `@JsonIgnore` annotation? — GoingAround, Sep 28 '22 at 17:10
Is the code from the US though, that looks like code I have worked on before. Hmmm...... I realize you may not be at liberty to say. — WPW, Sep 28 '22 at 17:11
No: You've got a circular reference and your JSON serializer knows nothing about the JPA mappings, so JsonIgnore is one way to prevent it looping repeatedly over the same objects - there are others but that is a separate topic. You just have to make sure your serialization logic plays nice with what you are fetching with JPA and of course, how you want to fetch it. — Chris, Sep 28 '22 at 17:14
@WPW the code is from an online Java course on Spring + Angular which I then adapted to my needs, but I'll take that as a compliment :D — GoingAround, Sep 28 '22 at 17:16
Well, take one thing for sure, you got a circular reference holding you up. — WPW, Sep 28 '22 at 17:17
@WPW JPA is required to handle bidirectional relationships (circular dependencies) without endlessly querying the DB without any special logic - generally they have a cache, and they perform cache lookups before querying. Complexities with that cache and handling partial objects being built is why I wouldn't write my own JPA provider, and likely why Jackson and other serialization tools don't always handle it well. — Chris, Sep 28 '22 at 17:20
Pst.... Break the reference. Hack off one of the objects you don't need — WPW, Sep 28 '22 at 17:20
The one needed, the ManyToOne, is the one causing or at least involved in the issue. That ManyToOne forces JPA to find the associated Operator for each airport being built based on the the operator_id fk value read in, and that is what apparently is causing these queries. JPA allows for this, but you can fix it as mentioned -make the relationship lazy and prevent your serializer from touching it via JsonIgnore OR have the query used to read in Airports fetch join the operator data, so it is all queried in one statement. — Chris, Sep 28 '22 at 17:24
I tried adding `@JsonIgnore` to the many to one and also made the fetch type lazy, but the issue persists, I'll try to add the fetch join instead — GoingAround, Sep 28 '22 at 17:29
Please add `fetch = FetchType.LAZY` to the parent(aircraft, as well)! — xerx593, Sep 28 '22 at 17:32
I think we all can fall into the trap of dragging around data across the network we don't really need, I am seeing that in the project I am on now, just extra properties sitting there doing nothing, it's too easy to just use the same objects all over the place when you might need a smaller slimmer object with some static data. — WPW, Sep 28 '22 at 17:33
https://stackoverflow.com/questions/2990799/difference-between-fetchtype-lazy-and-eager-in-java-persistence-api — WPW, Sep 28 '22 at 17:36
I managed to solve the problem by overriding the `findAll()` in the aircraft repository and adding a join fetch as @Chris said. Also, I've made the fetch type LAZY on both sides and left the `@JsonIgnore` only in the operator class. Thanks guys! — GoingAround, Sep 28 '22 at 17:43
I think a valid question is do you need the data on both sides, is it being used anywhere in the app? If so stick with your solution, if not slim things down so you are not pulling extra data you don't need, my 2 cents — WPW, Sep 28 '22 at 17:53

score 0 · Accepted Answer · answered Sep 28 '22 at 17:53

0

The problem was that there was a circular dependency which was solved by overriding the findAll() in the AircraftRepository class and including JOIN FETCH

public interface AircraftRepository extends JpaRepository<Aircraft, Long> {
    @Override
    @Query(value = "SELECT distinct a" +
            "         FROM Aircraft a " +
            "              JOIN FETCH a.operator ")
    List<Aircraft> findAll();
}

answered Sep 28 '22 at 17:53

GoingAround

67
1
8

1

You can do even better, query only what you need and return the DTO directly from the query, that saves you from transfering unneeded data (which you are doing now) and just throwing it away. – M. Deinum Sep 29 '22 at 06:54

Java spring hibernate JPA @ManyToAll findAll() slow on large data, many subqueries created

1 Answers1