[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Performance problem with hierarchicaltreestructure (JPA)
|
Hi Mathias,
I will definitely have to do some reading representing hierarchical data.
I am not sure JPA provides exactly what you are looking for by default.
There are certain performance optimizations that can reduce the amount of SQL
you will get, be in the end, when EAGERLY reading an arbitrarily deep object
hierarchy, you are going to get a fairly large number of SQL statements.
JPA provides fetch joins, and LAZY mappings that help in many cases.
EclipseLink goes further to allow deeper fetch joins, batch reading, and uses a
cache to avoid some additional SQL. However, if you are going arbitrarily deep
EAGERLY, at some point, those things become less helpful.
EclipseLink actually does have a hierarchical query feature, but at the
moment, it uses Oracle-specific SQL syntax. Here is a link to the documentation:
http://wiki.eclipse.org/Using_Advanced_Query_API_(ELUG)#How_to_Use_Hierarchical_Queries
If there are facilities in MySQL (or other databases) that allow similar
functionality, I believe supporting those facilities would be desirable and
would suggest entering a feature request in Bugzilla.
Sorry not to have a better answer,
Tom
Mathias Walter wrote:
Hi Tom,
The issue for us is associating the data
with objects. We
need a way to take the result set and map it to the objects
in such a way as to
create the object tree correctly. We build the tree level by
level to ensure everything is correct.
That's what I also have to do.
How would you write your single SQL statement that would
get the nodes from one tree and only the nodes from that tree?
Do you mean subtree or branch instead of "tree", did you?
Do you now the nested set model to represent trees? At mysql.com you'll find
some interessting articles about this model and how to use it to store
hierarchical data.
The book "Joe Celko's Trees and Hierarchies in SQL for Smarties" might also
be interesting for you.
If writing a query that uses batching helps, using some
customization code, it
is also possible to define the actual mapping as batched.
That could further reduce your SQL.
I don't know who to map a result set to the object hierarchy with JPA. At
the moment I'm using a named query to get the nodes and then create the
object hierarchy by myself. I've declared the childNode collection as
transient. But now I can not store the relationship together with the
object.
2. A Lazy mapping - do you need all the data from the tree at
once?
Yes I need all data of a specific subtree, because the nodes and their
hierarchy are displayed at a web page with a tree object.
--
Kind regards,
Mathias
-Tom
Mathias Walter wrote:
Hi Tom,
What is your goal? Do you want the whole tree read in in
one SQL statement?
I'd be careful about this because the number of joins
required to do this gets
exponentially big. For that reason, there is not really a
That's incorrect from the point of SQL. You can get a whole
tree with just
one select (SELECT * ...). It would be correct if a
specific branch is
queried, but only in case of a tree implementation
according to the adjaceny
list model as used by Rune.
If the tree is implemented according to the nested set
model, no recursive
fetch is necessary. Just a simple select would return a
branch, path or the
whole tree.
The problem is to create the object hierarchy based on the
result set
(doesn't matter which tree model is used).
Is there any customizer available to achieve this task?
--
Kind regards,
Mathias
way to specify a
recursive fetch join in EclipseLink.
Have you considered making your Node.childNodes collection
LAZY? That would
certainly reduce the amount of SQL you see on the initial
read and reduce the
SQL on subsequent reads as well.
In your example with the NPE, you are likely seeing the
issue because you
need to fetch join t.top before you can fetch join
t.top.childNodes.
e.g.
<named-query name="treeQuery">
<query>select t
from Tree t
where t.name = ?1
</query>
<hint name="eclipselink.join-fetch" value="t.top"/>
<hint name="eclipselink.join-fetch" value="t.top.childNodes"/>
</named-query>
-Tom
Rune Kvamme wrote:
Hi,
I have a performance issue that I hope to receive some
advice on. I know
that one of the major benefits with EclipseLink is its
ability to tune
the generated selects, and I need some help with the
following case.
Iv created a small test case to illustrate the problem.
I have two classes which represents a hierarchical tree
structure. The
"Tree" class contains one top Node and each node may
contain zero or
more child nodes.
@Entity
public class Tree {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Version
private Long version;
@Basic
private String name;
@OneToOne(cascade = CascadeType.ALL)
private Node top;
...
...
}
@Entity
@Table(name = "NODE")
public class Node extends Tree {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Version
private Long version;
@Basic
private String name;
@Basic
private String xyz;
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Node> childNodes;
...
...
...
}
I have the following JPQL query;
<query>select t
from Tree t
where t.name = ?1
</query>
When issuing the query EclipseLink generates the
following queries
(assuming that nothing is in the cache).
1. On query to get the Tree object.
2. On query to get the Top Node object.
3. Then one query for each Node in the tree structure to
get the nodes
children.
I have tried to use different types of hints, e.g.
eclipselink.join-fetch, but without any luck.
The below hint just caused a null pointer exception.
<named-query name="treeQuery">
<query>select t
from Tree t
where t.name = ?1
</query>
<hint name="eclipselink.join-fetch" value="t.top.childNodes"/>
</named-query>
What am i doing wrong. Is there a way to achieve this
without eclipslink
generating so many queries?
Please help:-)
Best regards,
Rune
--------------------------------------------------------------
----------
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
