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.
>
>
>
This archive was generated by hypermail 2.0.0 : Wed Apr 19 2006 - 20:51:42 EDT