Hi Ralf, Thanks for your help.
I'll make the tests as soon as possible, and I'll tell you what happens... Sylvie. ___________________ CREDI RA Sylvie Palluel [EMAIL PROTECTED] ___________________ > -----Message d'origine----- > De�: Ralf Joachim [mailto:[EMAIL PROTECTED] > Envoy�: mercredi 8 juin 2005 09:58 > ��: [email protected] > Objet�: Re: [castor-user] RE :[castor-user] How to optimize collection > queries ? > > Hi Sylvie, > > I created a test case to show how you can improve load performance for > collections. You need to: > > 1. setup a test database (I called it sylvie) with user 'test' having > password 'test'. > 2. create test tables with create.sql script. > 3. change jdo-conf.xml to point to your database. > 4. run TestCreate once to create the records to load. > 5. run TestLoadCollection to see how much time is required to load the > data. > 6. run TestRemove to delete all records of the tables. > > The data I created are 3 profils that match the load query and 7 profils > that don't. The test data creates 9999 habilitations that are equal > distributed over all the profiles. You can adjust this values to your > needs very easy by changing the PROFIL_MATCH, PROFIL_FAIL and > HABILITATION constants in TestCreate. > > I tested different configurations of the mapping with lazy loading for > listHabilitation enabled and disabled and different cache sizes for > habilitations. As we load 3 profils with about 1000 habilitations each I > used cache size 1000 and 3000. While habilitations need to be reloaded > with cache size 1000 all habilitations are cached with 3000. If you > increase cache size to more than 3000 there will be no further > improvement. > > 1. lazy=true / cache=1000 > OQL_RW: 1875 OQL_RO: 1828 SQL_RW: 1625 SQL_RO: 1672 > > 2. lazy=true / cache=3000 > OQL_RW: 937 OQL_RO: 859 SQL_RW: 750 SQL_RO: 687 > > 3. lazy=false / cache=1000 > OQL_RW: 1844 OQL_RO: 1578 SQL_RW: 1609 SQL_RO: 1406 > > 4. lazy=false / cache=3000 > OQL_RW: 875 OQL_RO: 312 SQL_RW: 750 SQL_RO: 79 > > The result show that it makes no sense to switch on lazy loading if you > need all values of the collection. Having said that it may make sense if > you only need some of the habilitations. You should load your objects > read only where possible and only reload them shared when you realy know > that you need to update them. In addition you can gain a lot of > performance by tuning the cache size. The technic with the identity > loading I used with the SQL results will be integrated with castor in > one of the next releases. We also want to test if we can gain a furter > improvement with a foreign key loading as you suggested. > > Hope this will help. > Ralf > > > SYLVIE PALLUEL schrieb: > > 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_P > RO > > > FIL.PRO_NUM_VERSION,SCA_PROFIL.PRO_RESUME,SCA_PROFIL.PRO_DESCRIPTION,SCA_P > RO > > > FIL.PRO_ORGANISME_PERE,SCA_PROFIL.PRO_NOM_PROFIL_PERE,SCA_PROF_HABILITATIO > N. > > 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_HAB > IL > > 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_HAB > IL > > 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_HAB > IL > > 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_HAB > IL > > 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] > > ------------------------------------------------- > > -- > > 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] -------------------------------------------------
