FAQ
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.

Search Discussions

  • Robert Zeigler at Apr 20, 2006 at 12:51 am

    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.

  • Andrus Adamchik at Apr 20, 2006 at 6:40 am

    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
  • Andrus Adamchik at Apr 20, 2006 at 6:27 am

    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
  • Bryan Lewis at Apr 20, 2006 at 2:19 pm
    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
  • Arnaud GARCIA at Apr 24, 2006 at 2:57 pm
    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
  • Cris Daniluk at Apr 24, 2006 at 3:13 pm

    On 4/24/06, Arnaud GARCIA 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedApr 20, '06 at 12:28a
activeApr 24, '06 at 3:13p
posts7
users5
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase