Oracle® Application Server TopLink Application Developer's Guide
10g Release 2 (10.1.2) Part No. B15901-01 |
|
Previous |
Next |
Optimization is an important consideration when you design your database schema and object model. Most performance issues occur when the object model or database schema is too complex, which can make the database slow and difficult to query. This is most likely to happen if you derive your database schema directly from a complex object model.
To optimize performance, we recommend that you design the object model and database schema together; however, ensure that there is no direct one-to-one correlation between the two.
A common schema optimization technique is to aggregate two tables into a single table. This improves read and write performance by requiring only one database operation instead of two.
Table 10-4 and Table 10-5 illustrate the table aggregation technique.
Table 10-4 Original Schema
Elements | Details |
---|---|
Title | ACME Member Location Tracking System |
Classes | Member, Address |
Tables | MEMBER, ADDRESS |
Relationships | Source, Instance Variable, Mapping, Target, Member, address, one-to-one, Address |
The nature of this application dictates that you always look up employees and addresses together. Because of this, querying a member based on address information requires a database join, and reading a member and its address requires two read statements. Writing a member requires two write statements. This adds unnecessary complexity to the system, and results in poor performance.
A better solution is to combine the MEMBER and ADDRESS tables into a single table, and change the one-to-one relationship to an aggregate relationship. This enables you to read all information with a single operation, and doubles the speed of updates and inserts, because they must modify only a single row in one table.
To improve overall performance of the system, split large tables into two or more smaller tables. This significantly reduces the amount of data traffic required to query the database.
For example, the system illustrated in Table 10-6 assigns employees to projects within an organization. The most common operation reads a set of employees and projects, assigns employees to projects, and updates the employees. The employee's address or job classification is also occasionally used to determine the project on which the employee is placed.
Table 10-6 Original Schema
Elements | Details | Instance Variable | Mapping | Target |
---|---|---|---|---|
Title | ACME Employee Workflow System |
|
|
|
Classes | Employee, Address, PhoneNumber, EmailAddress, JobClassification, Project |
|
|
|
Tables | EMPLOYEE, PROJECT, PROJ_EMP |
|
|
|
Relationships | Employee | address | aggregate | Address |
|
Employee | phoneNumber | aggregate | EmailAddress |
|
Employee | emailAddress | aggregate | EmailAddress |
|
Employee | job | aggregate | JobClassification |
|
Employee | projects | many-to-many | Project |
When you read a large volume of employees from the database, you must also read their aggregate parts. Because of this, the system suffers from general read performance issues. To resolve this, break the EMPLOYEE table into the EMPLOYEE, ADDRESS, PHONE, EMAIL, and JOB tables, as illustrated in Table 10-7.
Because you normally read only the employee information, splitting the table reduces the amount of data transferred from the database to the client. This improves your read performance by reducing the amount of data traffic by 25 percent.
Table 10-7 Optimized Schema
Elements | Details | Instance Variable | Mapping | Target |
---|---|---|---|---|
Title | ACME Employee Workflow System |
|
|
|
Classes | Employee, Address, PhoneNumber, EmailAddress, JobClassification, Project |
|
|
|
Tables | EMPLOYEE, ADDRESS, PHONE, EMAIL, JOB, PROJECT, PROJ_EMP |
|
|
|
Relationships | Employee | address | one-to-one | Address |
|
Employee | phoneNumber | one-to-one | EmailAddress |
|
Employee | emailAddress | one-to-one | EmailAddress |
|
Employee | job | one-to-one | JobClassification |
|
Employee | projects | many-to-many | Project |
When you transform an object oriented design into a relational model, a common mistake is to build a large hierarchy of tables on the database. This makes querying difficult, because queries against this type of design can require a large number of joins. It is usually a good idea to collapse some of the levels in your inheritance hierarchy into a single table.
Table 10-8 represents a system that assigns clients to a company's sales representatives. The managers also track the sales representatives that report to them.
Table 10-8 Original Schema
Elements | Details |
---|---|
Title | ACME Sales Force System |
Classes | Tables |
Person | PERSON |
Employee | PERSON, EMPLOYEE |
SalesRep | PERSON, EMPLOYEE, REP |
Staff | PERSON, EMPLOYEE, STAFF |
Client | PERSON, CLIENT |
Contact | PERSON, CONTACT |
The system suffers from complexity issues that hinder system development and performance. Nearly all queries against the database require large, resource intensive joins. If you collapse the three-level table hierarchy into a single table, as illustrated in Table 10-9, then you substantially reduce system complexity. You eliminate joins from the system and simplify queries.
In a one-to-many relationship, a single source object has a collection of other objects. In some cases, the source object frequently requires one particular object in the collection, but requires the other objects only infrequently. You can reduce the size of the returned result set in this type of case by adding an instance variable for the frequently required object. This enables you to access the object without instantiating the other objects in the collection.
Table 10-10 represents a system by which an international shipping company tracks the location of packages in transit. When a package moves from one location to another, the system creates a new location entry for the package in the database. The most common query against any given package is for its current location.
Table 10-10 Original Schema
Elements | Details | Instance Variable | Mapping | Target |
---|---|---|---|---|
Title | ACME Shipping Package Location Tracking System |
|
|
|
Classes | Package, Location |
|
|
|
Tables | PACKAGE, LOCATION |
|
|
|
Relationships | Package | locations | one-to-many | Location |
A package in this system can accumulate several location values in its LOCATION collection as it travels to its destination. Reading all locations from the database is resource intensive, especially when the only location of interest is the current location.
To resolve this type of problem, add a specific instance variable that represents the current location. You then add a one-to-one mapping for the instance variable, and use the instance variable to query for the current location. As illustrated in Table 10-11, because you can now query for the current location without reading all locations associated with the package, this dramatically improves the performance of the system.
Table 10-11 Optimized Schema
Elements | Details | Instance Variable | Mapping | Target |
---|---|---|---|---|
Classes | Package, Location |
|
|
|
Tables | PACKAGE, LOCATION |
|
|
|
Relationships | Package | locations | one-to-many | Location |
|
Package | currentLocation | one-to-one | Location |