You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bryan Lewis <br...@maine.rr.com> on 2006/04/20 02:28:07 UTC
joint prefetching narrows result set
I was doing some performance tweaking on some of our old queries with
the new joint-prefetch semantics, and I was a little surprised. When I
added a joint prefetch on a non-mandatory relationship, the number of
rows returned was reduced.
The code:
Expression exp = ExpressionFactory.greaterOrEqualExp("publicationDate",
from);
exp = exp.andExp(ExpressionFactory.lessOrEexpExp("publicationDate", to));
SelectQuery query = new SelectQuery("Story", exp);
List list = dc.performQuery(query);
Generated SQL:
SELECT t0.*
FROM STORY t0, LKPSTORYTYPE t1
WHERE t0.STORYTYPE_ID = t1.STORYTYPE_ID
AND ((t0.PUBLICATIONDATE >= ?)
AND (t0.PUBLICATIONDATE <= ?))
=== returned 153 rows.
Then I added this joint prefetch on the non-mandatory "claim" relationship:
query.addPrefetch("claim").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
SELECT t0.*
FROM STORY t0, CLAIM t1, LKPSTORYTYPE t2
WHERE t0.CLAIM_ID = t1.CLAIM_ID // new qualifier
AND t0.STORYTYPE_ID = t2.STORYTYPE_ID
AND ((t0.PUBLICATIONDATE >= ?)
AND (t0.PUBLICATIONDATE <= ?))
=== returned 15 rows.
Since only a small subset of the Story objects have associated claims,
the result set was reduced.
Maybe it's common knowledge that one shouldn't specify a joint prefetch
on such a relationship? (The old non-joint prefetch with its separate
query didn't cause any trouble, of course.) I didn't see this discussed
in the user guide. It's not a big deal but I thought I'd mention it in
case I'm doing something wrong or it's not common knowledge.
Re: joint prefetching narrows result set
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 20, 2006, at 4:51 AM, Robert Zeigler wrote:
> Hm. That's interesting... I haven't looked at the queries generated
> for
> the new stuff... if I was writing raw sql, I probably would have
> done a
> left outer join (but maybe not enough db vendors support it???) and
> used
> an "ON" clause for the join, rather than a where clause... I always
> thought that "Where" is for winnowing results, and "on" is for joining
> tables...? I'll confess to being curious as to the rational for using
> "where" for a joint prefetch rather than "on".
ON is supposedly a standard SQL syntax for both INNER and OUTER
joins, however historically most databases did the joins as a part of
WHERE clause. For instance an OUTER join on Oracle could be done like
this:
select t1.* from painting t1, artist t2
where t1.artist_id = t2.artist_id (+)
We'll be considering the "standard" syntax of with ON when we start
work on OUTER joins support in relationships.
Andrus
Re: joint prefetching narrows result set
Posted by Robert Zeigler <ro...@puregumption.com>.
Bryan Lewis wrote:
> I was doing some performance tweaking on some of our old queries with
> the new joint-prefetch semantics, and I was a little surprised. When I
> added a joint prefetch on a non-mandatory relationship, the number of
> rows returned was reduced.
>
> The code:
>
> Expression exp = ExpressionFactory.greaterOrEqualExp("publicationDate",
> from);
> exp = exp.andExp(ExpressionFactory.lessOrEexpExp("publicationDate", to));
> SelectQuery query = new SelectQuery("Story", exp);
> List list = dc.performQuery(query);
>
> Generated SQL:
>
> SELECT t0.*
> FROM STORY t0, LKPSTORYTYPE t1
> WHERE t0.STORYTYPE_ID = t1.STORYTYPE_ID
> AND ((t0.PUBLICATIONDATE >= ?)
> AND (t0.PUBLICATIONDATE <= ?))
> === returned 153 rows.
>
> Then I added this joint prefetch on the non-mandatory "claim" relationship:
>
> query.addPrefetch("claim").setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>
> SELECT t0.*
> FROM STORY t0, CLAIM t1, LKPSTORYTYPE t2
> WHERE t0.CLAIM_ID = t1.CLAIM_ID // new qualifier
> AND t0.STORYTYPE_ID = t2.STORYTYPE_ID
> AND ((t0.PUBLICATIONDATE >= ?)
> AND (t0.PUBLICATIONDATE <= ?))
> === returned 15 rows.
>
>
Hm. That's interesting... I haven't looked at the queries generated for
the new stuff... if I was writing raw sql, I probably would have done a
left outer join (but maybe not enough db vendors support it???) and used
an "ON" clause for the join, rather than a where clause... I always
thought that "Where" is for winnowing results, and "on" is for joining
tables...? I'll confess to being curious as to the rational for using
"where" for a joint prefetch rather than "on".
Robert
> Since only a small subset of the Story objects have associated claims,
> the result set was reduced.
>
> Maybe it's common knowledge that one shouldn't specify a joint prefetch
> on such a relationship? (The old non-joint prefetch with its separate
> query didn't cause any trouble, of course.) I didn't see this discussed
> in the user guide. It's not a big deal but I thought I'd mention it in
> case I'm doing something wrong or it's not common knowledge.
>
>
>
Re: joint prefetching narrows result set
Posted by Bryan Lewis <br...@maine.rr.com>.
Cool, thanks. Gotta switch my bookmark over to the new docs!
Andrus Adamchik wrote:
>
> On Apr 20, 2006, at 4:28 AM, Bryan Lewis wrote:
>
>> Maybe it's common knowledge that one shouldn't specify a joint prefetch
>> on such a relationship? (The old non-joint prefetch with its separate
>> query didn't cause any trouble, of course.) I didn't see this
>> discussed
>> in the user guide. It's not a big deal but I thought I'd mention it in
>> case I'm doing something wrong or it's not common knowledge.
>
>
> Bryan,
>
> It is mentioned briefly in the new docs (last item under prefetching
> hints) -
>
> http://objectstyle.org/confluence/display/CAYDOC/Prefetching
>
> This is a totally arbitrary limitation. In the following releases
> we'll add support for the outer joins and this will become a non-issue.
>
> Andrus
>
Re: ConcurrentModificationException ...
Posted by Cris Daniluk <cr...@gmail.com>.
On 4/24/06, Arnaud GARCIA <ar...@sim.hcuge.ch> wrote:
> Hello,
> I have a problem with multithreaded access with cayenne ...
> My model is a simple one to many relationship (an Order can have many
> series)
>
> In my main thread I add new series to an Order :
> order.addToSeries(aSerie);
>
> But in another thread I am doing an Iteration over the series which
> launch an exception:
> List series = order.getSeries();
> for (Iterator iter = series.iterator(); iter.hasNext();) {
> ...
> }
Cayenne is returning its internal reference to the collection. This is
easy to avoid by just wrapping the collection:
List series = new ArrayList(order.getSeries());
This is a good general habit to be in anyway... in single-theaded
apps, you'll still get the CME if you attempt to modify the collection
(through remove, etc)
Cris
ConcurrentModificationException ...
Posted by Arnaud GARCIA <ar...@sim.hcuge.ch>.
Hello,
I have a problem with multithreaded access with cayenne ...
My model is a simple one to many relationship (an Order can have many
series)
In my main thread I add new series to an Order :
order.addToSeries(aSerie);
But in another thread I am doing an Iteration over the series which
launch an exception:
List series = order.getSeries();
for (Iterator iter = series.iterator(); iter.hasNext();) {
...
}
Exception in thread "Thread-985" java.util.ConcurrentModificationException
at
java.util.LinkedList$ListItr.checkForComodification(LinkedList.java:617)
Is there a way to lock/synchronize the addToSeries or the iter.hasNext()
.... what is the good way to do this with cayenne ?
many thanks,
Arnaud
Re: joint prefetching narrows result set
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Apr 20, 2006, at 4:28 AM, Bryan Lewis wrote:
> Maybe it's common knowledge that one shouldn't specify a joint
> prefetch
> on such a relationship? (The old non-joint prefetch with its separate
> query didn't cause any trouble, of course.) I didn't see this
> discussed
> in the user guide. It's not a big deal but I thought I'd mention
> it in
> case I'm doing something wrong or it's not common knowledge.
Bryan,
It is mentioned briefly in the new docs (last item under prefetching
hints) -
http://objectstyle.org/confluence/display/CAYDOC/Prefetching
This is a totally arbitrary limitation. In the following releases
we'll add support for the outer joins and this will become a non-issue.
Andrus