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.
