Hi,

OpenJPA 1.0.0
Oracle 10g R2

Is there any known performance issues with either self-reference or circular
reference? When I analyze Oracle SQL trace, it reports the CPU and number of
queries way too high for tables which have only < 10 rows!

THE PERSISTENCE CLASSES:

public class Taxonomy ... {
  // ...
  @ManyToOne   // Also tried (fetch = FetchType.LAZY)
  @JoinColumn(name = "root_category_fk")
  private Category rootCategory;   <=== Circular reference
}

public class Category ... {
  @ManyToOne
  @JoinColumn(name = "taxonomy_fk", nullable = false)
  private Taxonomy taxonomy;   <=== Circular reference

  @ManyToOne   // Also tried (fetch = FetchType.LAZY)
  @JoinColumn(name = "parent_category_fk")
  private Category parent;   <=== Self-reference

  @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
  private Set<Category> children = new HashSet<Category>();
}

NOTE: There's database index on taxonomy's name and category's taxonomy_fk.

If I executed the following query:
  @NamedQuery(
    name = "Taxonomy.findByName",
    query = "SELECT t " +
          "FROM Taxonomy t " +
          "WHERE t.name = :name"
  )

From OpenJPA trace:
...> Executing query: [SELECT c FROM Category c WHERE c.labelPath =
:labelPath AND c.taxonomy = :taxonomy] with parameters: {labelPath=/Topic,
taxonomy=id=1, name=TestTaxonomy, ver=1, }
...> executing prepstmnt 23053938 SELECT t0.id, t0.version,
t0.last_updated_by, t0.last_updated_date, t0.label, t0.label_path,
t0.parent_category_fk, t1.id, t1.version, t1.last_updated_by,
t1.last_updated_date, t1.name, t1.root_category_fk FROM category t0,
taxonomy t1 WHERE (t0.label_path = ? AND t0.taxonomy_fk = ?) AND
t0.taxonomy_fk = t1.id(+) [params=(String) /Topic, (long) 1]
...> executing prepstmnt 4368408 SELECT t0.id, t0.version,
t0.last_updated_by, t0.last_updated_date, t0.label, t0.label_path,
t0.parent_category_fk, t1.id, t1.version, t1.last_updated_by,
t1.last_updated_date, t1.name, t1.root_category_fk FROM category t0,
taxonomy t1 WHERE t0.parent_category_fk = ? AND t0.taxonomy_fk = t1.id(+)
[params=(long) 1]

From the Oracle SQL trace, you can tell it takes too much CPU and number or
queries for 1 taxonomies and 8 categories:

SELECT t0.id, t0.version, t0.last_updated_by, t0.last_updated_date,
t0.label,
t0.label_path, t1.id, t1.version, t1.last_updated_by,
t1.last_updated_date,
t1.name
FROM
category t0, taxonomy t1 WHERE t0.parent_category_fk = :1 AND
t0.taxonomy_fk
= t1.id(+)


call   count     cpu   elapsed     disk    query   current    
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse   24698    0.90     0.95       0       0       0      
0
Execute 24823    1.42     1.33       0       4       0      
0
Fetch   24821    6.39     6.68       0   270797       0    
57173
------- ------ -------- ---------- ---------- ---------- ----------
----------
total   74342    8.71     8.98       0   270801       0    
57173


Any helps is greatly appreciated.

-Frank

--
Sent from the OpenJPA Users mailing list archive at Nabble.com.