Databases Reference
In-Depth Information
Connection Pool
connection1
S1
connection2
connection3
S1
Figure 8-8 Part 2: Pooled statements associated with connections in the
connection pool
Statement1
(S1) is now in two statement pools: the statement pool associ-
ated with
connection1
, and the one associated with
connection3
.
Even though the statements are the same, a statement cannot be shared
across connections. Throughout the lifetime of the connection pool, each pre-
pared statement may be associated with each pooled connection. This can equate
to memory issues on the database server.
Here are some general guidelines for using statement pooling:
•
Because all prepared statements go into the pool, do not use statement pool-
ing unless at least 90% of your statements are executed multiple times.
Note
JDBC 4.0 provides a more granular level of statement pooling by allow-
ing applications to hint to the pool manager about whether or not a pre-
pared statement should be pooled.
•
Most database servers impose a limit on the number of statements that can
be active on a connection. Therefore, do not configure the maximum num-
ber of statements for a statement pool to be greater than the server's maxi-
mum limit. For example, if the maximum number of active statements per
connection for the database server is 100, configure the maximum number