Skip Headers

# Writing Case: Batch Writes

Previous

Next

The most common write performance problem occurs when a batch job inserts a large volume of data into the database. For example, consider a batch job that loads a large amount of data from one database, and then migrates the data into another. The objects involved:

- Are simple individual objects with no relationships

- Use generated sequence numbers as their primary key

- Have an address that also uses a sequence number

The batch job loads 10,000 employee records from the first database and inserts them into the target database. With no optimization, the batch job reads all the records from the source database, acquires a Unit of Work from the target database, registers all objects, and commits the Unit of Work.

No Optimization

    /* Read all the employees, acquire a Unit of Work, and register them */

    // Read all the employees from the database. This requires 1 SQL call,
    // but will be very memory intensive as 10,000 objects will be read
    Vector employees = sourceSession.readAllObjects(Employee.class);

    //SQL: Select * from Employee

    // Acquire a Unit of Work and register the employees
    UnitOfWork uow = targetSession.acquireUnitOfWork();
    uow.registerAllObjects(employees);
    uow.commit();

    // SQL: Begin transaction
    // SQL: Update Sequence set count = count + 1 where name = 'EMP'
    // SQL: Select count from Sequence
    // SQL: ... repeat this 10,000 times + 10,000 times for the addresses ...
    // SQL: Commit transaction
    // SQL: Begin transaction
    // SQL: Insert into Address (...) values (...)
    // SQL: ... repeat this 10,000 times
    // SQL: Insert into Employee (...) values (...)
    // SQL: ... repeat this 10,000 times
    // SQL: Commit transaction

This batch job performs poorly, because it requires 60,000 SQL executions. It also reads huge amounts of data into memory, which can raise memory performance issues. TopLink offers several optimization features to improve the performance of this batch job.

To improve this operation, do the following:

- Use TopLink batch read operations and cursor support (see "Cursors").

- Use batch writing or parameterized batch writing to write to the database (see "Batch Writing and Parameterized SQL").

  If your database does not support batch writing, use parameterized SQL to implement the write query.

- Implement sequence number preallocation (see "Sequence Number Preallocation").

- Implement multiprocessing (see "Multiprocessing").