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