Query Object Performance Options

Batch Reading

Batch reading propagates query selection criteria through the relationship attribute mappings of an object. You can also nest batch reads down through complex object graphs. This significantly reduces the number of required SQL select statements and improves database access efficiency.

For example, in reading n employees and their related projects, OracleAS TopLink may require n + 1 selects. All employees are read at once, but the projects of each are read individually. With batch reading, all related projects can also be read with one select by using the original selection criteria, for a total of only 2 selects.

To implement batch reading, use one of the following methods:

• To add the batch read attribute to a query, use the query.addBatchReadAttribute(Expression anExpression) API.

  For example:

  …
  ReadAllQuery raq = new ReadAllQuery(Trade.class);
  ExpressionBuilder tradeBuilder = raq.getBuilder();
  …
  Expression batchReadProduct = tradeBuilder.get("product");
  readAllQuery.addBatchReadAttribute(batchReadProduct); 
  Expression batchReadPricingDetails = batchReadProduct.get("pricingDetails");
  readAllQuery.addBatchReadAttribute(batchReadPricingDetails); 
  …
  
  

• Add batch reading at the mapping level for a descriptor. Use either OracleAS TopLink Mapping Workbench or a descriptor amendment method to add the setUsesBatchReading() API on the relationship mappings of the descriptor.

  For example:

  public static void amendTradeDescriptor(Descriptor theDescriptor) {
      OneToOneMapping productOneToOneMapping =        theDescriptor.getMappingForAttributeName("product");
      productOneToOneMapping.setUsesBatchReading(true);
  }
  
  

You can combine batch reading and indirection to provide controlled reading of object attributes. For example, if you have one-to-one backpointer relationship attributes, you can defer backpointer instantiation until the end of the query, when all parent and owning objects are instantiated. This prevents unnecessary database access and optimizes OracleAS TopLink cache use.

Join Reading

When OracleAS TopLink queries, it can use joins to check values from other objects or other tables that represent parts of the same object. Although this works well under most circumstances, it can cause problems when you query against a one-to-one relationship in which one side of the relationship is not present.

For example, Employee objects may have an Address object, but if the Address is unknown, it is null at the object level and has a null foreign key at the database level. When you attempt a read that traverses the relationship, missing objects cause the query to return unexpected results. Consider the expression:

(emp.get("firstName").equal("Steve")).or(emp.get("address"). get("city").equal("Ottawa"))

In this case, employees with no address do not appear in the result set, regardless of their first name. Although not obvious at the object level, this behavior is fundamental to the nature of relational databases.

Outer joins rectify this problem in the databases that support them. In this example, the use of an outer join provides the expected result: all employees named Steve appear in the result set, even if their address is unknown.

To implement an outer join, use getAllowingNull() rather than get(), and anyOfAllowingNone() rather than anyOf().

For example:

(emp.get("firstName").equal("Steve")).or
  (emp.getAllowingNull ("address").get("city").equal("Ottawa"))

Support and syntax for outer joins vary widely between databases and database drivers. OracleAS TopLink supports outer joins for Oracle databases, IBM DB2, SQL Anywhere, Microsoft Access, Microsoft SQL Server, Sybase SQL Server, and the JDBC outer join syntax. Of these, only Oracle supports the outer join semantics in or clauses.

You can also use outer joins with ordering.

For more information, see "Ordering for Read All Queries" .

Join reading enables you to read data from a one-to-one mapping in conjunction with data from the original query. Join reading is available only for one-to-one mappings. To implement join reading, use either of the following methods:

• To add the joined attribute to the query at the query level, use the Query.addJoinedAttribute(Expression anExpression) API.

  For example:

  …
  ReadAllQuery raq = new ReadAllQuery(Trade.class);
  ExpressionBuilder tradeBuilder = raq.getBuilder();
  …
  Expression portfolio = tradeBuilder.get("portfolio");
  readAllQuery.addJoinedAttribute(portfolio); 
  …
  
  

• Use OracleAS TopLink Mapping Workbench or a descriptor amendment method to invoke the setUsesJoining() API on the OneToOneMapping class, as follows:

  public static void amendTradeDescriptor(Descriptor theDescriptor) {
      OneToOneMapping portfolioOneToOneMapping = 
        theDescriptor.getMappingForAttributeName("portfolio");
      portfolioOneToOneMapping.setUsesJoining(true);
  }
  
  

For more information about joins as a performance tool, see Chapter 10, "Tuning for Performance".

ReportQuery

Report query enables you to retrieve data from a set of objects and their related objects. Report query supports database reporting functions and features. Although the report query returns data rather than objects, it still enables you to query and specify the data at the object level.

The ReportQuery API returns a collection of ReportQueryResult objects, similar in structure and behavior to a DatabaseRow or a Map.

Report query allows you to:

• Specify a subset of the attributes of an object and the attributes of its related object, which allows you to query for lightweight information.

• Build complex object-level expressions for the selection criteria and ordering criteria.

• Use database aggregation functions, such as SUM, MIN, MAX, AVG, and COUNT.

• Use expressions to group data.

• Request primary key attributes with each ReportQueryResult. This makes it easy to request the real object from a lightweight result.

  
  

  Note: OracleAS TopLink report queries do not support multiple references to the same attribute in a single result set.

  
  

ExpressionBuilder emp = new ExpressionBuilder();
ReportQuery query = new ReportQuery(emp);
query.setReferenceClass(Employee.class);
query.addMaximum("max-salary", emp.get("salary"));
query.addAverage("average-salary", emp.get("salary"));
query.addAttribute("city", emp.get("address").get("city"));

query.setSelectionCriteria(emp.get("address").get("country").equal("Canada"));
query.addOrdering(emp.get("address").get("city"));
query.addGrouping(emp.get("address").get("city"));
Vector reports = (Vector) session.executeQuery(query);

Partial Attribute Reading

You can query for parts of objects rather than complete objects. For example, you can build a read query that returns a subset of the attributes of an object rather than the entire object. This improves database read performance when you do not require the complete object.

To configure partial object reading, use the addPartialAttribute() method. For more information, see "Partial Object Reading".

Note the following when you use partial object reading:

• You cannot edit or cache partial objects.

• OracleAS TopLink does not automatically include primary key information in a partial object. If you need primary key information (for example, if you want to re-query or edit the object) specify it as a required attribute.

Cache Results In Query Objects

Query objects maintain an internal cache of the objects previously returned by the query. This improves query performance and ensures that the query always returns the same objects.

The internal cache is disabled by default. To enable it, use the cacheQueryResults() method in the query.

ReadObjectQuery query = new ReadObjectQuery();
query.setReferenceClass(Employee.class);
query.cacheQueryResults();

// The query object reads from the database the first time you invoke it.
Employee employee = (Employee) session.executeQuery(query);

/* On this second call to execute the query, the query object does not read from the database, but reads from the query object's internal cache instead */
Employee employee = (Employee) session.executeQuery(query);