cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Zeigler <robe...@puregumption.com>
Subject Re: joint prefetching narrows result set
Date Thu, 20 Apr 2006 00:51:14 GMT
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.
>
>
>   


Mime
View raw message