Databases Reference
In-Depth Information
ronment may use more CPU than expected. As a general rule, when the data-
base has to create a new connection or prepared statement, CPU processing
becomes expensive. See Chapter 8, “Connection Pooling and Statement
Pooling,” for information about configuring your pooling environment.
•
Avoid context switching by reducing network round trips
—Each data
request that results in a network round trip triggers a context switch.
Context switching requires significant processor time, so excessive context
switches and high CPU use tend to go hand in hand. Reducing the number
of network round trips also reduces the number of context switches.
Application design and coding practices that reduce network round trips
include connection pooling, statement pooling, avoiding auto-commit
mode in favor of manual commits, using local transactions instead of dis-
tributed transactions where appropriate, and using batches or arrays of
parameters for bulk inserts.
•
Minimize data conversions
—
Choose database drivers that convert data
efficiently. For example, some database drivers don't support Unicode, a
standard encoding that is used for multilingual character sets. If your data-
base driver doesn't support Unicode, more data conversion is required to
work with Unicode data, resulting in higher CPU use.
In addition, choose data types that process efficiently. When you are working
with data on a large scale, select the data type that can be processed most
efficiently. Retrieving and returning certain data types across the network
can increase or decrease network traffic. See “Choosing the Right Data
Type,” page 34, for details on which data types process more efficiently than
others.
•
Be aware that emulated functionality can increase CPU use
—Database dri-
vers sometimes emulate functionality if the database system doesn't support
it. While this provides the benefit of interoperability, you should remember
that emulated behavior typically uses more CPU because the database driver
or the database must perform extra steps to satisfy the behavior. For example,
if your application uses scrollable cursors against Oracle, which doesn't sup-
port scrollable cursors, CPU use on both the client/application server and
database server will be higher than against a database system that does sup-
port scrollable cursors, such as DB2. For more information about the type
