Multiple LEFT JOIN FETCH clauses in EJBQL

[Alexander Saint Croix <sa...@gmail.com>]

Hey, guys.

Is it possible to write an EJBQL query with more than one LEFT JOIN FETCH
clause?  I have a number of entities with more than collection-valued
references that are lazy loaded by default.  I'm trying to avoid flagging
them as FetchType.EAGER.  The O'Reilly book on EJB 3 has exactly [--- this
---] much information about JOIN FETCH clauses, and the web's been less than
forthcoming.

I managed to isolate some patterns in the cascade testing, and am nearly
done with the the first module for my project.  At that point I'll be
tagging a release and redirecting energy toward articles.

Cheers!
--
Alex

Re: Multiple LEFT JOIN FETCH clauses in EJBQL

[Dain Sundstrom <da...@iq80.com>]

On Jan 16, 2008, at 3:03 PM, Alexander Saint Croix wrote:

> Thank you, Dain.
>
> This is helpful in that it reinforces my intuition to avoid eager  
> loading
> for anything that can be replaced by well-written queries.  I'd  
> rather the
> extra data were optionally retrieved than enforced across all  
> applications
> regardless of circumstance.
>
> I asked one of the University's former EJBQL experts, Rogers George,  
> and his
> recommendation regarding using multiple join fetches is "don't."

Rogers and I went to school together... tell him I said hi next time  
you see him.

> He continues:
>
> "You can only fetch one multi-valued association at a time in EJBQL.
> Hibername 3 QL will let you, but it's almost always a bad idea; the
> resultset becomes a cartesian product of the contents of the  
> collections.
> Just running the two queries instead is almost certainly faster.
>
> If the two collections are nested, you can usually rearrange your  
> query so
> that it selects the "leaves" of your entity graph, and all the join  
> fetch
> clauses follow many-to-one relations back towards the root.  Then  
> you can
> iterate over the results directly without even triggering collection
> fetches, and get at the root object with java accessors."
>
> Big thanks to Rogers George for the above information.  My  
> particular need
> involves multiple collection-valued reference, and I'll be  
> investigating
> using fetch groups or fetch plans to tune my query for these tests.   
> Once
> it's working, I'll try to post up some code snippets in case others  
> have
> this trouble down the line.

Looks like solid advice.  Let us know how it works out.

-dain

Re: Multiple LEFT JOIN FETCH clauses in EJBQL

[Alexander Saint Croix <sa...@gmail.com>]

Thank you, Dain.

This is helpful in that it reinforces my intuition to avoid eager loading
for anything that can be replaced by well-written queries.  I'd rather the
extra data were optionally retrieved than enforced across all applications
regardless of circumstance.

I asked one of the University's former EJBQL experts, Rogers George, and his
recommendation regarding using multiple join fetches is "don't."

He continues:

"You can only fetch one multi-valued association at a time in EJBQL.
Hibername 3 QL will let you, but it's almost always a bad idea; the
resultset becomes a cartesian product of the contents of the collections.
Just running the two queries instead is almost certainly faster.

If the two collections are nested, you can usually rearrange your query so
that it selects the "leaves" of your entity graph, and all the join fetch
clauses follow many-to-one relations back towards the root.  Then you can
iterate over the results directly without even triggering collection
fetches, and get at the root object with java accessors."

Big thanks to Rogers George for the above information.  My particular need
involves multiple collection-valued reference, and I'll be investigating
using fetch groups or fetch plans to tune my query for these tests.  Once
it's working, I'll try to post up some code snippets in case others have
this trouble down the line.

Cheers,
--
Alex






On Jan 16, 2008 1:58 PM, Dain Sundstrom <da...@iq80.com> wrote:

> On Jan 16, 2008, at 7:16 AM, Alexander Saint Croix wrote:
>
> > Hey, guys.
> >
> > Is it possible to write an EJBQL query with more than one LEFT JOIN
> > FETCH
> > clause?
>
> Sure.  Just be careful with outer joins.  I always seem to end up
> getting too much data with them, and end up using trial and error to
> get the query right.
>
> > I have a number of entities with more than collection-valued
> > references that are lazy loaded by default.  I'm trying to avoid
> > flagging
> > them as FetchType.EAGER.
>
> FWIU, eager fetch and join fetch, only deal with how much data is
> loaded and cached in the JPA context and not the query result value.
> When it comes to performance of persistent applications, the cache is
> typically the thing that makes or breaks the application.
>
> Most JPA examples I have seen use lots of eager fetching, which makes
> the example perform really well since all data is available, but
> doesn't scale to large data sets.  In the real world, your goal is to
> only load the data you need for your transaction.  If you load too
> little, you end up going back to the database which makes the
> transaction take longer which leaves your locks open longer which
> reduces throughput on the database.  On the other hand, if you load
> too much data, your application with run out of memory and start
> paging, or more likely with the OpenJPA SoftReference chache, your
> extra data gets pushed out of memory and then reloaded.
>
> There is a big trade off here (as with all performance base stuff)
> between memory usage and time spent processing.  There is also a trade
> off between raw performance and complexity of your code since all the
> performance tuning results in more configuration and/or code.  I have
> found that more complex code tends to be slower, simply because no one
> wants to work on it.
>
> > The O'Reilly book on EJB 3 has exactly [--- this
> > ---] much information about JOIN FETCH clauses, and the web's been
> > less than
> > forthcoming.
> >
> > I managed to isolate some patterns in the cascade testing, and am
> > nearly
> > done with the the first module for my project.  At that point I'll be
> > tagging a release and redirecting energy toward articles.
>
> I hope this helped.  I'm not sure what kind of answer you were looking
> for.
>
> -dain
>
>

Re: Multiple LEFT JOIN FETCH clauses in EJBQL

[Dain Sundstrom <da...@iq80.com>]

On Jan 16, 2008, at 7:16 AM, Alexander Saint Croix wrote:

> Hey, guys.
>
> Is it possible to write an EJBQL query with more than one LEFT JOIN  
> FETCH
> clause?

Sure.  Just be careful with outer joins.  I always seem to end up  
getting too much data with them, and end up using trial and error to  
get the query right.

> I have a number of entities with more than collection-valued
> references that are lazy loaded by default.  I'm trying to avoid  
> flagging
> them as FetchType.EAGER.

FWIU, eager fetch and join fetch, only deal with how much data is  
loaded and cached in the JPA context and not the query result value.   
When it comes to performance of persistent applications, the cache is  
typically the thing that makes or breaks the application.

Most JPA examples I have seen use lots of eager fetching, which makes  
the example perform really well since all data is available, but  
doesn't scale to large data sets.  In the real world, your goal is to  
only load the data you need for your transaction.  If you load too  
little, you end up going back to the database which makes the  
transaction take longer which leaves your locks open longer which  
reduces throughput on the database.  On the other hand, if you load  
too much data, your application with run out of memory and start  
paging, or more likely with the OpenJPA SoftReference chache, your  
extra data gets pushed out of memory and then reloaded.

There is a big trade off here (as with all performance base stuff)  
between memory usage and time spent processing.  There is also a trade  
off between raw performance and complexity of your code since all the  
performance tuning results in more configuration and/or code.  I have  
found that more complex code tends to be slower, simply because no one  
wants to work on it.

> The O'Reilly book on EJB 3 has exactly [--- this
> ---] much information about JOIN FETCH clauses, and the web's been  
> less than
> forthcoming.
>
> I managed to isolate some patterns in the cascade testing, and am  
> nearly
> done with the the first module for my project.  At that point I'll be
> tagging a release and redirecting energy toward articles.

I hope this helped.  I'm not sure what kind of answer you were looking  
for.

-dain