Queries in Ebean

Autofetch - Automatic query tuning

Ebean can automatically tune your queries for you using a feature called "Autofetch".

This has the benefit of executing better performing queries that and reduced lazy loading.

example
// ----
// a program that fetches some orders ... and processes them
// ----

// fetch new orders 
List<Order> orders = 
        Ebean.find(Order.class)
                .where().eq("status", Order.Status.NEW)         
                .findList();

// just read the orderDate
for (Order order : orders) {
        Date orderDate = order.getOrderDate();
}

Initially Ebean has no "profiling" information so when the program is first run the query executed is not optimal.

<sql summary='[app.data.test.Order]'>
select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id 
from or_order o 
where o.status_code = ? 
<sql>

However, now that we have run the program once Ebean has collected some profiling information. Specifically it knows that for this query (based on the call stack) the program only reads the orderDate. So when we run the program a 2nd time...

<sql summary='[app.data.test.Order] autoFetchTuned[true]'>
select o.id, o.order_date, o.updtime 
from or_order o 
where o.status_code = ?  
<sql>

You will note the autoFetchTuned[true] on the output. This indicates that this query was tuned via Autofetch. In this case, the profiling indicated that only the orderDate property was used so that, along with the Id property and the version column are the only properties fetched.

example

Now lets change the program. We will use another property of order and we will also get the customers name.

List<Order> orders = 
        Ebean.find(Order.class)
                .where().eq("status", Order.Status.NEW)         
                .findList();

for (Order order : orders) {
        Date orderDate = order.getOrderDate();
        // also get the ship date
        Date shipDate = order.getShipDate();
        // ... and get the customer name
        Customer customer = order.getCustomer();
        String customerName = customer.getName();
}

The query Ebean will run is optimised for what the program USED to do. This results in some lazy loading occuring. The rest of the order is lazy loaded when the shipDate is read... and then the customer is lazy loaded when the customer name is read.

<sql summary='[app.data.test.Order] autoFetchTuned[true]'>
select o.id, o.order_date, o.updtime 
from or_order o 
where o.status_code = ? 
</sql>
-- LAZY LOADING: ... due to reading shipDate
<sql summary='[app.data.test.Order]'>
select o.id, o.order_date, o.ship_date, o.cretime, o.updtime, o.status_code, o.customer_id 
from or_order o
where o.id = ?  
</sql>
-- LAZY LOADING: ... due to reading customers name
<sql summary='[app.data.test.Customer]'>
select c.id, c.name, c.cretime, c.updtime, c.billing_address_id, c.status_code, c.shipping_address_id 
from or_customer c
where c.id = ?  
<sql>

So the above queries are not so good. However, Ebean has now updated its profiling information so when we run the program again...

<sql summary='[app.data.test.Order, customer] autoFetchTuned[true]'>
select o.id, o.order_date, o.updtime, o.ship_date
        , c.id, c.name, c.updtime 
from or_order o
join or_customer c on o.customer_id = c.id  
where o.status_code = ? 
<sql>

... now Ebean has added the shipDate and a join to customer fetching the customer name.

So, if you turn on autoFetch Ebean can optimise your queries based on how the object graphs are actually used by the application. The developer has less work by not having to define joins and you get the performance benefits of partial objects with no work - nice.

Autofetch can continue to update its profiling information so that when your application changes the queries will automatically be tuned to suit.

Introduction User Guide (pdf) Install/Configure Public JavaDoc Whitepapers
General Database Specific Byte Code Deployment Annotations Features
Top Bugs Top Enhancements
woResponse