Werner, I'm using Castor 0.9.6.
And I had traces in mysql logs : 1 SQL query to get Profil, and all idHabilitation (left outer join), 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; ... Sylvie. ___________________ CREDI RA Sylvie Palluel [EMAIL PROTECTED] ___________________ > -----Message d'origine----- > De�: Werner Guttmann [mailto:[EMAIL PROTECTED] > Envoy�: lundi 6 juin 2005 12:18 > ��: [email protected] > Objet�: RE: [castor-user] How to optimize collection queries ? > > 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] > ------------------------------------------------- ------------------------------------------------- If you wish to unsubscribe from this list, please send an empty message to the following address: [EMAIL PROTECTED] -------------------------------------------------
