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