docs/2.0/prefetching.html

Title: Prefetching

	
<H3><A name="Prefetching-IntroductiontoPrefetching"></A>Introduction to Prefetching</H3>

<P>Prefetching is a performance optimization technique that allows to bring back more than one type of objects in a single query. Prefetches are configured in terms of relationship paths from the query root entity to the &quot;prefetched&quot; entity. E.g.:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-comment">// configure query with prefetches
</SPAN>SelectQuery query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class);
query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray&quot;</SPAN>); 
...
<SPAN class="code-comment">// execute query and <SPAN class="code-keyword">do</SPAN> something with results
</SPAN>List artists = context.performQuery(query);
Iterator it = artists.iterator();
<SPAN class="code-keyword">while</SPAN>(it.hasNext()) {
  Artist a = (Artist) it.next();
  <SPAN class="code-object">System</SPAN>.out.println(<SPAN class="code-quote">&quot;paintings: &quot;</SPAN> + a.getPaintingArray().size());
}
</PRE>
</DIV></DIV>

<P>When prefetching is set, corresponding relationships are &quot;inflated&quot; with database objects within a single <TT>performQuery</TT> run, leaving it up to Cayenne to optimize retrieval of multiple entities. For instance the example above results in just two SQL queries issued to the database internally, while running the same query without a prefetch and later iterating over artists will result in <TT>1 + N</TT> queries, where <TT>N</TT> is the number of artists returned. </P>

<H3><A name="Prefetching-PrefetchingHints"></A>Prefetching Hints</H3>

<UL>
	<LI>All types of relationships can be prefetched - to-one, to-many, flattened.</LI>
	<LI>A prefetch can span more than one relationship:
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray.toGallery&quot;</SPAN>);
</PRE>
</DIV></DIV></LI>
	<LI>A query can have more than one prefetch path at the same time:
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray&quot;</SPAN>); 
query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray.toGallery&quot;</SPAN>);
</PRE>
</DIV></DIV></LI>
	<LI><FONT color="red">PREFETCH LIMITATION:</FONT> To-many relationships should not be prefetched if a query qualifier can potentially reduce a number of related objects, resulting in incorrect relationship list. E.g.:
<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
SelectQuery query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class);

Expression exp = ExpressionFactory.matchExp(<SPAN class="code-quote">&quot;paintingArray.paintingTitle&quot;</SPAN>, <SPAN class="code-quote">&quot;Some Painting&quot;</SPAN>);

<SPAN class="code-comment">// INVALID!! since there can be more than one painting per artist, <SPAN class="code-keyword">this</SPAN> prefetch
</SPAN><SPAN class="code-comment">// wouldn't work.
</SPAN>query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray&quot;</SPAN>); 
</PRE>
</DIV></DIV>
<P>In the future versions of Cayenne this will be addressed by using SQL subqueries. For now it is programmer's responsibility to avoid such prefetches.</P></LI>
	<LI>If SelectQuery is fetching data rows, all default prefetches are ignored, though custom joint prefetches (see below) will be included.</LI>
	<LI>When you customize SelectQuery prefetches to use joint semantics (see below how customization can be done), be aware that joint prefetch adds an extra inner join to the main query. This may result in fewer objects returned than expected. If you are SQL-savvy it may be helpful to think of disjoint prefetches as analogous to SQL outer joins and joint prefetches - to SQL inner joins.</LI>
</UL>


<P><EM>The rest of this page describes advanced use and can be skipped.</EM></P>


<H3><A name="Prefetching-PrefetchSemantics"></A>Prefetch Semantics</H3>

<P><EM>(semantics flavors were introduced in 1.2M3, with some changes in 1.2M8)</EM></P>

<P>Queries store prefetching information as trees of <TT>PrefetchTreeNode</TT> objects:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
PrefetchTreeNode treeRoot = query.getPrefetchTree();
<SPAN class="code-keyword">if</SPAN>(treeRoot != <SPAN class="code-keyword">null</SPAN>) {
  <SPAN class="code-comment">// <SPAN class="code-keyword">do</SPAN> something with tree nodes
</SPAN>}
</PRE>
</DIV></DIV>

<P>Each node specifies the name of prefetch path segment and execution semantics. There are two flavors of prefetch semantics - <B>joint</B> and <B>disjoint</B>. Semantics of each node is initially determined by Cayenne when a new prefetch path is added, and can be later customized by the user (e.g., see joint example below).</P>

<DIV class="panelMacro"><TABLE class="noteMacro"><COLGROUP><COL width="24"><COL></COLGROUP><TR><TD valign="top"><IMG src="http://cayenne.apache.org/docs/1.2/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></TD><TD>In most cases prefetch semantics is of no concern to the users. Cayenne will do its best to configure the right semantics on the fly. Don't tweak semantics unless you understand the implications and have some proof that different semantics would result in better select performance on your database.</TD></TR></TABLE></DIV>

<P>Some internal semantics rules:</P>

<UL>
	<LI>SelectQuery uses disjoint prefetches by default.</LI>
	<LI>SQLTemplate and ProcedureQuery use joint prefetches and can not use disjoint semantics due to their nature.</LI>
	<LI>Prefetches with different semantics can be mixed freely within a query, as long as there is no conflict with other rules.</LI>
</UL>



<H3><A name="Prefetching-DisjointPrefetches"></A>Disjoint Prefetches</H3>

<P><EM>(available since 1.0)</EM></P>

<P>&quot;Disjoint&quot; prefetches (aka &quot;normal prefetches&quot;, as this is how Cayenne implemented prefetching since 1.0) internally result in a separate SQL statement per prefetch path.</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
SelectQuery query = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class);

<SPAN class="code-comment">// <SPAN class="code-quote">&quot;disjoint&quot;</SPAN> is <SPAN class="code-keyword">default</SPAN> semantics of SelectQuery
</SPAN>query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray&quot;</SPAN>); 
query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray.toGallery&quot;</SPAN>);

<SPAN class="code-comment">// <SPAN class="code-keyword">this</SPAN> will result in 1 main SQL query plus 2 extra prefetch queries
</SPAN>context.performQuery(query);
</PRE>
</DIV></DIV>

<H3><A name="Prefetching-JointPrefetches"></A>Joint Prefetches</H3>

<P><EM>(available since 1.2M3, with significant API changes in 1.2M8)</EM></P>

<P>&quot;Joint&quot; is prefetch type that issues a single SQL statement for multiple prefetch paths. Cayenne processes in memory a cartesian product of the entities involved, converting it to an object tree. SQLTemplate and ProcedureQuery create joint prefetches by default. SelectQuery needs to be told to use joint prefetch:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java">
<SPAN class="code-comment">// after adding a <SPAN class="code-keyword">new</SPAN> prefetch, change its semantics to joint
</SPAN>query.addPrefetch(<SPAN class="code-quote">&quot;paintingArray&quot;</SPAN>).setSemantics(
                PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);

context.performQuery(query);
</PRE>
</DIV></DIV>

<P>Code above will result in a single SQL statement issued.</P>