docs/1.2/paginated-queries.html

Title: Paginated Queries

	

<P>Another common situation found in interactive applications is when it is needed to fetch a large number of rows, and at the same time only a small subset of objects is accessed directly. An example of such application would be a search page. User would like to see how many total results exist that match a certain criteria, but she will only look at a few pages of results out of possibly hundreds or thousands. It will be extremely inefficient to fetch all objects in the memory.</P>

<P>A solution offered by Cayenne is &quot;paginated&quot; queries. A user can set a size of a &quot;page&quot; of a select query. If page size is greater than zero, on query execution Cayenne will only fetch the first &quot;page&quot; as DataObjects. For the rest of the result set, only primary keys are read. When a user accesses an object that has not been resolved yet, the whole page containing this object will be resolved all at once. Most important, this happens totally transparent to the user.</P>

<P>Our tests show almost an order of magnitude speed increase when comparing the time it takes to read a full list of 2000 objects, and the time it takes to do the initial paginated query fetch with page size of 50.</P>

<P>Paged query example:</P>

<DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent">
<PRE class="code-java"><SPAN class="code-keyword">import</SPAN> java.util.List;
<SPAN class="code-keyword">import</SPAN> java.util.Map;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.access.DataContext;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.cayenne.query.SelectQuery;
<SPAN class="code-keyword">import</SPAN> org.objectstyle.art.Artist;
...
DataContext ctxt;

<SPAN class="code-comment">// create a query returning data rows
</SPAN>SelectQuery q = <SPAN class="code-keyword">new</SPAN> SelectQuery(Artist.class);
q.setPageSize(50);

<SPAN class="code-comment">// the fact that result is paged is transparent
</SPAN>List artistRows = ctxt.performQuery(q);


<SPAN class="code-comment">// we are reading from the first page (index &lt; 50), 
</SPAN><SPAN class="code-comment">// <SPAN class="code-keyword">this</SPAN> will simply <SPAN class="code-keyword">return</SPAN> an object
</SPAN>Artist artist1 = (Artist)artistRows.get(3);

<SPAN class="code-comment">// we are reading from the 4th page, 
</SPAN><SPAN class="code-comment">// <SPAN class="code-keyword">this</SPAN> will transparently resolve all objects on <SPAN class="code-quote">&quot;Page #4&quot;</SPAN>
</SPAN><SPAN class="code-comment">// and then <SPAN class="code-keyword">return</SPAN> an object in question
</SPAN>Artist artist2 = (Artist)artistRows.get(153);


<SPAN class="code-comment">// This is safe and will NOT trigger a full fetch
</SPAN><SPAN class="code-object">int</SPAN> size = artistRows.size();
...
</PRE>
</DIV></DIV>

<DIV class="panelMacro"><TABLE class="tipMacro"><COLGROUP><COL width="24"><COL></COLGROUP><TR><TD valign="top"><IMG src="http://cayenne.apache.org/docs/1.2/images/icons/emoticons/check.gif" width="16" height="16" align="absmiddle" alt="" border="0"></TD><TD><B>Combining data rows and paginated queries</B><BR>Cayenne supports combining both performance optimizations in the same query - fetching data rows (see previous chapters) and paginated queries. So if users work with tabular data and don't care much about real objects, combining the two approaches would improve speed and memory use even more.</TD></TR></TABLE></DIV>