Canonical Database Architecture and DB2 Performance … Really?

by Susan Lawson, Daniel L. Luksetich
May 13, 2009

• The more layers involved means more complexity and confusion. The ultimate goal of the architecture is to provide a more simplistic view of the enterprise. However, there’s considerable complexity involved in building the environment to support that. With common object management and mapping, migrations become more intense and involve more people. Ultimately, you’ll probably need more people to support the enterprise architecture than you anticipated.



Generic Object, Generic Performance

This is one of the biggest trade-offs when it comes to database performance. If data access objects are built on top of individual database objects, then your applications have no way of capitalizing on the specific performance features of the database you’re accessing. In tests:

• Coding a two-table SQL join over accessing the two tables in separate SQL statements has shown up to 30 percent CPU savings.

• Reducing the number of columns an SQL statement returns has demonstrated up to 50 percent CPU savings.

Some organizations have considered using the database software as an access method rather than a database. This has led to highly denormalized database designs and, in some cases, a Binary Large Object (BLOB)-type storage of all data into a single database column. While these designs show promise on paper in lowering database access costs, they can have extreme database performance consequences, including:

• Increased backup and recovery costs because there’s no way to separate only the part of the database that changes. This means having to continuously back up all data, leading to increased backup footprint size, huge backup windows, and increased machine costs to support the strategy.

• Extreme costs in handling database updates. This means that while access costs go down, update costs go up because more data has to be updated in the generic design. If the 80/20 rule applies (80 percent read and 20 percent update), maybe the savings outweigh the costs, but if your activity varies, the costs can become excessive. Also, with a generic design, you could end up updating far more often than is necessary. That is, with the generic design the 80/20 rule may not apply, as there can be fewer controls minimizing the issuing of update statements.

• The potential for update anomalies in a generic or denormalized design can lead to data integrity issues. This can increase the costs associated with testing and troubleshooting.

You should seriously consider the costs associated with generic design and denormalization. Consistent use of denormalization as a solution to data access costs for a generic design can slow or even halt a company’s update operations. One company that experienced this problem eventually had to abandon their entire development effort and restart with a more specifically tuned design. This occurred after investing years of development time and money. Even if they could have achieved acceptable performance, their other goal of having a flexible, accessible database (via SQL and Web applications) could never have been realized.