Re: getCollectionByQuery() hung up in prefetchRelationship()

[Jakob Braeuchi]

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]