Is there any known performance issues with either self-reference or circular reference?

[frankca]

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

-- 
View this message in context: 
http://www.nabble.com/Is-there-any-known-performance-issues-with-either-self-reference-or-circular-reference--tp17194049p17194049.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.