Title: Prefetching

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");

PREFETCH LIMITATION: 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.:
```
SelectQuery query = new SelectQuery(Artist.class);

Expression exp = ExpressionFactory.matchExp("paintingArray.paintingTitle", "Some Painting");

// INVALID!! since there can be more than one painting per artist, this prefetch
// wouldn't work.
query.addPrefetch("paintingArray"); 
```
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.
If SelectQuery is fetching data rows, all default prefetches are ignored, though custom joint prefetches (see below) will be included.
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.

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

Prefetch Semantics

(semantics flavors were introduced in 1.2M3, with some changes in 1.2M8)

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

(available since 1.0)

"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

(available since 1.2M3, with significant API changes in 1.2M8)

"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.