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

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

CodePudding user response：

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();
}