RE: [eclipselink-users] Performance problem with hierarchical treestruct

["Mathias Walter" <mathias.walter@xxxxxxx>]

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