Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Performance problem with hierarchical treestructure (JPA) 
Hi Mathias,
You are right that getting all the data from SQL is a matter of a very simple select statement. 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. How would you write your single SQL statement that would get the nodes from one tree and only the nodes from that tree? 

  Here are some things that might help

1. Batch Reading - You can read the levels of the tree in batches
There is a query hint you can use. Just look for eclipselink.batch under the following link: 

http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#How_to_Use_EclipseLink_JPA_Query_Hints
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.
2. A Lazy mapping - do you need all the data from the tree at once? If not, it may be better to have you mapping be lazy. 

-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

Back to the top