Databases Reference
In-Depth Information
Advantages and Disadvantages
The advantage of using one connection for multiple statements is that it reduces
the overhead of establishing multiple connections, while allowing multiple state-
ments to access the database. The overhead is reduced on both the database
server and client machines.
The disadvantage of using this method of connection management is that
the application may have to wait to execute a statement until the single connec-
tion is available. We explained why in “How One Connection for Multiple
Statements Works,” page 17.
Guidelines for One Connection for Multiple Statements
Here are some guidelines for when to use one connection for multiple state-
ments:
•
Consider using this connection model when your database server has hard-
ware constraints such as limited memory and one or more of the following
conditions are true:
a.
You are using a cursor-based protocol database.
b.
The statements in your application return small result sets or no result
sets.
c.
Waiting for a connection is acceptable. The amount of time that is
acceptable for the connection to be unavailable depends on the require-
ments of your application. For example, 5 seconds may be acceptable for
an internal application that logs an employee's time but may not be
acceptable for an online transaction processing (OLTP) application such
as an ATM application. What is an acceptable response time for your
application?
•
This connection model should not be used when your application uses
transactions.
Case Study: Designing Connections
Let's look at one case study to help you understand how to design database con-
nections. The environment details are as follows:
•
The environment includes a middle tier that must support 20 to 100 concur-
rent database users, and performance is key.
