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
� : [email protected]
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
� : [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>
-------------------------------------------------
If you wish to unsubscribe from this list, please
send an empty message to the following address:
[EMAIL PROTECTED]
-------------------------------------------------
