AutoFetch

• Kudos to Ali Ibrahim
• What is AutoFetch?
• Problem 1 - Developers too busy to tune queries
• Problem 2 - DAO Pattern, Leaky Abstraction

Kudos to Ali Ibrahim

Visit Ali's AutoFetch home page here.

What is AutoFetch?

In short, instead of manually specifying joins and properties to fetch on a ORM query AutoFetch can do this for you based on profiling past object graph usage.

That is, ORM queries return object graphs. These object graphs are profiled collecting information such as the nodes and properties on those nodes that are actually used by the application. This information is then used to automatically tune future executions of the same query.

Ali is working on a Hibernate implementation and I have built AutoFetch into Ebean 0.9.7. The code examples you see are Ebean specific but the idea applies equally to all ORM's.

Problem 1 - Developers too busy to tune queries

A developer can tune their application by controlling the query. Specifically by controlling the properties and joins that should be used in a query to get the data they need and no more.

// a tuned query
public Order findOrder(Integer id){
  
  Query<Order> query = Ebean.createQuery(Order.class);
  query.setId(id);

  // we only want some of the properties of the order
  query.select("id, status, shippingDate");

  // join to the customer but we only want their name
  query.join("customer","name");

  // we do want all the properties on the order detail
  query.join("details");

  // for each order detail get the product name and sku
  query.join("details.product", "name, sku");

  return query.findUnique();
}

The above is a query that is tuned by using select() and join() to get all the information required in a single query but no more than that.

A busy developer many not bother tuning the query at all. As a result they may feel the performance effects of a lot of "lazy loading" queries. In a more subtle fashion they may feel a performance hit from fetching all the properties of a bean when they really only needed a few properties (especially unnecessary clob fetching).

Without some tuning the database queries can be significantly more expensive due to lots of lazy loading or fetching more data than is actually required. The problem for a developer looking to tune a query is that they need to know/determine/guess the data required by the application so that they can specify the appropriate select() and joins(). For a large application this can be a significant amount of work.

Problem 1 - solution

AutoFetch will build the select() and join() parts of the query automatically for you. It will do this based on profiled usage patterns and will automatically MAINTAIN itself if the usage patterns change (automatically add/remove properties and joins).

Problem 2 - DAO Pattern, Leaky Abstraction

There is a problem with the DAO Pattern that AutoFetch solves so let's look at an example.

customerDao.findById(Integer id); A DAO Developer builds a customer find by id method and there are 2 developers that want to use this method.

Developer 1 really just needs the customers name and status.

Developer 2 wants all the properties on the customer and the customer's shipping and billing address.

In running the application it is decided there is a performance problem. The query executed is not suited for use in either of the two cases. The DAO Developer wants to tune the query to perform better and so he decides to provide 2 methods tuned for use by each of the developers.

// tuned for use by Developer 1
public Customer findByIdWithName(Integer id){
  Query<Customer> query = Ebean.createQuery(Customer.class);
  query.setId(id);
  // just select the customer id and name
  query.select("id,name");
  return query.findUnique();
}

// tuned for use by Developer 2
public Customer findByIdWithAddresses(Integer id){
  Query<Customer> query = Ebean.createQuery(Customer.class);
  query.setId(id);
  // fetch join the shipping and billing address
  query.join("shippingAddress");
  query.join("billingAddress");
  return query.findUnique();
}

Now what happens if there are other use cases that use other completely different parts of a Customer? Should we continue adding a method for each situation? Leaky abstraction perhaps? Is the implementation detail of the customerDao leaking back up into the application code?

This issue becomes more of a problem as the application gets larger and performance more critical.

Problem 2 - solution

With AutoFetch a single query can be tuned different according to the call stack that was used to call the query. For example it can tune the query for both use cases differently giving optimal query performance for both developer 1 and developer 2.

NEXT: Using AutoFetch with Ebean