Re: getCollectionByQuery() hung up in prefetchRelationship()

Jakob Braeuchi Sat, 31 Jan 2004 12:25:41 -0800

hi jay,

this sql is generated because of the extents defined for article and not because of prefetching. i agree that prefetching using joins may be more efficient. but the handling of the resultset is more complicated :(

hth
jakob

Jay Xu wrote:

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]

<class-descriptor
  class="sim.harness.store.Level"
  table="LEVEL"
  >
  <field-descriptor
      name="id"
      column="ID"
      jdbc-type="INTEGER"
      primarykey="true"
      autoincrement="true"
      access="anonymous"
      />
  
  <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>

<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>

<class-descriptor
  class="sim.harness.store.Value"
  >
  <extent-class class-ref="sim.harness.store.AbstractValue"/>
</class-descriptor>

<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>

<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>

---------------------------------------------------------------------
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]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: getCollectionByQuery() hung up in prefetchRelationship()

Reply via email to