Batching

• Transparent Batching
• Batching of UpdateSql and CallableSql
• Batching of Beans
• setBatchGetGeneratedKeys

I am expecting you to be familar with how Cascading Save and Delete work prior to reading this document.

In JDBC v3 statement batching was added to the API. The general benefit of statement batching is to improve performance by reducing the number of network roundtrips.

As an example say you saved an Order with 10 detail lines this could be processed by executing 11 statements, or by batching the 10 details into a single 'batched' statement this could be processed in 2 statements.

In general there is a potential for a very significant performance improvement by using preparedStatement batching.

For people using a driver/database that supports getGeneratedKeys batching can be made transparent (always on). For use with a driver/database that does NOT support getGeneratedKeys (e.g. Oracle9) Developers will have to be more savvy as to what is actually happening and may specifically look to use more relational approaches for very large insert batching.

Transparent Batching

If your driver/database supportGetGeneratedKeys then you are in luck. You can get Ebean to use statement batching all the time transparently.

#
# In system.properties set ebean.batch.mode=true 
# for transparent batching
#
ebean.batch.mode=true

# default batch size is 20
#ebean.batch.size=20

You can look in the transaction logs and should see the batching occur.

Batching of UpdateSql and CallableSql

It is important to note is that both UpdateSql and CallableSql are designed to be reused by just setting new parameters and then called execute again. This is not the case for Beans.

 String data = "This is a simple test of the batch processing"
                + " mode and the transaction execute batch method";
 
 String[] da = data.split(" ");
 
 String sql = "{call sp_t3(?,?)}";
 
 Timestamp now = new Timestamp(System.currentTimeMillis());

 CallableSql cs = new CallableSql(sql);
 cs.setParameter(2, now);
 
 // (optional) inform eBean this stored procedure
 // inserts into a table called my_test_table
 cs.addModification("my_test_table", true, false, false);
 
 Transaction t = Ebean.beginTransaction();
 t.setBatchMode(true);
 t.setBatchSize(3);
 try {
    for (int i = 0; i < da.length;) {
       cs.setParameter(1, da[i]);
       Ebean.execute(cs);
    }
 
    // NB: commit implicitly flushes  
    Ebean.commitTransaction();
 
 } finally {
    Ebean.endTransaction();
 }

In this example we explicitly set the transaction to use batching and set the batch size to 3. The other interesting note is that we explicitly inform Ebean that we inserted into the "my_test_table" and Ebean uses this information to invalidate its cache (and Lucene Indexes) as appropriate.

Note: Beans are queued according to depth but UpdateSql and CallableSql do not require that mechanism. They are bound to PreparedStatements immediately and are thus able to be reused by setting new parameters and then calling execute again.

Note: CallableSql can have OUT parameters but when these are executed in batch you are not able to get the OUT parameters back. Some JDBC drivers do not allow you to batch CallableStatements that have OUT parameters.

Note: When transparent batching is on you do not need to explicitly set the batch mode or batch size on the transaction.

Batching of Beans

What should be noted for Beans is that the beans are queued according to depth.

This is probably best explained by the example describing the cascading batch order.

• Depth -2: All the billing addresses and shipping addresses
• Depth -1: All the customers
• Depth 0: All the orders
• Depth 1: All the order details

setBatchGetGeneratedKeys

The Transaction also has the method setBatchGetGeneratedKeys() for turning off the getting of the generated keys (if its supported). This is there for the case when for performance considerations you may choose to not bother getting back the generated keys.

When you do not get back the generated keys then you can not then modify the beans and save them getting an update. That is because they don't have their Id values.