Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Tree structure - avoid select on parent_id
Tree structure - avoid select on parent_id [message #385288] Sun, 18 January 2009 16:51 Go to next message
Peter  is currently offline Peter
Messages: 3
Registered: July 2009
Junior Member
Gentlepeople,

Being new to JPA in general and even newer wrt EclipseLink I hope the
level of the question is nevertheless ok for this newsgroup.

I have a fairly straightforward class structure to model a tree (abstract
BaseNode,Node and Leaf).

The Node has a Collection<BaseNode> member and a BaseNode has a parent
member.

I am now trying to write code to optimize the retrieval of a complete
subtree.

I am using (LEFT) JOIN FETCH and that kind of works. I still see a query
which at least in theory I think is not needed.

The EJBQL is

SELECT n FROM Node n LEFT JOIN FETCH n.nodes WHERE n.path = :path

What I see in the SQL logging is


SELECT t1.id, t1.type, t1.path, t1.parent_id, t0.id, t0.type, t0.path,
t0.parent_id FROM node t1 LEFT OUTER JOIN node t0 ON (t0.parent_id =
t1.id) WHERE ((t1.path = ?) AND (t1.type = ?))
bind => [/r/c2/c24/, 2]

SELECT id, type, path, parent_id FROM node WHERE (parent_id = ?)
bind => [52559]


I am wondering whether there is anything I can do to avoid the second
select. I would think that all information was already retrieved in the
first select (52559 is the id of the Node with path /r/c2/c24/)

Is this possible with EclipseLink or would that not always be correct?

Many thanks indeed!

Peter
Re: Tree structure - avoid select on parent_id [message #385291 is a reply to message #385288] Mon, 19 January 2009 08:46 Go to previous message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You are joining the children, but not the parent. If the parent is in the
cache, then it will get a cache hit, but if any of the parents are not in
the cache, and your relationship is not lazy, then it will need to select
it.

I would recommend making your parent (and children) lazy. You could also
join the parent, but then you could still have selects for the parent's
parent, so lazy really should be used.

You may also consider using batch reading than joining, it is more
efficient.
See,
http://www.eclipse.org/eclipselink/api/1.0.1/org/eclipse/per sistence/config/QueryHints.html#BATCH

----
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Previous Topic:ddl generation without jpa
Next Topic:Native Query issues
Goto Forum:
  


Current Time: Mon Sep 01 22:16:07 EDT 2014

Powered by FUDForum. Page generated in 0.01635 seconds