Thank you, Jacob, for the information. My SQL tarce (with P6Spy) confirmed your discussion here which OJB do generate IN-statements. But, how can I generate the sample SQL statement in the turtorial,
SELECT DISTINCT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM Kategorien A0 INNER JOIN Artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr LEFT OUTER JOIN BOOKS A1E0 ON A0.Kategorie_Nr=A1E0.Kategorie_Nr LEFT OUTER JOIN CDS A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr WHERE A1.Artikelname LIKE 'F%' OR A1E0.Artikelname LIKE 'F%' OR A1E1.Artikelname LIKE 'F%' with INNER JOIN. With IN-statement, you still execute n SQLs. Will using JOIN be more efficient?Thank you for your insight!Jay----- Original Message ----- From: "Jakob Braeuchi" <[EMAIL PROTECTED]> To: "OJB Users List" <[EMAIL PROTECTED]> Sent: Saturday, January 31, 2004 9:08 AM Subject: Re: getCollectionByQuery() hung up in prefetchRelationship() > hi, > > ojb uses IN-statements to minimize the number of queries executed. > > the following sample uses the ProductGroup 1:n Articles model shipped with ojb. > > i executed a query returning about 50 Articles associated with ProductGroups > 1,2,3,4 or 5. ojb now retrieves these ProducGroups in a single SQL instead of > using about 50 SQLs: > > SELECT A0.KategorieName,A0.Kategorie_Nr,A0.Beschreibung FROM Kategorien A0 WHERE > A0.Kategorie_Nr IN ( '2' , '4' , '5' , '3' , '1' ) > > in OJB.properties there's a limit for the number of values in the IN-Clause, > default is afaik 200. if this limit is exeeded ojb has to execute another SELECT. > > please provide the sql-trace. > > hth > jakob > > > Jay Xu wrote: > > Unfortuantely, this is also what I observed. If you use P6Spy, you will see > > that instead of generating JOIN in SQL statement, it creates seperate SQL > > statements which cause 1+n SQL statement execution (in 1:1 mapping). This > > works, but quite inefficient. I followed turtorial about JOIN, but no luck. > > Really hope some experts in the group can help. > > > > ----- Original Message ----- > > From: "Jason Woodard" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Friday, January 30, 2004 10:36 AM > > Subject: getCollectionByQuery() hung up in prefetchRelationship() > > > > > > > >>Hi, > >> > >>I am trying to use OJB to manage experiment data, and have started > >>with a naive approach where each experiment is a nested collection > >>eventually ending with variable/value pairs. Storing data works well, > >>and retrieving it using getCollectionByQuery() works eventually, but > >>is taking much longer than it should. > >> > >>When retrieving an experiment object, OJB invokes BasePrefetcher's > >>prefetchRelationship(), which makes a bunch of JDBC calls via > >>getCollectionByQuery(), which seems to return all the data OJB needs > >>to reconstruct the object. This takes about 10 seconds for my test > >>experiment (1000 runs, 25 observations each). prefetchRelationship() > >>then invokes associateBatched(), and stays there for another 20 > >>minutes with no further debug output, mainly creating Identity > >>objects. > >> > >>I'm wondering whether I've set up my schema in a way that's grossly > >>inefficient for OJB. (I've appended part of the repository.xml file > >>below.) I've thought about adding a reference from Value back to > >>Level, or using explicit PKs instead of anonymous ones, but haven't > >>tried either yet. > >> > >>FWIW, I'm using OJB 1.0.rc5 with the PB interface against a MySQL > >>database (3.0.9-stable driver). > >> > >>many thanks for any ideas, > >> > >> -j > >> > >>Jason Woodard > >>[EMAIL PROTECTED] > >> > >> > >> > >><!-- Definitions for sim.harness.store.Level --> > >><class-descriptor > >> class="sim.harness.store.Level" > >> table="LEVEL" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <!-- foreign key for parent collection --> > >> <field-descriptor > >> name="seriesId" > >> column="SERIES" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="variableId" > >> column="VARIABLE" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <reference-descriptor > >> name="variable" > >> class-ref="sim.harness.store.Variable" > >> auto-update="true" > >> auto-delete="true" > >> > > >> <foreignkey field-ref="variableId"/> > >> </reference-descriptor> > >> <field-descriptor > >> name="valueId" > >> column="VALUE" > >> jdbc-type="INTEGER" > >> access="anonymous" > >> /> > >> <reference-descriptor > >> name="value" > >> class-ref="sim.harness.store.Value" > >> auto-update="true" > >> auto-delete="true" > >> > > >> <foreignkey field-ref="valueId"/> > >> </reference-descriptor> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.Variable --> > >><class-descriptor > >> class="sim.harness.store.Variable" > >> table="VARIABLE" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="name" > >> column="NAME" > >> jdbc-type="VARCHAR" > >> /> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.Value --> > >><class-descriptor > >> class="sim.harness.store.Value" > >> > > >> <extent-class class-ref="sim.harness.store.AbstractValue"/> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.AbstractValue --> > >><class-descriptor > >> class="sim.harness.store.AbstractValue" > >> > > >> <extent-class class-ref="sim.harness.store.IntValue"/> > >> <extent-class class-ref="sim.harness.store.DoubleValue"/> > >> <extent-class class-ref="sim.harness.store.BooleanValue"/> > >> <extent-class class-ref="sim.harness.store.StringValue"/> > >></class-descriptor> > >> > >><!-- Definitions for sim.harness.store.DoubleValue --> > >><class-descriptor > >> class="sim.harness.store.DoubleValue" > >> table="DBL_VALUE" > >> > > >> <field-descriptor > >> name="id" > >> column="ID" > >> jdbc-type="INTEGER" > >> primarykey="true" > >> autoincrement="true" > >> access="anonymous" > >> /> > >> <field-descriptor > >> name="value" > >> column="VALUE" > >> jdbc-type="DOUBLE" > >> /> > >></class-descriptor> > >> > >><!-- etc. for other Value types --> > >> > >> > >>--------------------------------------------------------------------- > >>To unsubscribe, e-mail: [EMAIL PROTECTED] > >>For additional commands, e-mail: [EMAIL PROTECTED] > >> > > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
