Databases Reference
In-Depth Information
Statement pooling is often used with connection pooling. In fact, some imple-
mentations of statement pooling require that you also use connection pooling.
Using statement pooling with connection pooling might consume more memory
on the database server than you realize. Let's look at why.
All connections in a connection pool are maintained in the database's mem-
ory. If you implement statement pooling with connection pooling, each pooled
connection has its own statement pool associated with it. On the database client,
client resources that correlate to each pooled statement are stored in memory. On
the database server, each pooled connection has a statement associated with it
that's also maintained in memory. For example, if you have 5 pooled connections
and 20 prepared statements, each statement pool associated with a connection
may have all 20 prepared statements in it, which means that a total of 100 pre-
pared statements could be maintained in the database's memory. All these con-
nections and statements stay in memory even if no active users are on the system.
Here is how this can happen.
The application connects, prepares
statement1
, closes
statement1
, and
closes the connection. Then the application repeats this operation.
The first time the operation is executed, the application user receives
con-
nection1
and at that time
statement1
(S1) is associated with
connection1
,as
shown in Figure 8-7.
Connection Pool
connection1
S1
connection2
connection3
Figure 8-7 Part 1: Pooled statements associated with connections in the
connection pool
The next time the operation is executed,
connection1
is not available. The
application user receives
connection3,
and
statement1
(S1) is associated with
connection3
, as shown in Figure 8-8.
