Re: [castor-user] RE : [castor-user] How to optimize collection queries ?

Mon, 06 Jun 2005 05:19:15 -0700 

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?



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. 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]
-------------------------------------------------


--

Syscon Ingenieurb�ro f�r
Me�- und Datentechnik GmbH
Ralf Joachim
Raiffeisenstra�e 11
D-72127 Kusterdingen
Germany

Tel.   +49 7071 3690 52
Mobil: +49 173 9630135
Fax    +49 7071 3690 98

Email: [EMAIL PROTECTED]
Web:   www.syscon-world.de

-------------------------------------------------

If you wish to unsubscribe from this list, please 
send an empty message to the following address:


[EMAIL PROTECTED]
-------------------------------------------------























					Reply via email to