Prefetching is a technique that allows to bring back in one query not only the queried objects, but also objects related to them. In other words it is a controlled eager relationship resolving mechanism. Prefetching is discussed in the "Performance Tuning" chapter, as it is a powerful performance optimization method. However another common application of prefetching is to refresh stale object relationships, so more generally it can be viewed as a technique for managing subsets of the object graph.

Prefetching example:

ObjectSelect<Artist> query = ObjectSelect.query(Artist.class);

// instructs Cayenne to prefetch one of Artist's relationships
query.prefetch(Artist.PAINTINGS.disjoint());

// the above line is equivalent to the following: 
// query.prefetch("paintings", PrefetchTreeNode.DISJOINT_PREFETCH_SEMANTICS);

// query is expecuted as usual, but the resulting Artists will have
// their paintings "inflated"
List<Artist> artists = query.select(context);

All types of relationships can be preftetched - to-one, to-many, flattened. A prefetch can span multiple relationships:

query.prefetch(Artist.PAINTINGS.dot(Painting.GALLERY).disjoint());

A query can have multiple prefetches:

query.prefetch(Artist.PAINTINGS.disjoint());
query.prefetch(Artist.PAINTINGS.dot(Painting.GALLERY).disjoint());

If a query is fetching DataRows, all "disjoint" prefetches are ignored, only "joint" prefetches are executed (see prefetching semantics discussion below for what disjoint and joint prefetches mean).

PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS
PrefetchTreeNode.DISJOINT_PREFETCH_SEMANTICS
PrefetchTreeNode.DISJOINT_BY_ID_PREFETCH_SEMANTICS

SELECT a FROM Artist a LEFT JOIN FETCH a.paintings

Converting result set data to Persistent objects and registering these objects in the ObjectContext can be an expensive operation compareable to the time spent running the query (and frequently exceeding it). Internally Cayenne builds the result as a list of DataRows, that are later converted to objects. Skipping the last step and using data in the form of DataRows can significantly increase performance.

DataRow is a simply a map of values keyed by their DB column name. It is a ubiqutous representation of DB data used internally by Cayenne. And it can be quite usable as is in the application in many cases. So performance sensitive selects should consider DataRows - it saves memory and CPU cycles. All selecting queries support DataRows option, e.g.:

ObjectSelect<DataRow> query = ObjectSelect.dataRowQuery(Artist.class);

List<DataRow> rows = query.select(context);

SQLSelect<DataRow> query = SQLSelect.dataRowQuery("SELECT * FROM ARTIST");
List<DataRow> rows = query.select(context);

Individual DataRows may be converted to Persistent objects as needed. So e.g. you may implement some in-memory filtering, only converting a subset of fetched objects:

// you need to cast ObjectContext to DataContext to get access to 'objectFromDataRow'
DataContext dataContext = (DataContext) context;

for(DataRow row : rows) {
    if(row.get("DATE_OF_BIRTH") != null) {
        Artist artist = dataContext.objectFromDataRow(Artist.class, row);
        // do something with Artist...
        ...
    }
}

It is possible to fetch specific attributes and relationships from a model using EJBQLQuery. The following example would return a java.util.List of String objects;

SELECT a.name FROM Artist a

The following will yield a java.util.List containing Object[] instances, each of which would contain the name followed by the dateOfBirth value.

SELECT a.name, a.dateOfBirth FROM Artist a

Refer to third-party query language documentation for further detail on this mechanism.

While contemporary hardware may easily allow applications to fetch hundreds of thousands or even millions of objects into memory, it doesn't mean this is always a good idea to do so. You can optimize processing of very large result sets with two techniques discussed in this and the following chapter - iterated and paginated queries.

Iterated query is not actually a special query. Any selecting query can be executed in iterated mode by an ObjectContext. ObjectContext creates an object called ResultIterator that is backed by an open ResultSet. Iterator provides constant memory performance for arbitrarily large ResultSets. This is true at least on the Cayenne end, as JDBC driver may still decide to bring the entire ResultSet into the JVM memory.

Data is read from ResultIterator one row/object at a time until it is exhausted. There are two styles of accessing ResultIterator - direct access which requires explicit closing to avoid JDBC resources leak, or a callback that lets Cayenne handle resource management. In both cases iteration can be performed using "for" loop, as ResultIterator is "Iterable".

Direct access. Here common sense tells us that ResultIterators instances should be processed and closed as soon as possible to release the DB connection. E.g. storing open iterators between HTTP requests for unpredictable length of time would quickly exhaust the connection pool.

try(ResultIterator<Artist> it = ObjectSelect.query(Artist.class).iterator(context)) {
    for(Artist a : it) {
       // do something with the object...
       ...
    }           
}

Same thing with a callback:

ObjectSelect.query(Artist.class).iterate(context, (Artist a) -> {
    // do something with the object...
    ...
});

Another example is a batch iterator that allows to process more than one object in each iteration. This is a common scenario in various data processing jobs - read a batch of objects, process them, commit the results, and then repeat. This allows to further optimize processing (e.g. by avoiding frequent commits).

try(ResultBatchIterator<Artist> it = ObjectSelect.query(Artist.class).batchIterator(context, 100)) {
    for(List<Artist> list : it) {
       // do something with each list
       ...
       // possibly commit your changes
       context.commitChanges();
    }           
}

Enabling query pagination allows to load very large result sets in a Java app with very little memory overhead (much smaller than even the DataRows option discussed above). Moreover it is completely transparent to the application - a user gets what appears to be a list of Persistent objects - there's no iterator to close or DataRows to convert to objects:

// the fact that result is paginated is transparent
List<Artist> artists = 
    ObjectSelect.query(Artist.class).pageSize(50).select(context);

Having said that, DataRows option can be combined with pagination, providing the best of both worlds:

List<DataRow> rows = 
    ObjectSelect.dataRowQuery(Artist.class).pageSize(50).select(context);

The way pagination works internally, it first fetches a list of IDs for the root entity of the query. This is very fast and initially takes very little memory. Then when an object is requested at an arbitrary index in the list, this object and adjacent objects (a "page" of objects that is determined by the query pageSize parameter) are fetched together by ID. Subsequent requests to the objects of this "page" are served from memory.

An obvious limitation of pagination is that if you eventually access all objects in the list, the memory use will end up being the same as with no pagination. However it is still a very useful approach. With some lists (e.g. multi-page search results) only a few top objects are normally accessed. At the same time pagination allows to estimate the full list size without fetching all the objects. And again - it is completely transparent and looks like a normal query.

By default when a single ObjectContext commits its changes, all other contexts in the same runtime receive an event that contains all the committed changes. This allows them to update their cached object state to match the latest committed data. There are however many problems with this ostensibly helpful feature. In short - it works well in environments with few contexts and in unclustered scenarios, such as single user desktop applications, or simple webapps with only a few users. More specifically:

So we've made a good case for disabling synchronization in most webapps. To do that, set to "false" the following DI property - Constants.SERVER_CONTEXTS_SYNC_PROPERTY, using one of the standard Cayenne DI approaches. E.g. from command line:

$ java -Dcayenne.server.contexts_sync_strategy=false

Or by changing the standard properties Map in a custom extensions module:

public class MyModule implements Module {

    @Override
    public void configure(Binder binder) {
        binder.bindMap(Constants.PROPERTIES_MAP).put(Constants.SERVER_CONTEXTS_SYNC_PROPERTY, "false");
    }
}