Hi Ralf,
1. I did not try to use something else than Collection.
2. the load behavior doesn't change if I remove the depends at
Habilitation.profile. In fact I've removed it.
3. Here are the get/set/add methods for listHabilitation :
public java.util.Collection getListeHabilitation() {
return listeHabilitation;
}
public void setListeHabilitation(java.util.Collection collection) {
listeHabilitation = collection;
}
public void addHabilitation(Habilitation habilitation) {
listeHabilitation.add(habilitation);
}
The mesthods setListeHabilitation and getListeHabilitation are used by
castor.
4. When I'm loading the Profile with its Habilitations I need
sometime a write access (to update this Profil), and more often I should
need only readonly access.
5. castor is the only application that access this tables.
6. Now I have put a cache-type for the 2 classes :
<class name="test.testCollection.Profil" access="shared"
key-generator="MAX" identity="idProfil">
<cache-type type="count-limited" capacity="100" />
<class name="test.testCollection.Habilitation" access="shared"
key-generator="MAX" identity="idHabilitation">
<cache-type type="count-limited" capacity="2000" />
The default seems to be
<cache-type type="count-limited" capacity="30" />
Thanks for your help.
Sylvie.
The Oql query is :
OQL_LES_PROFILS = "SELECT profil FROM test.testCollection.Profil profil
WHERE organisme= $1 and mnemonique= $2 order by organisme, mnemonique,
numVersion DESC";
Here's what happens in mysql log :
050607 8:23:24 1 Query show status
050607 8:23:34 1 Query show status
55 Connect [EMAIL PROTECTED] on scapin_rf_innodb
55 Init DB scapin_rf_innodb
55 Query select round('inf'), round('-inf'),
round('nan')
55 Query SHOW VARIABLES
55 Query SET autocommit=1
55 Query SET autocommit=0
55 Query SELECT
SCA_PROFIL.PRO_ID,SCA_PROFIL.PRO_ORGANISME,SCA_PROFIL.PRO_NOM_PROFIL,SCA_PRO
FIL.PRO_NUM_VERSION,SCA_PROFIL.PRO_RESUME,SCA_PROFIL.PRO_DESCRIPTION,SCA_PRO
FIL.PRO_ORGANISME_PERE,SCA_PROFIL.PRO_NOM_PROFIL_PERE,SCA_PROF_HABILITATION.
HAB_ID FROM SCA_PROFIL LEFT OUTER JOIN SCA_PROF_HABILITATION ON
SCA_PROFIL.PRO_ID=SCA_PROF_HABILITATION.PRO_ID WHERE
(SCA_PROFIL.PRO_ORGANISME = 'National' and SCA_PROFIL.PRO_NOM_PROFIL =
'Technicien') ORDER BY SCA_PROFIL.PRO_ORGANISME , SCA_PROFIL.PRO_NOM_PROFIL
, SCA_PROFIL.PRO_NUM_VERSION DESC
050607 8:23:35 55 Query SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_HABIL
ITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=1
55 Query SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_HABIL
ITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=2
55 Query SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_HABIL
ITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=3
55 Query SELECT
SCA_PROF_HABILITATION.PRO_ID,SCA_PROF_HABILITATION.HAB_ACTION,SCA_PROF_HABIL
ITATION.HAB_DROIT FROM SCA_PROF_HABILITATION WHERE
SCA_PROF_HABILITATION.HAB_ID=4
.
.
.
Here is a test case to reproduce easily the "problem".
Mapping file :
<?xml version="1.0" ?>
<!DOCTYPE mapping PUBLIC "-//EXOLAB/Castor Mapping DTD Version 1.0//EN"
"http://castor.exolab.org/mapping.dtd";>
<mapping>
<class name="test.testCollection.Profil" access="shared"
key-generator="MAX" identity="idProfil">
<cache-type type="count-limited" capacity="100" />
<map-to table="SCA_PROFIL" />
<field name="idProfil" type="integer" required="true">
<sql type="integer" name="PRO_ID" />
<bind-xml name="idProfil" />
</field>
<field name="organisme" type="string">
<sql type="char" name="PRO_ORGANISME" />
<bind-xml name="organisme" />
</field>
<field name="mnemonique" type="string">
<sql type="char" name="PRO_NOM_PROFIL" />
<bind-xml name="mnemonique" />
</field>
<field name="numVersion" type="integer">
<sql type="integer" name="PRO_NUM_VERSION" />
<bind-xml name="numVersion" />
</field>
<field name="resume" type="string">
<sql type="char" name="PRO_RESUME" />
<bind-xml name="resume" />
</field>
<field name="description" type="string">
<sql type="char" name="PRO_DESCRIPTION" />
<bind-xml name="description" />
</field>
<field name="organismePere" type="string">
<sql type="char" name="PRO_ORGANISME_PERE" />
<bind-xml name="organismePere" />
</field>
<field name="nomProfilPere" type="string">
<sql type="char" name="PRO_NOM_PROFIL_PERE" />
<bind-xml name="nomProfilPere" />
</field>
<field name="listeHabilitation" collection="collection" lazy="true"
type="test.testCollection.Habilitation">
<sql many-key="PRO_ID" />
<bind-xml name="listeHabilitation" />
</field>
</class>
<class name="test.testCollection.Habilitation" access="shared"
key-generator="MAX" identity="idHabilitation">
<cache-type type="count-limited" capacity="2000" />
<map-to table="SCA_PROF_HABILITATION" />
<field name="idHabilitation" type="integer" required="true">
<sql type="integer" name="HAB_ID" />
<bind-xml name="idHabilitation" />
</field>
<field name="profil" type="test.testCollection.Profil">
<sql name="PRO_ID" />
<bind-xml name="profil" />
</field>
<field name="action" type="string">
<sql type="char" name="HAB_ACTION" />
<bind-xml name="action" />
</field>
<field name="droitAssocie" type="string">
<sql type="char" name="HAB_DROIT" />
<bind-xml name="droitAssocie" />
</field>
</class>
</mapping>
package test.testCollection;
import fr.cnam.scapin.util.FormatUtils;
public class Profil {
private int idProfil;
private String organisme;
private String mnemonique;
private int numVersion;
private String resume;
private String description;
private String organismePere;
private String nomProfilPere;
private java.util.Collection listeHabilitation;
public Profil() {
}
public String getMnemonique() {
return mnemonique;
}
public void addHabilitation(Habilitation habilitation) {
listeHabilitation.add(habilitation);
}
public String getDescription() {
return description;
}
public int getIdProfil() {
return idProfil;
}
public java.util.Collection getListeHabilitation() {
return listeHabilitation;
}
public String getNomProfilPere() {
return nomProfilPere;
}
public int getNumVersion() {
return numVersion;
}
public String getOrganisme() {
return organisme;
}
public String getOrganismePere() {
return organismePere;
}
public String getResume() {
return resume;
}
public void setDescription(String string) {
description = FormatUtils.nullString(string);
}
public void setIdProfil(int i) {
idProfil = i;
}
public void setListeHabilitation(java.util.Collection collection) {
listeHabilitation = collection;
}
public void setMnemonique(String string) {
mnemonique = FormatUtils.nullString(string);
}
public void setNomProfilPere(String string) {
nomProfilPere = FormatUtils.nullString(string);
}
public void setNumVersion(int i) {
numVersion = i;
}
public void setOrganisme(String string) {
organisme = FormatUtils.nullString(string);
}
public void setOrganismePere(String string) {
organismePere = FormatUtils.nullString(string);
}
public void setResume(String string) {
resume = FormatUtils.nullString(string);
}
}
package test.testCollection;
import fr.cnam.scapin.util.FormatUtils;
public class Habilitation {
private int idHabilitation;
private Profil profil;
private String action;
private String droitAssocie;
public Habilitation() {
}
public String getAction() {
return action;
}
public String getDroitAssocie() {
return droitAssocie;
}
public int getIdHabilitation() {
return idHabilitation;
}
public Profil getProfil() {
return profil;
}
public void setAction(String string) {
action = FormatUtils.nullString(string);
}
public void setDroitAssocie(String string) {
droitAssocie = FormatUtils.nullString(string);
}
public void setIdHabilitation(int i) {
idHabilitation = i;
}
public void setProfil(Profil profil) {
this.profil = profil;
}
}
package test.testCollection;
import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;
import junit.framework.TestCase;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.exolab.castor.jdo.Database;
import org.exolab.castor.jdo.JDO;
import org.exolab.castor.jdo.OQLQuery;
import org.exolab.castor.jdo.QueryResults;
import org.exolab.castor.util.JdoConfFactory;
public class TestCollection extends TestCase {
private static Log log = null;
// private JDO2 jdo;
private JDO jdo = null;
private static final String DATABASE_NAME = "toto";
public static final String databaseFile = "jdo_conf.xml";
private static final String OQL_LES_PROFILS = "SELECT profil FROM
test.testCollection.Profil profil WHERE organisme= $1 and mnemonique= $2
order by organisme, mnemonique, numVersion DESC";
/**
* Creates an instance of this class.
* @param name Name of the test case.
*/
public TestCollection(String name) {
super(name);
}
/*
* @see TestCase#setUp()
*/
protected void setUp() throws Exception {
super.setUp();
log =
LogFactory.getFactory().getInstance(TestCollection.class);
if (jdo == null) {
URL u = this.getClass().getResource(databaseFile);
(databaseFile).toString(), getClass().getClassLoader());
org.exolab.castor.jdo.conf.Database jdoDbConf;
String db_url =
"jdbc:mysql://localhost/scapin_rf_innodb";
String username = "root";
String password = "";
// Use the mapping file located in the current
Java package
URL map_url =
getClass().getResource("mapping.xml");
// Create the JDO object
jdo = new JDO();
jdo.setDatabaseName("toto");
// Set up the JDO configuration with the specified
database driver and connection information
jdoDbConf = JdoConfFactory.createJdoDbConf("toto",
"mysql",
JdoConfFactory.createJdoDriverConf("org.gjt.mm.mysql.Driver",
db_url, username, password));
// Store the mapping file location in the
configuration
jdoDbConf.addMapping(JdoConfFactory.createJdoMappingConf(map_url.toString())
);
// Activate the JDO configuration
jdo.setConfiguration(JdoConfFactory.createJdoConf(jdoDbConf));
if (jdo == null) {
log.error("error.initAccesBaseDeDonnees.erreur" + "\nBase " + DATABASE_NAME
+ " - " + "Objet JDO introuvable ( databaseFileConfig = " + databaseFile + "
- nomDatabase = " + DATABASE_NAME + ")");
} else {
log.info("jdo = " + jdo);
}
}
}
/*
* @see TestCase#tearDown()
*/
protected void tearDown() throws Exception {
super.tearDown();
}
public void testLectureProfils() throws Exception {
Database db = null;
URL baseConfig = null;
ArrayList liste = null;
log.info("\n\nlectureProfils()");
db = jdo.getDatabase();
db.setAutoStore(false);
db.begin();
Profil profil = null;
ArrayList lesProfils = new ArrayList();
OQLQuery oql = null;
QueryResults results = null;
int nbElts = 0;
String paramOrganisme = "National";
String paramProfil = "Technicien";
oql = db.getOQLQuery(OQL_LES_PROFILS);
oql.bind(paramOrganisme);
oql.bind(paramProfil);
// Exécuter la requête
results = oql.execute();
// balayer les objets de la collection
while (results.hasMore()) {
nbElts++;
profil = (Profil) results.next();
assertNotNull (profil);
assertEquals ("Technicien", profil.getMnemonique());
lesProfils.add(profil);
log.info(profil.getIdProfil() + " - " +
profil.getMnemonique()+ " - " + profil.getOrganisme()+ " - " +
profil.getNumVersion());
for (Iterator iter =
profil.getListeHabilitation().iterator(); iter.hasNext();) {
Habilitation element = (Habilitation)
iter.next();
log.info(" --> " +
element.getIdHabilitation()+ " - " + element.getAction()+ " - " +
element.getDroitAssocie());
}
}
db.commit();
db.close();
}
}
___________________
CREDI RA
Sylvie Palluel
[EMAIL PROTECTED]
___________________
> -----Message d'origine-----
> De : Ralf Joachim [mailto:[EMAIL PROTECTED]
> Envoyé : lundi 6 juin 2005 14:18
> À : user@castor.codehaus.org
> Objet : Re: [castor-user] RE : [castor-user] How to optimize collection
> queries ?
>
> 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
> >>À : user@castor.codehaus.org
> >>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: dev@castor.codehaus.org
> >>wg> Cc: user@castor.codehaus.org
> >>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>
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
