Ralf, Ralf Joachim wrote: > Hi Silvie, > > I have some ideas how to work around your performance problems but > before explaining them I'd like to know a little more about how your > application. Also I want you to do some small tests. > > 1. Does the load behavier change if you use 'vector' or 'arraylist' as > collection types for Profile.listHabilitation instead of 'collection'? > > 2. Does the load behavier change if you remove the depends at > Habilitation.profile? This is exactly what I have been asking myself, i.e. whether it'S the depends here that prevents Castor from not loading everything in one go.
> 3. Which get/set/add methods for listHabilitation have you defined at > Profile and how do they look like? If you have set and add methods > defined, which one is used by castor? > > 4. When you are loading the Profile with its Habilitations do you need > write access or would readonly access be enough? Maybe you can also post > a short snap of that code if that's not to complicated. > > 5. Is castor the only application that access this tables of your > database or are there other systems with concurrent access to this data? > > 6. You have no <cache-type> definition in your mapping. ATM I don't know > which setting is used by castor whenh this is absend. Afaik, count-limited (with a default of either 100 or thirty). > May you are > someone else educate me about that? Is this my intension? > > Regards > Ralf > > > SYLVIE PALLUEL schrieb: > >> 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] >> ------------------------------------------------- > > ------------------------------------------------- If you wish to unsubscribe from this list, please send an empty message to the following address: [EMAIL PROTECTED] -------------------------------------------------
