Spring Data JPA, query too slow, too many subquery instead join

Question

I have a REST service that calls a simple findAll with Spring Data JPA. The problem is that in the console are logged a lot of query, so instead of a join, I suppose the Spring Data do a single query for every row of my main class named Dis.

I have a main entity that must fetch only some data annotated with @JsonView:

@Entity
@NamedQuery(name="Dis.findAll", query="SELECT d FROM Dis d")
public class Dis implements Serializable {
    private static final long serialVersionUID = 1L;

    @JsonView(View.Summary.class)
    @Id
    @Column(name="id_dis")
    private Integer idDis;

    private byte[] logo;

    @JsonView(View.Summary.class)
    private String text;

    @JsonView(View.Summary.class)
    @JsonManagedReference
    //bi-directional many-to-one association to TipoValidazione
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="validazione")
    private TipoValidazione tipoValidazione;

    //bi-directional many-to-one association to DisComune
    @OneToMany(mappedBy="dis")
    private List<DisComune> disComunes;

    //bi-directional many-to-one association to PdfDis
    @OneToMany(mappedBy="dis")
    private List<PdfDis> pdfDiss;

    @JsonView(View.Summary.class)
    @JsonManagedReference
    //bi-directional many-to-one association to Prodotto
    @OneToOne(mappedBy="dis", fetch=FetchType.EAGER)
    private Prodotto prodotto;

    //getter and setter
}

I set FetchType.EAGER (but I suppose I can avoid this because for this kind of relationship is the default) because I need always the two objects. Below is the Prodotto item that Dis must fetch in my findAll:

@Entity
@NamedQuery(name="Prodotto.findAll", query="SELECT p FROM Prodotto p")
public class Prodotto implements Serializable {
    private static final long serialVersionUID = 1L;

    @JsonView(View.Summary.class)
    @Id
    @Column(name="id_prodotto")
    private Integer idProdotto;

    @JsonView(View.Summary.class)
    @Column(name="cod_prodotto")
    private String codProdotto;

    @Column(name="id_classe")
    private Integer idClasse;


    @JsonBackReference
    //bi-directional many-to-one association to Dis
    @OneToOne
    @JoinColumn(name="id_dis")
    private Dis dis;

    //getter and setter
}

and similarly the class TipoValidazione:

@Entity
@Table(name="tipo_validazione")
@NamedQuery(name="TipoValidazione.findAll", query="SELECT t FROM TipoValidazione t")
public class TipoValidazione implements Serializable {
    private static final long serialVersionUID = 1L;

    @JsonView(View.Summary.class)
    @Id
    private String validazione;

    @JsonBackReference
    //bi-directional many-to-one association to Dis
    @OneToMany(mappedBy="tipoValidazione")
    private List<Dis> diss;

    //getter and setter
}

The simple findAll have the problem that spawns a lot subquery (I suppose one or two for every row, instead to do a join), so it works, but in a really slow and not usable.

I tried to do an interface-based projection, using a findBy() with no parameter, but the problem above is still present (and strangely the interface related to Dis, return two null value for the interfaces Prodotto and TipoValidazione).

How I can solve? I know I could write a custom nativeQuery but I was trying to avoid this solution.

Accollativo · Accepted Answer · 2019-11-21 11:21:10Z

5

What you want to do is a JOIN FETCH with your query (there are several ways to implement this) so that hibernate will fetch the child table rows along with the parent entity instead of doing the default, which is a n + 1query.

Here are the two ways I know how to do it:

Implement your own query to include the JOIN FETCH

    @Override
    @Query(value = "SELECT distinct d" +
            "         FROM Dis d " +
            "              JOIN FETCH d.disComunes ")
    List<Dis> findAll();

Create a @NamedEntityGraph where you can apply a query hint to all queries you select In Entity:

@Entity
@NamedEntityGraph(name = "Dis.detail", 
attributeNodes = {@NamedAttributeNode("tipoValidazione"),
        @NamedAttributeNode("prodotto")})
@NamedQuery(name="Dis.findAll", query="SELECT d FROM Dis d")
public class Dis implements Serializable {

In Repository:

@Override
@EntityGraph(value = "Dis.detail" , type=EntityGraphType.FETCH)
List<Dis> findAll();

edited Nov 21, 2019 at 11:21

Accollativo

1,5794 gold badges35 silver badges59 bronze badges

answered Nov 19, 2019 at 16:54

Chris Savory

2,7551 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Accollativo Over a year ago

I added the annotation @Fetch(FetchMode.JOIN) on the 2 relationship (tipoValidazione and prodotto) in the class Dis, but I still get n+1 query.

Collectives™ on Stack Overflow

Spring Data JPA, query too slow, too many subquery instead join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related