OracleAS TopLink Reading Optimization Features

Example 10-8 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 }

Example 10-9 Optimization Through Joining

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

// Read all the employees from the database, using joining. 
// This requires 1 SQL call.
ReadAllQuery query = new ReadAllQuery();
query.setReferenceClass(Employee.class);
query.setSelectionCriteria(new    ExpressionBuilder().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());

Example 10-10 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 only takes 
   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();
query.setReferenceClass(Employee.class);
query.setSelectionCriteria(new    ExpressionBuilder().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 10-9 and Example 10-10) accesses the database only twice, it is significantly faster than the approach illustrated in Example 10-8.

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

For example, if employees in Example 10-8, Example 10-9, and Example 10-10 live 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 also available for one-to-many relationships, but joining is available only for one-to-one relationships.

Example 10-11 No Optimization

/* Gather the information to report on an employee and return the summary of the 
   information. In this situation a hashtable is used to hold the report 
   information. Notice that this reads a lot of objects from the database, but 
   uses very little of the information contained in the objects. This may take 5 
   queries and read in a large number of objects.
*/

public Hashtable reportOnEmployee(String employeeName)
    {
    Vector projects, associations;
    Hashtable report = new Hashtable();
    // Retrieve employee from database.
    Employee employee = session.readObject(Employee.class, new
       ExpressionBuilder.get("lastName").equal(employeeName)); 
    // Get all the projects affiliated with the employee.
    projects = session.readAllObjects(Project.class, "SELECT P.* FROM PROJECT P, 
      EMPLOYEE E WHERE P.MEMBER_ID = E.EMP_ID AND E.L_NAME = " + employeeName);
    // Get all the associations affiliated with the employee.
    associations = session.readAllObjects(Association.class, "SELECT A.*
      FROM ASSOC A, EMPLOYEE E WHERE A.MEMBER_ID = E.EMP_ID AND E.L_NAME = 
      " + employeeName);
}
    report.put("firstName", employee.getFirstName());
    report.put("lastName", employee.getLastName());
    report.put("manager", employee.getManager());
    report.put("city", employee.getAddress().getCity());
    report.put("projects", projects);
    report.put("associations", associations);
    return report;
}

Example 10-12 Optimization Through View Object

CREATE VIEW NAMED EMPLOYEE_VIEW AS (SELECT F_NAME = E.F_NAME, L_NAME = E.L_NAME,EMP_ID = E.EMP_ID, MANAGER_NAME = E.NAME, CITY = A.CITY, NAME = E.NAME 
FROM EMPLOYEE E, EMPLOYEE M, ADDRESS A 
WHERE E.MANAGER_ID = M.EMP_ID
AND E.ADDRESS_ID = A.ADDRESS_ID)

Example 10-13 View the Report from Example 10-12

/* Return the report for the employee.*/
public EmployeeReport reportOnEmployee(String employeeName) 
{
    EmployeeReport report;
    report = (EmployeeReport) session.readObject(EmployeeReport.class, 
      new ExpressionBuilder.get("lastName").equal(employeeName));
    return report;}