Skip Headers

# Reading Case 2: Batch Reading Objects

Previous

Next

The way your application reads data from the database affects performance. For example, reading a collection of rows from the database is significantly faster than reading each row individually.

A common performance challenge is to read a collection of objects that have a one-to-one reference to another object. This typically requires one read operation to read in the source rows, and one call for each target row in the one-to-one relationship.

To reduce the number of read operations required, use join and batch reading. Example: No Optimization illustrates the unoptimized code required to retrieve a collection of objects with a one-to-one reference to another object. Example: Optimization Through Joining and Example: Optimization Through Batch Reading illustrate the use of joins and batch reading to improve efficiency.

No Optimization

    /* Read all the employees, and collect their address' cities. This takes N + 1
       queries if not optimized */

    // Read all the employees from the database. This requires 1 SQL call
    Vector employees = session.readAllObjects(Employee.class,
       new ExpressionBuilder().get("lastName").equal("Smith"));

    //SQL: Select * from Employee where l_name = 'Smith'

    // Iterate over employees and get their addresses.
    // This requires N SQL calls
    Enumeration enum = employees.elements();
    Vector cities = new Vector();
    while(enum.hasMoreElements())
       Employee employee = (Employee) enum.nextElement();
       cities.addElement(employee.getAddress().getCity());

    //SQL: Select * from Address where address_id = 123, etc
    }

Optimization Through Joining

    /* Read all the employees, and collect their address' cities. Although the code
       is almost identical because joining optimization is used it takes only 1
       query */

    // Read all the employees from the database, using joining.
    // This requires 1 SQL call
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCriteria(builder.get("lastName").equal("Smith"));
    query.addJoinedAttribute("address");
    Vector employees = session.executeQuery(query);

    /* SQL: Select E.*, A.* from Employee E, Address A where E.l_name = 'Smith' and
       E.address_id = A.address_id Iterate over employees and get their addresses.
       The previous SQL already read all the addresses, so no SQL is required */
    Enumeration enum = employees.elements();
    Vector cities = new Vector();
    while (enum.hasMoreElements()) {
        Employee employee = (Employee) enum.nextElement();
        cities.addElement(employee.getAddress().getCity());
    }

Optimization Through Batch Reading

    /* Read all the employees, and collect their address' cities. Although the code
       is almost identical because batch reading optimization is used it takes only
       2 queries */

    // Read all the employees from the database, using batch reading.
    // This requires 1 SQL call, note that only the employees are read
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCriteria(bulder.get("lastName").equal("Smith"));
    query.addBatchReadAttribute("address");
    Vector employees = (Vector)session.executeQuery(query);

    // SQL: Select * from Employee where l_name = 'Smith'

    // Iterate over employees and get their addresses.
    // The first address accessed will cause all the addresses
    // to be read in a single SQL call
    Enumeration enum = employees.elements();
    Vector cities = new Vector();
    while (enum.hasMoreElements()) {
        Employee employee = (Employee) enum.nextElement();
        cities.addElement(employee.getAddress().getCity());
        // SQL: Select distinct A.* from Employee E, Address A
        // where E.l_name = 'Smith' and E.address_id = A.address_i
    }

Because the two-phase approach to the query (Example: Optimization Through Joining and Example: Optimization Through Batch Reading) accesses the database only twice, it is significantly faster than the approach illustrated in Example: No Optimization.

Joins offer a significant performance increase under most circumstances. Batch reading offers a further performance advantage in that it allows for delayed loading through value holders, and has much better performance where the target objects are shared.

For example, if employees in Example: No Optimization, Example: Optimization Through Joining, and Example: Optimization Through Batch Reading are at the same address, batch reading reads much less data than joining, because batch reading uses a SQL `DISTINCT` call to filter duplicate data.

Batch reading is available for one-to-one, one-to-many, many-to-many, direct collection, direct map and aggregate collection mappings. Joining is only available for one-to-one and one-to-many mappings. Note that one-to-many joining will return a large amount of duplicate data and so is normally less efficient than batch reading.

WARNING: Allowing an unverified SQL string to be passed into methods (for example: `readAllObjects(Class class, String sql)` method) makes your application vulnerable to SQL injection attacks.