LEFT JOIN FETCH ise tüm doktor'ları yükler, varsa randevularını da yükler
Not : N + 1 Select problem ile ilgili çözümler şöyle
Perhaps you are facing an N+1 select issue* With Hibernate, you may fix it by using JOIN FETCHor LEFT JOIN FETCHor FetchType.LAZYor ...* With Spring Data JPA, you may fix it by adding @EntityGraph(attributePaths = { "..." })on repository method: https://docs.spring.io/spring-data/jpa/docs/current/reference/ht
Özet
SpringBoot kullanıyorsak Repository sınıfına @Query yazarız. Şöyle yaparız
public interface ZooRepository extends JpaRepository<Zoo, Long> {
@Query("FROM Zoo z LEFT JOIN FETCH z.animals")
List<Zoo> findAllWithAnimalsJoined();
}
Örnek - 2 Nesne İlişkisi
Şöyle yaparız
SELECT d FROM Doctor d
LEFT JOIN FETCH d.appointments
Açıklaması şöyle
If you’re willing to fetch the full set of Doctors including the ones who do not have any Appointments it’s best to use left join fetch query.
Üretilen SQL şöyledir
SELECT doctor0_.id as id1_1_0_,appointmen1_.id as id1_0_1_,doctor0_.name as name2_1_0_,appointmen1_.appointmentTime as appointm2_0_1_,appointmen1_.doctor_id as doctor_i3_0_1_,appointmen1_.doctor_id as doctor_i3_0_0__,appointmen1_.id as id1_0_0__FROM Doctor doctor0_INNER JOINAppointment appointmen1_ on doctor0_.id=appointmen1_.doctor_id
Örnek
Elimizde şöyle bir kod olsun.
@Entity
public class Item {
@Id
@GeneratedValue
private Long id;
...
@ManyToOne
private Basket basket;
}
Bu koda ait elimizde şöyle bir repository olsun.
public interface ItemRepository extends CrudRepository<Item, Long> {
}
@ManyToOne EAGER olmasına rağmen N + 1 Select Problem var. Açıklaması şöyle
Now, when you use findAll() it is equivalent to firing a JPQL query like entityManager.createQuery(...) and it by default loads the items first and subsequently for each item it loads the basket entity and causing N+1 problem.
Üretilen SQL şöyle
Hibernate: select item0_.id as ... from item item0_Hibernate: select basket0_.id as ... where basket0_.id=?Hibernate: select basket0_.id as ... where basket0_.id=?Hibernate: select basket0_.id as ... where basket0_.id=?
Repository şöyle olsun
public interface ItemRepository extends CrudRepository<Item, Long> {
@Override
@Query("select item from Item item left join fetch item.basket")
Iterable<Item> findAll();
}
Bu sefer üretilen SQL şöyle
Hibernate: select item0_.id as id1_1_0_, basket1_.id as id1_0_1_, item0_.basket_id as basket_i3_1_0_,...from item item0_ left outer join basket basket1_ on item0_.basket_id=basket1_.id
Örnek - 2 Nesne İlişkisi
Elimizde şöyle bir kod olsun.
@Entity
@Table(name="users")
public class User {
...
@OneToMany(mappedBy="user")
private List<Comment> comments;
}
Elimizde şöyle bir kod olsun.@Entity
@Table(name="comments")
public class Comment {
...
@ManyToOne
@JoinColumn(name="user_id")
private User user;
}
Şöyle yaparız.TypedQuery<User> query = entityManager.createQuery(
"SELECT e from User e left join fetch e.comments", User.class);
Örnek - 4 Nesne İlişkisi
Şeklen şöyle
Şöyle yaparız
public List<Country> getAll() {
List<Country> countries = em.createQuery(
"SELECT c FROM Country c left join c.authors a "
+ "left join a.posts p left join p.likes l"
+ " order by c.COUNTRY_NAME,a.AUTHOR_NAME,p.POST_NAME,l.AUTHOR_NAME")
.setHint("eclipselink.left-join-fetch", "c.authors.posts.likes")
.setHint("eclipselink.read-only", true)
.getResultList();
countries.stream().flatMap(c -> c.getAuthors().stream())
.flatMap(a -> a.getPosts().stream()) // all posts
.forEach(p -> p.removePostsInFans());
return countries;
}
çıklaması şöyle. Burada JPQL ile Likes için Left Join yazılsa bile, Left Join Fetch yapılıyor
By default JPA executes an individual SQL query for each parent object to load its associations. The EclipseLink-specific query hint eclipselink.left-join-fetch triggers fetch joins along the specified path c.authors.posts.likes. Without the hint, 20 additional queries would be needed to load the tiny data. The hint eclipselink.read-only indicates that the query results should not be registered with the persistence context.
1. LEFT JOIN FETCH İstemiyorsak
Bir diğer çözüm de Hibernate'e mahsus @Batch kullanmak.
2 LEFT JOIN FETCH ve Full Cartesian Product Problemi
LEFT JOIN FETCH özellikle parent nesnede iki tane ilişki varsa problem olabiliyor.
Örnek
Elimizde şöyle bir JPQL olsun
SELECT a FROM A
LEFT JOIN FETCH a.bs
LEFT JOIN FETCH a.cs
Açıklaması şöyle
then you create full Cartesian product between bs and cs and returning count(a.bs) x count(a.cs) rows in the result set for each a which are read one by one and assembled into the entities of A and their collections of bs and cs.
Burada tek bir SQL çalışmasına rağmen problem büyük bir result set dönülmesi. Bu result set'i hibernate ayıklayıp tek nesne haline getirmek zorunda. Açıklaması şöyle
The query which is generated by eager fetching both associated collections results in a big redundancy in returned data set. For a doctor with 3 appointments and 4 patients, we’re receiving 3 x 4 = 12 rows. For this particular example it’s not a big issue, but imagine a doctor with 200 patients and 50 appointments returning 200 x 50 = 10 000 rows. This data needs to be wired from the database to application and later de-duplicated by Hibernate, which may lead to performance and memory issues.
Çözümler
- Çözümlerden birisi her nesneyi teker teker çekmek. Şöyle yaparız. Tabii ki bu durumda Doctor nesnesinden child nesnelere erişemiyoruz
Doctor doctor = entityManager.find(Doctor.class, 1L);List<Appointment> doctorsAppointments = entityManager
.createQuery("select a from Appointment a where a.doctor.id = :doctorId").setParameter("doctorId", doctor.getId()).getResultList();List<Patient> doctorsPatients = entityManager
.createQuery("select p from Patient p where p.leadingDoctor.id = :doctorId").setParameter("doctorId", doctor.getId()).getResultList();
- Aslında bu madde bir çözüm değil fakat ilgili olduğu için yazdım. Çözüm ilişkiyi EAGER hale getirip Hibernate'e mahsus @org.hibernate.annotations.Fetch(FetchMode.SELECT) kullanmak
Şöyle yaparız. Burada ilişki kod EAGER halinde. Eğer hibernate anotasyonunu kullanmazsak ve entityManager.find() yaparsak kartezyen çarpım problemi oluyor.
Bu anotasyonu eklersek ve entityManager.find() yaparsak hibernate sadece 3 tane sql çalıştırıyor ve kocaman bir kartezyen çarpım sonucu gelmiyor.
@Entitypublic class Doctor {@OneToMany(mappedBy = "doctor", fetch = FetchType.EAGER)@org.hibernate.annotations.Fetch(FetchMode.SELECT)private Set<Appointment> appointments;@OneToMany(mappedBy = "leadingDoctor", fetch = FetchType.EAGER)@org.hibernate.annotations.Fetch(FetchMode.SELECT)private Set<Patient> patients;}
Açıklaması şöyle
Please note that this functionality is only available in Hibernate. Hibernate allows configuring a global setting for eagerly fetched collections. This solution to Cartesian Product Problem allows for each associated collection to be fetched with a separate SQL query. In nature, it’s similar to what we manually did above, but it also has a benefit of loading all related entities directly into Doctor entity.
Çalıştırılan SQL şöyle
selectdoctor0_.id as id1_1_,doctor0_.name as name2_1_from Doctor doctor0_where doctor0_.id=1selectpatients0_.leadingDoctor_id as leadingD3_2_0_,patients0_.id as id1_2_0_,patients0_.id as id1_2_1_,patients0_.leadingDoctor_id as leadingD3_2_1_,patients0_.name as name2_2_1_from Patient patients0_where patients0_.leadingDoctor_id=?selectappointmen0_.doctor_id as doctor_i3_0_0_,appointmen0_.id as id1_0_0_,appointmen0_.id as id1_0_1_,appointmen0_.appointmentTime as appointm2_0_1_,appointmen0_.doctor_id as doctor_i3_0_1_from Appointment appointmen0_where appointmen0_.doctor_id=?
- Diğeri ise SQL kullanmak. Şöyle yaparız. Burada entityManager.find() kullanılmıyor. Aslında bu çözüm de dokümante edilmiş bir çözüm değil. Sadece çalışıyor
Doctor doctor = entityManager.createQuery("select d from Doctor d where d.id=1",
Doctor.class).getSingleResult();
Hiç yorum yok:
Yorum Gönder