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.
A solution offered by Cayenne is "paginated" queries. A user can set a size of a "page" of a select query. If page size is greater than zero, on query execution Cayenne will only fetch the first "page" 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.
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.
Paged query example:
|Combining data rows and paginated queries|
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.