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