Hi,
I have a big problem of performance...
I have two classes: the first one is Profil which has a collection of
Habilitation (may be 500 elements).
When I want to get , by a jdo query, a Profil and all his Habilitation(s),
I've seen in MySql log that there are 1 SQL query to get Profil, and 500 SQL
queries (The criteria is on idHabilitation ) to get the 500 habilitations
...
SELECT * FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.idHabilitation = 1;
SELECT * FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.idHabilitation = 2;
....
Is there something wrong in my classes?
I thought it was due to the lazy mode, so I took it off.
But the same thing seems to occur.
Is there a way to get all the habilitations with only one SQL query (The
criteria should be on idProfil) like this :
SELECT * FROM SCA_PROF_HABILITATION WHERE SCA_PROF_HABILITATION.idProfil =
SCA_PROFIL.idProfil;
Here are the classes Pofil, and Habilitation.
Thanks for your help.
Here's the mapping file:
<class name="fr.cnam.scapin.metier.donnees.agents.profils.Profil"
key-generator="MAX" identity="idProfil">
<map-to table="SCA_PROFIL" />
<field name="idProfil" type="integer" required="true">
<sql type="integer" name="PRO_ID" />
<bind-xml name="idProfil" />
</field>
<field name="organisme" type="string">
<sql type="char" name="PRO_ORGANISME" />
<bind-xml name="organisme" />
</field>
<field name="mnemonique" type="string">
<sql type="char" name="PRO_NOM_PROFIL" />
<bind-xml name="mnemonique" />
</field>
<field name="numVersion" type="integer">
<sql type="integer" name="PRO_NUM_VERSION" />
<bind-xml name="numVersion" />
</field>
<field name="resume" type="string">
<sql type="char" name="PRO_RESUME" />
<bind-xml name="resume" />
</field>
<field name="description" type="string">
<sql type="char" name="PRO_DESCRIPTION" />
<bind-xml name="description" />
</field>
<field name="organismePere" type="string">
<sql type="char" name="PRO_ORGANISME_PERE" />
<bind-xml name="organismePere" />
</field>
<field name="nomProfilPere" type="string">
<sql type="char" name="PRO_NOM_PROFIL_PERE" />
<bind-xml name="nomProfilPere" />
</field>
<field name="listeHabilitation" collection="collection"
type="fr.cnam.scapin.metier.donnees.agents.profils.Habilitation">
<sql many-key="PRO_ID" />
<bind-xml name="listeHabilitation" />
</field>
</class>
<class name="fr.cnam.scapin.metier.donnees.agents.profils.Habilitation"
depends="fr.cnam.scapin.metier.donnees.agents.profils.Profil"
key-generator="MAX" identity="idHabilitation">
<map-to table="SCA_PROF_HABILITATION" />
<field name="idHabilitation" type="integer" required="true">
<sql type="integer" name="HAB_ID" />
<bind-xml name="idHabilitation" />
</field>
<field name="profil"
type="fr.cnam.scapin.metier.donnees.agents.profils.Profil">
<sql name="PRO_ID" />
<bind-xml name="profil" />
</field>
<field name="action" type="string">
<sql type="char" name="HAB_ACTION" />
<bind-xml name="action" />
</field>
<field name="droitAssocie" type="string">
<sql type="char" name="HAB_DROIT" />
<bind-xml name="droitAssocie" />
</field>
</class>
Here'a a little part of what Jdo is doing when I'm querying a Profil and all
his habilitations :
Requete Jdo :
getProfils() --> SELECT profil FROM
fr.cnam.scapin.metier.donnees.agents.profils.Profil profil WHERE organisme=
$1 and mnemonique= $2 order by organisme, mnemonique, numVersion DESC
getProfils() --> param = null - paramProfil = Technicien - paramOrganisme =
National
org.exolab.castor.jdo.engine.SQLEngine - Create SQL: SELECT
SCA_PROFIL.PRO_ID,SCA_PROFIL.PRO_ORGANISME,SCA_PROFIL.PRO_NOM_PROFIL,SCA
_PROFIL.PRO_NUM_VERSION,SCA_PROFIL.PRO_RESUME,SCA_PROFIL.PRO_DESCRIPTION
,SCA_PROFIL.PRO_ORGANISME_PERE,SCA_PROFIL.PRO_NOM_PROFIL_PERE,SCA_PROF_H
ABILITATION.HAB_ID FROM SCA_PROFIL LEFT OUTER JOIN SCA_PROF_HABILITATION ON
SCA_PROFIL.PRO_ID=SCA_PROF_HABILITATION.PRO_ID WHERE
(SCA_PROFIL.PRO_ORGANISME = ?1 and SCA_PROFIL.PRO_NOM_PROFIL = ?2) ORDER BY
SCA_PROFIL.PRO_ORGANISME , SCA_PROFIL.PRO_NOM_PROFIL ,
SCA_PROFIL.PRO_NUM_VERSION DESC
org.exolab.castor.jdo.engine.SQLEngine - Executing the following SQL: SELECT
SCA_PROFIL.PRO_ID,SCA_PROFIL.PRO_ORGANISME,SCA_PROFIL.PRO_NOM_PROFIL,SCA
_PROFIL.PRO_NUM_VERSION,SCA_PROFIL.PRO_RESUME,SCA_PROFIL.PRO_DESCRIPTION
,SCA_PROFIL.PRO_ORGANISME_PERE,SCA_PROFIL.PRO_NOM_PROFIL_PERE,SCA_PROF_H
ABILITATION.HAB_ID FROM SCA_PROFIL LEFT OUTER JOIN SCA_PROF_HABILITATION ON
SCA_PROFIL.PRO_ID=SCA_PROF_HABILITATION.PRO_ID WHERE
(SCA_PROFIL.PRO_ORGANISME = ? and SCA_PROFIL.PRO_NOM_PROFIL = ?) ORDER BY
SCA_PROFIL.PRO_ORGANISME , SCA_PROFIL.PRO_NOM_PROFIL ,
SCA_PROFIL.PRO_NUM_VERSION DESC
org.exolab.castor.jdo.engine.SQLEngine - Loading class:
fr.cnam.scapin.metier.donnees.agents.profils.Habilitation using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�412 org.exolab.castor.jdo.engine.SQLEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�413 org.exolab.castor.jdo.engine.SQLEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�414 org.exolab.castor.jdo.engine.SQLEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�415 org.exolab.castor.jdo.engine.SQLEngine - Loading class:
fr.cnam.scapin.metier.donnees.agents.profils.Habilitation using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�416 org.exolab.castor.jdo.engine.SQLEngine - Loading class:
fr.cnam.scapin.metier.donnees.agents.profils.Habilitation using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�417 org.exolab.castor.jdo.engine.SQLEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�418 org.exolab.castor.jdo.engine.SQLEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
using SQL: SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_H
ABILITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=? org.exolab.castor.persist.LockEngine -
Loading class: fr.cnam.scapin.metier.donnees.agents.profils.Habilitation
with id: 5�419 . . .
___________________
CREDI RA
Sylvie Palluel
[EMAIL PROTECTED]
___________________
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
