Dear all,
I just made a 1-n relation between entities Contrat and Mouvement put in work,
in a context of pagination.
HQL Request code:
Code:
String lQueryString =
"SELECT c FROM Contrat c JOIN FETCH c.client client WHERE c.client.id = :clientId ORDER BY c ASC";
Query lQuery = getEntityManager().createQuery(lQueryString.toString());
lQuery.setParameter("clientId", aClientId);
lQuery.setFirstResult(aFirstResult);
lQuery.setMaxResults(aResultCount);
List<Contrat> lListe = lQuery.getResultList();
To avoid n+1 problem during jsf rendering on my association, i used a subselect fetching via annotation
Code:
@OneToMany(cascade = CascadeType.ALL, mappedBy = "contrat")
//@BatchSize(size=10)
@Fetch(value=FetchMode.SUBSELECT)
@org.hibernate.annotations.Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
private List<Mouvement> mouvements = new ArrayList<Mouvement>();
Now when Hibernate executes sql, i'm surprised to see that
the subselect executes on all the contract and can lead to a serious performance issue if there are to many of them...
generated sql:
Code:
select mouvements0_.COCONT as COCONT2_,
mouvements0_.CESEQO as CESEQO2_,
mouvements0_.CESEQO as CESEQO12_1_,
mouvements0_.COCONT as COCONT12_1_,
mouvements0_.DATCRE as DATCRE12_1_,
mouvements0_.DATEFF as DATEFF12_1_,
mouvements0_.LIBMVT as LIBMVT12_1_,
mouvements0_.AMOUNT as AMOUNT12_1_,
mouvements0_.COPERI as COPERI12_1_
from SPHMMVT mouvements0_
where mouvements0_.COCONT in (
select
contrat0_.CESEQO
from
SPHMCONTRAT contrat0_
inner join
SPHMCLIENT client1_
on contrat0_.CODCLI=client1_.COPERS
where
contrat0_.CODCLI=534847
)
My question is:
why don't take the rownum in account in the subselect query also, like this for example:
Code:
select mouvements0_.COCONT as COCONT2_,
mouvements0_.CESEQO as CESEQO2_,
mouvements0_.CESEQO as CESEQO12_1_,
mouvements0_.COCONT as COCONT12_1_,
mouvements0_.DATCRE as DATCRE12_1_,
mouvements0_.DATEFF as DATEFF12_1_,
mouvements0_.LIBMVT as LIBMVT12_1_,
mouvements0_.AMOUNT as AMOUNT12_1_,
mouvements0_.COPERI as COPERI12_1_
from SPHMMVT mouvements0_
where mouvements0_.COCONT in (
SELECT row_1.CESEQO
FROM (SELECT row_.*, ROWNUM rownum_
FROM ( select contrat0_.CESEQO
from SPHMCONTRAT contrat0_
inner join SPHMCLIENT client1_
on contrat0_.CODCLI=client1_.COPERS
where contrat0_.CODCLI=534847
order by contrat0_.CESEQO ASC) row_
WHERE ROWNUM <= 2) row_1
WHERE rownum_ > 0
)
I'm sure there's a good reason why you didn't do it, but i can't see which..
Thanks in advance for your help,
C.
Login
FAQ
Search