12.10 Databases

Databases all have particular features that allow performance optimizations. Usually, the database documentation includes a section on optimizing performance, and that is the place to start.

Here are some hints applicable to many databases (note that JDBC optimizations are covered in Chapter 16):

• Object databases are usually faster than relational databases for applications with strongly object-oriented designs, especially when navigating object networks^[5] is a significant part of the application.

  ^[5] By "navigating object networks," I mean the activity of repeatedly accessing objects from one object's instance variables to another's. The structure formed by the graph of objects reachable through nested instance variable access is a network.

• Relational databases are generally faster than object databases when dealing with large amounts of basic data types, e.g., for objects whose object types are easily mapped into relational tables.

• Application partitioning is important for database access. Reducing the amount of data transferred over the network is often the key to good performance with databases.

• Application partitioning applies to accessing relational databases. Most relational-database products have the ability to execute server-side code in the form of stored procedures. Stored procedures are precompiled SQL code that can be executed by the database server. Some relational-database products can now run Java on the server too (e.g., Oracle).

• Database queries are often faster if they are statically defined, i.e., defined and precompiled. For relational databases, these take the form of prepared statements that can usually accept parameters. Many object databases also support statically defined queries that can navigate object networks more quickly using internal nodal access rather than executing methods.

• Many databases support batching queries to reduce the number of network round trips, and these batching features should be used to improve performance.

• Transactional access to databases is slower than nontransactional access, so use the nontransactional form whenever possible.