cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Lewis <br...@maine.rr.com>
Subject joint prefetching narrows result set
Date Thu, 20 Apr 2006 00:28:07 GMT
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.



Mime
View raw message