Introduction to Prefetching

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 "prefetched" entity. E.g.:

// configure query with prefetches
SelectQuery query = new SelectQuery(Artist.class);
query.addPrefetch("paintingArray"); 
...
// execute query and do something with results
List artists = context.performQuery(query);
Iterator it = artists.iterator();
while(it.hasNext()) {
  Artist a = (Artist) it.next();
  System.out.println("paintings: " + a.getPaintingArray().size());
}

When prefetching is set, corresponding relationships are "inflated" with database objects within a single performQuery 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 1 + N queries, where N is the number of artists returned.

Prefetching Hints

  • All types of relationships can be prefetched - to-one, to-many, flattened.
  • A prefetch can span more than one relationship: 
    query.addPrefetch("paintingArray.toGallery");
  • A query can have more than one prefetch path at the same time: 
    query.addPrefetch("paintingArray"); 
query.addPrefetch("paintingArray.toGallery");
  • If SelectQuery is fetching data rows, all default prefetches are ignored, though custom joint prefetches (see below) will be included.

The rest of this page describes advanced use and can be skipped.

Prefetch Semantics

Queries store prefetching information as trees of PrefetchTreeNode objects:

PrefetchTreeNode treeRoot = query.getPrefetchTree();
if(treeRoot != null) {
  // do something with tree nodes
}

Each node specifies the name of prefetch path segment and execution semantics. There are two flavors of prefetch semantics - joint and disjoint. 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).

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.

Some internal semantics rules:

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

Disjoint Prefetches

"Disjoint" prefetches (aka "normal prefetches", as this is how Cayenne implemented prefetching since 1.0) internally result in a separate SQL statement per prefetch path.

SelectQuery query = new SelectQuery(Artist.class);

// "disjoint" is default semantics of SelectQuery
query.addPrefetch("paintingArray"); 
query.addPrefetch("paintingArray.toGallery");

// this will result in 1 main SQL query plus 2 extra prefetch queries
context.performQuery(query);

Joint Prefetches

"Joint" 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:

// after adding a new prefetch, change its semantics to joint
query.addPrefetch("paintingArray").setSemantics(
                PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);

context.performQuery(query);

Code above will result in a single SQL statement issued. OUTER joins will be used for this type of prefetch. Specifics of the column naming when using prefetching with SQLTemplate are discussed here.