Databases Reference
In-Depth Information
•
CPU and memory are plentiful on both the middle tier and database server.
•
The database is Oracle, Microsoft SQL Server, Sybase, or DB2.
•
The API that the application uses is ODBC, JDBC, or ADO.NET.
•
There are 25 licenses for connections to the database server.
Here are some possible solutions:
•
Solution 1: Use a connection pool with a maximum of 20 connections, each
with a single statement.
•
Solution 2: Use a connection pool with a maximum of 5 connections, each
with 5 statements.
•
Solution 3: Use a single connection with 5 to 25 statements.
The key information in this case study is the ample CPU and memory on
both the middle tier and database server and the ample number of licenses to the
database server. The other information is really irrelevant to the design of the
database connections.
Solution 1 is the best solution because it performs better than the other two
solutions. Why? Processing one statement per connection provides faster results
for users because all the statements can access the database at the same time.
The architecture for Solutions 2 and 3 is one connection for multiple state-
ments. In these solutions, the single connection can become a bottleneck, which
means slower results for users. Therefore, these solutions do not meet the
requirement of “performance is key.”
A
transaction
is one or more SQL statements that make up a unit of work per-
formed against the database, and either all the statements in a transaction are
committed as a unit or all the statements are rolled back as a unit. This unit of
work typically satisfies a user request and ensures data integrity. For example,
when you use a computer to transfer money from one bank account to another,
the request involves a transaction: updating values stored in the database for
both accounts. For a transaction to be completed and database changes to be
made permanent, a transaction must be completed in its entirety.
What is the correct transaction commit mode to use in your application?
What is the right transaction model for your database application: local or dis-
tributed? Use the guidelines in this section to help you manage transactions more
efficiently.
