I don't understand why a simple JoinTable is this slow with Spring boot compared to SQL JOIN TABLE

Question

I have two really simple JPA Spring Entities : Farm and Coordinates

@Entity
@Getter
@Setter
@Table(name = "farms")
public class FarmEntity {

    @Id
    Long id;

    @Column(name = "categorie")
    String categorie;

    @Column(name = "nom")
    String nom;

    @Column(name = "description")
    String description;
    
    @OneToOne
    @JoinColumn(name = "id")
    Coordinates coordinate;

}


// COORDINATES.JAVA

@Entity
@Getter
@Setter
@Table(name = "coordinates")
public class Coordinates {

    @Id
    Long id;

    @Column(name = "type")
    String type;

    @Column(name = "lat")
    String lat;

    @Column(name = "lon")
    String lon;

}

I have about 16k rows in my postgres database,

When im fetching data from JPA Repository -> 200OK 8.34 s 6.56 MB

When im doing a simple JOIN TABLE in sql

SELECT *
FROM farms f
INNER JOIN coordinates c  ON f.id = c.id;

• 16509 row(s) fetched in 0.037s (0.012s fetch)

Why is there this HUUUGE difference between OneToOne and my JOIN table in SQL ?

Maybe there is a better way to implement this in my entities but i dont find anything even in spring documentations

Thanks for any help or explainations :)

Answer 1

I found out everything.

Hibernates was creating a select statement for each entity in the request, i was loosing performance in transaction time.

To fetch everything in once, you can use the annotation @EntityGraph

I added this in my repository

@EntityGraph(attributePaths = "coordinate")
@Query("SELECT f FROM FarmEntity f INNER JOIN CoordinatesEntity c ON f.id = c.id")
List<FarmEntity> findAllFarms();

From 8s to 300ms 😉